TimescaleDB如何先向前填充设备读数再按小时桶聚合计算平均值？

我有一张存储多台IoT设备更新数据的表，希望基于设备读数计算每小时的平均值，且需要先将每台设备的读数向前填充（存在数据间隙，并非每小时都有读数）。

给定表结构 #

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

预期结果 #

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

我尝试使用locf、avg和time_bucket_gapfill，但locf似乎只能作为顶层函数，仅能向前填充之前的平均值，这不符合需求。尝试的SQL语句：

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

我曾尝试获取locf的前值以实现按设备处理，但似乎无法做到。请问该如何实现需求？

核心思路是先按设备维度生成每小时的时间桶并向前填充读数，再基于填充后的设备数据计算每小时的平均值，具体SQL实现（基于TimescaleDB）如下：

WITH hourly_buckets AS (
    -- 生成目标时间范围内的所有小时桶
    SELECT time_bucket_gapfill('1h', s) AS bucket
    FROM generate_series('2024-12-21 01:00:00'::timestamp, '2024-12-21 04:00:00'::timestamp, '1h') s
),
device_buckets AS (
    -- 关联所有设备和小时桶，确保每个设备在每个桶都有记录
    SELECT h.bucket, d.device_id
    FROM hourly_buckets h
    CROSS JOIN (SELECT DISTINCT device_id FROM your_table) d
),
filled_readings AS (
    -- 关联原始数据，对每个设备的读数进行向前填充
    SELECT 
        db.bucket,
        db.device_id,
        locf(last_value(y.reading) OVER (PARTITION BY db.device_id ORDER BY db.bucket)) AS filled_reading
    FROM device_buckets db
    LEFT JOIN your_table y ON db.bucket = y.stamp AND db.device_id = y.device_id
)
-- 计算每小时的平均读数
SELECT bucket AS stamp, AVG(filled_reading) AS average_reading
FROM filled_readings
GROUP BY bucket
ORDER BY bucket;

语句说明 #

• hourly_buckets：生成目标时间区间内的所有小时级时间桶，避免遗漏需要统计的小时段。
• device_buckets：通过交叉连接让每个设备对应每个时间桶，解决部分设备在特定小时无数据的问题。
• filled_readings：借助last_value窗口函数结合locf，对单设备的读数按时间排序后向前填充，保证每个设备在每小时都有有效读数。
• 最后按小时分组计算填充后所有设备读数的平均值，得到符合预期的结果。

内容的提问来源于stack exchange，提问作者Marek