I have a table that contains IoT updates from many devices. I’d like to calculate an average for each hour based on the device readings. I would like those readings to be carried forward before aggregating. I don’t have readings for each hour, there are gaps.
Given a table:
stamp | device_id | reading
2024-12-21 01:00:00 | 1 | 200
2024-12-21 02:00:00 | 2 | 500
2024-12-21 03:00:00 | 2 | 100
I would expect a result:
stamp | average_reading
2024-12-21 01:00:00 | 200
2024-12-21 02:00:00 | 350 = (200+500)/2
2024-12-21 03:00:00 | 150 = (200+100)/2
2024-12-21 04:00:00 | 150 = (200+100)/2
I was playing with locf & avg and time_bucket_gapfill, but it seems that locf has to be a top function, so it can only carry forward a previous average, which is not what I want.
SELECT time_bucket_gapfill('1h', table.stamp) as bucket,
locf(avg(reading))
FROM table
WHERE stamp >= '2024-12-21 01:00:00'
AND stamp < '2024-12-21 05:00:00'
GROUP BY bucket
I was trying to get prev of lcof somehow to get it done, but it doesn’t seem possible to do it per device.
How can I do it?
This seems to work:
WITH filled_data AS (
SELECT
time_bucket_gapfill('1h', stamp) AS bucket,
device_id,
locf(avg(reading)) AS carried_forward_reading
FROM iot_table
WHERE stamp >= '2024-12-21 01:00:00'
AND stamp < '2024-12-21 05:00:00'
GROUP BY bucket, device_id
)
SELECT
bucket AS stamp,
avg(carried_forward_reading) AS average_reading
FROM filled_data
GROUP BY bucket
ORDER BY bucket;