Postgres 14

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

CREATE TABLE sensor_readings (
    sensor_id uuid,
    reading float4,
    "timestamp" timestamptz
);
INSERT INTO sensor_readings(sensor_id,reading,"timestamp") 
VALUES 
   ('94eb0f6c-4484-4d9f-9d1f-ff0867916117',0.5,'2021-12-18T11:25:30+00:00'),
   ('94eb0f6c-4484-4d9f-9d1f-ff0867916117',1,'2021-12-18T11:26:30+00:00'),
   ('94eb0f6c-4484-4d9f-9d1f-ff0867916117',0.5,'2021-12-18T11:27:30+00:00'),
   ('94eb0f6c-4484-4d9f-9d1f-ff0867916117',0.5,'2021-12-18T11:28:30+00:00'),
   ('94eb0f6c-4484-4d9f-9d1f-ff0867916117',1.5,'2021-12-18T11:29:30+00:00'),
   ('94eb0f6c-4484-4d9f-9d1f-ff0867916117',2.5,'2021-12-18T11:30:30+00:00'),
   ('94eb0f6c-4484-4d9f-9d1f-ff0867916117',1.5,'2021-12-18T11:31:30+00:00'),
   ('94eb0f6c-4484-4d9f-9d1f-ff0867916117',0.5,'2021-12-18T11:32:30+00:00'),
   ('94eb0f6c-4484-4d9f-9d1f-ff0867916117',0.5,'2021-12-18T11:33:30+00:00'),
   ('94eb0f6c-4484-4d9f-9d1f-ff0867916117',1,'2021-12-18T11:34:30+00:00'),
   ('94eb0f6c-4484-4d9f-9d1f-ff0867916117',1.5,'2021-12-18T11:35:30+00:00'),
    /* For 2nd Sensor **/
   ('e19d7fc7-e6af-4d9d-9292-cfba5fa75c16',0.5,'2021-12-18T11:25:30+00:00'),
   ('e19d7fc7-e6af-4d9d-9292-cfba5fa75c16',1,'2021-12-18T11:26:30+00:00'),
   ('e19d7fc7-e6af-4d9d-9292-cfba5fa75c16',0.5,'2021-12-18T11:27:30+00:00');
14 rows affected

select r.sensor_id,
       r.reading,
       r."timestamp",
       AVG(r.reading) OVER (partition by r.sensor_id 
                            ORDER BY r."timestamp" 
                            range between interval '5 minute' PRECEDING AND CURRENT ROW) as avg_reading
from sensor_readings r 
order by r.sensor_id, r."timestamp";


sensor_id
reading
timestamp
avg_reading


94eb0f6c-4484-4d9f-9d1f-ff0867916117
0.5
2021-12-18 11:25:30+00
0.5

94eb0f6c-4484-4d9f-9d1f-ff0867916117
1
2021-12-18 11:26:30+00
0.75

94eb0f6c-4484-4d9f-9d1f-ff0867916117
0.5
2021-12-18 11:27:30+00
0.6666666666666666

94eb0f6c-4484-4d9f-9d1f-ff0867916117
0.5
2021-12-18 11:28:30+00
0.625

94eb0f6c-4484-4d9f-9d1f-ff0867916117
1.5
2021-12-18 11:29:30+00
0.8

94eb0f6c-4484-4d9f-9d1f-ff0867916117
2.5
2021-12-18 11:30:30+00
1.0833333333333333

94eb0f6c-4484-4d9f-9d1f-ff0867916117
1.5
2021-12-18 11:31:30+00
1.25

94eb0f6c-4484-4d9f-9d1f-ff0867916117
0.5
2021-12-18 11:32:30+00
1.1666666666666667

94eb0f6c-4484-4d9f-9d1f-ff0867916117
0.5
2021-12-18 11:33:30+00
1.1666666666666667

94eb0f6c-4484-4d9f-9d1f-ff0867916117
1
2021-12-18 11:34:30+00
1.25

94eb0f6c-4484-4d9f-9d1f-ff0867916117
1.5
2021-12-18 11:35:30+00
1.25

e19d7fc7-e6af-4d9d-9292-cfba5fa75c16
0.5
2021-12-18 11:25:30+00
0.5

e19d7fc7-e6af-4d9d-9292-cfba5fa75c16
1
2021-12-18 11:26:30+00
0.75

e19d7fc7-e6af-4d9d-9292-cfba5fa75c16
0.5
2021-12-18 11:27:30+00
0.6666666666666666

sensor_id	reading	timestamp	avg_reading
94eb0f6c-4484-4d9f-9d1f-ff0867916117	0.5	2021-12-18 11:25:30+00	0.5
94eb0f6c-4484-4d9f-9d1f-ff0867916117	1	2021-12-18 11:26:30+00	0.75
94eb0f6c-4484-4d9f-9d1f-ff0867916117	0.5	2021-12-18 11:27:30+00	0.6666666666666666
94eb0f6c-4484-4d9f-9d1f-ff0867916117	0.5	2021-12-18 11:28:30+00	0.625
94eb0f6c-4484-4d9f-9d1f-ff0867916117	1.5	2021-12-18 11:29:30+00	0.8
94eb0f6c-4484-4d9f-9d1f-ff0867916117	2.5	2021-12-18 11:30:30+00	1.0833333333333333
94eb0f6c-4484-4d9f-9d1f-ff0867916117	1.5	2021-12-18 11:31:30+00	1.25
94eb0f6c-4484-4d9f-9d1f-ff0867916117	0.5	2021-12-18 11:32:30+00	1.1666666666666667
94eb0f6c-4484-4d9f-9d1f-ff0867916117	0.5	2021-12-18 11:33:30+00	1.1666666666666667
94eb0f6c-4484-4d9f-9d1f-ff0867916117	1	2021-12-18 11:34:30+00	1.25
94eb0f6c-4484-4d9f-9d1f-ff0867916117	1.5	2021-12-18 11:35:30+00	1.25
e19d7fc7-e6af-4d9d-9292-cfba5fa75c16	0.5	2021-12-18 11:25:30+00	0.5
e19d7fc7-e6af-4d9d-9292-cfba5fa75c16	1	2021-12-18 11:26:30+00	0.75
e19d7fc7-e6af-4d9d-9292-cfba5fa75c16	0.5	2021-12-18 11:27:30+00	0.6666666666666666