如何用SQL-Timeseries time bucket聚合表并返回最大值关联外键及Top N外键

核心思路 #

不能直接将peripheral_id或data_point_type_id加入GROUP BY（会拆分时间桶的全局聚合），改用窗口函数在每个时间桶内对数据排序，筛选出对应最大值或Top N的记录，同时保留关联外键。

1. 获取每个时间桶的最大值及对应外键 #

用ROW_NUMBER()窗口函数对每个60秒时间桶内的记录按value降序排名，取排名第1的记录（即最大值）：

WITH bucketed_data AS (
    SELECT
        time_bucket('60 seconds', time) AS bucket,
        value,
        peripheral_id,
        data_point_type_id,
        ROW_NUMBER() OVER (PARTITION BY time_bucket('60 seconds', time) ORDER BY value DESC) AS rn
    FROM public.iot_datapoint
)
SELECT 
    bucket, 
    value AS max_value, 
    peripheral_id, 
    data_point_type_id
FROM bucketed_data
WHERE rn = 1;

2. 返回每个时间桶的Top N个外键（按值降序） #

如果需要保留每个时间桶内前N个最大值的记录，将WHERE条件改为rn <= N即可。若存在并列最大值，建议用RANK()代替ROW_NUMBER()，避免遗漏并列数据：

WITH bucketed_data AS (
    SELECT
        time_bucket('60 seconds', time) AS bucket,
        value,
        peripheral_id,
        data_point_type_id,
        -- 并列最大值会获得相同排名，不会被过滤
        RANK() OVER (PARTITION BY time_bucket('60 seconds', time) ORDER BY value DESC) AS rnk
    FROM public.iot_datapoint
)
SELECT 
    bucket, 
    value, 
    peripheral_id, 
    data_point_type_id
FROM bucketed_data
WHERE rnk <= 3; -- 替换为你需要的Top N数值

补充说明 #

• PARTITION BY time_bucket('60 seconds', time)指定按60秒时间桶分组计算窗口函数
• 若需要按peripheral_id或data_point_type_id分组后再取Top N，可调整PARTITION BY的字段，例如：PARTITION BY time_bucket('60 seconds', time), peripheral_id
• 针对TimescaleDB超表，该方案会利用超表的分区特性，性能优于子查询嵌套

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