Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TEMP TABLE threshold (
   sensorname varchar(10), 
   minlimit numeric, 
   maxlimit numeric
);

INSERT INTO threshold VALUES
  ('Sensor1', 80 , 115)
, ('Sensor2', 60 , 70)
, ('Sensor3', 100, 120)
-- more
;

CREATE TABLE iotdata (
   iotdatetime timestamp PRIMARY KEY
 , sensor1 numeric(3)
 , sensor2 numeric(8,5)
 , sensor3 numeric(5,2)
);

INSERT INTO iotdata VALUES
  ('2020-01-01 11:05:00', 85, 65, 110)
, ('2020-01-01 11:10:00', 86, 11, 109)  -- low
, ('2020-01-01 11:15:00', 77, 15, 666)  -- low + hi
;
3 rows affected

3 rows affected

TABLE threshold;


sensorname
minlimit
maxlimit


Sensor1
80
115

Sensor2
60
70

Sensor3
100
120


-- pivot table threshold to match pivoted data
CREATE TABLE dim_threshold AS
SELECT *
FROM  crosstab(
 $$(
   SELECT 'min' AS dimension, sensorname, minlimit
   FROM   threshold
   ORDER  BY sensorname
   )
   UNION ALL
   (
   SELECT 'max' AS dimension, sensorname, maxlimit
   FROM   threshold
   ORDER  BY sensorname
   )$$
   , $$(SELECT unnest('{Sensor1,Sensor2,Sensor3}'::text[]))$$
   ) AS (dimension text, sensor1 numeric, sensor2 numeric, sensor3 numeric);

TABLE dim_threshold;
2 rows affected



dimension
sensor1
sensor2
sensor3


min
80
60
100

max
115
70
120


TABLE iotdata;


iotdatetime
sensor1
sensor2
sensor3


2020-01-01 11:05:00
85
65.00000
110.00

2020-01-01 11:10:00
86
11.00000
109.00

2020-01-01 11:15:00
77
15.00000
666.00


-- aux function to calculate percentage
CREATE FUNCTION f_calc_pct(bigint, bigint)
  RETURNS float LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ($1 * 100)::float / $2';
CREATE OR REPLACE FUNCTION udf_sensor_fail_per_day(pstartdate timestamp
                                                 , penddate   timestamp)
  RETURNS TABLE(the_day date, dimenstion text, sensor1 float, sensor2 float, sensor3 float) -- more?
  LANGUAGE sql ROWS 100 AS
$func$
WITH cte AS (
   SELECT iotdatetime::date AS the_day
        , count(*) AS ct
        , count(*) FILTER (WHERE i.sensor1 < min.sensor1) AS s1_min
        , count(*) FILTER (WHERE i.sensor2 < min.sensor2) AS s2_min
        , count(*) FILTER (WHERE i.sensor3 < min.sensor3) AS s3_min
      -- more ...
        , count(*) FILTER (WHERE i.sensor1 > max.sensor1) AS s1_max
        , count(*) FILTER (WHERE i.sensor2 > max.sensor2) AS s2_max
        , count(*) FILTER (WHERE i.sensor3 > max.sensor3) AS s3_max
      -- more ...
   FROM   iotdata i
   CROSS  JOIN (SELECT * FROM dim_threshold WHERE dimension = 'min') min
   CROSS  JOIN (SELECT * FROM dim_threshold WHERE dimension = 'max') max
   WHERE  iotdatetime >= pstartdate
   AND    iotdatetime <  penddate
   GROUP  BY 1
   )
SELECT the_day, 'min' AS dimension
     , f_calc_pct(s1_min, ct) -- AS s1
     , f_calc_pct(s2_min, ct) -- AS s2
     , f_calc_pct(s3_min, ct) -- AS s3
    -- more ...
FROM   cte
UNION ALL
SELECT the_day, 'max' AS dimension
     , f_calc_pct(s1_max, ct) -- AS s1
     , f_calc_pct(s2_max, ct) -- AS s2
     , f_calc_pct(s3_max, ct) -- AS s3
    -- more ...
FROM   cte;
$func$;
-- call
SELECT * FROM udf_sensor_fail_per_day('2020-01-01 00:00', '2020-01-11 00:00');


the_day
dimenstion
sensor1
sensor2
sensor3


2020-01-01
min
33.3333333333333
66.6666666666667
0

2020-01-01
max
0
0
33.3333333333333

iotdatetime	sensor1	sensor2	sensor3
2020-01-01 11:05:00	85	65.00000	110.00
2020-01-01 11:10:00	86	11.00000	109.00
2020-01-01 11:15:00	77	15.00000	666.00

the_day	dimenstion	sensor1	sensor2	sensor3
2020-01-01	min	33.3333333333333	66.6666666666667	0
2020-01-01	max	0	0	33.3333333333333