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?.

-- your setup:
CREATE TABLE tbl (sn text, date date, time time, vin1 numeric, vin2 numeric, vin3 numeric);
    
INSERT INTO tbl (sn, time, date, vin1, vin2, vin3)
VALUES
    ('4as11111111', '07:06:18', '2018-01-01', 447.42, 472.32, 682.59),
    ('4as11111111', '07:06:43', '2018-01-01', 455.97, 476.25, 682.59),
    ('4as11111111', '07:07:35', '2018-01-01', 470.88, 484.2 , 682.5 ),
    ('4as11111111', '07:27:35', '2018-01-01', 570.88, 584.2 , 782.5 ),  -- some more samples to make the test meaningful
    ('4as11111111', '07:27:35', '2018-01-01', 570.88, 584.2 , 782.5 ),
    ('4as11111111', '07:27:35', '2018-01-01', 570.88, 584.2 , 782.5 );

-- my suggested changes:
ALTER TABLE tbl ADD column ts timestamp;
UPDATE tbl SET ts = date + time;
ALTER TABLE tbl DROP column date , DROP column time;

CREATE TABLE



INSERT 0 6



ALTER TABLE



UPDATE 6



ALTER TABLE


-- now your table looks like this:
TABLE tbl;


sn
vin1
vin2
vin3
ts


4as11111111
447.42
472.32
682.59
2018-01-01 07:06:18

4as11111111
455.97
476.25
682.59
2018-01-01 07:06:43

4as11111111
470.88
484.2
682.5
2018-01-01 07:07:35

4as11111111
570.88
584.2
782.5
2018-01-01 07:27:35

4as11111111
570.88
584.2
782.5
2018-01-01 07:27:35

4as11111111
570.88
584.2
782.5
2018-01-01 07:27:35



SELECT 6


-- query
SELECT *
FROM  (
   SELECT sn, generate_series(min(ts), max(ts), interval '5 min') AS ts
   FROM   tbl
   WHERE  sn = '4as11111111'
   AND    ts >= '2018-01-01'
   AND    ts <  '2018-01-02'
   GROUP  BY 1
   ) grid
CROSS  JOIN LATERAL (
   SELECT round(avg(vin1), 2) AS vin1_av
        , round(avg(vin2), 2) AS vin2_av
        , round(avg(vin3), 2) AS vin3_av
   FROM   tbl
   WHERE  sn =  grid.sn
   AND    ts >= grid.ts
   AND    ts <  grid.ts + interval '5 min'
   ) avg;


sn
ts
vin1_av
vin2_av
vin3_av


4as11111111
2018-01-01 07:06:18
458.09
477.59
682.56

4as11111111
2018-01-01 07:11:18
null
null
null

4as11111111
2018-01-01 07:16:18
null
null
null

4as11111111
2018-01-01 07:21:18
null
null
null

4as11111111
2018-01-01 07:26:18
570.88
584.20
782.50



SELECT 5

sn	vin1	vin2	vin3	ts
4as11111111	447.42	472.32	682.59	2018-01-01 07:06:18
4as11111111	455.97	476.25	682.59	2018-01-01 07:06:43
4as11111111	470.88	484.2	682.5	2018-01-01 07:07:35
4as11111111	570.88	584.2	782.5	2018-01-01 07:27:35
4as11111111	570.88	584.2	782.5	2018-01-01 07:27:35
4as11111111	570.88	584.2	782.5	2018-01-01 07:27:35

sn	ts	vin1_av	vin2_av	vin3_av
4as11111111	2018-01-01 07:06:18	458.09	477.59	682.56
4as11111111	2018-01-01 07:11:18	null	null	null
4as11111111	2018-01-01 07:16:18	null	null	null
4as11111111	2018-01-01 07:21:18	null	null	null
4as11111111	2018-01-01 07:26:18	570.88	584.20	782.50