PostgreSQL中按10分钟间隔聚合时间戳并统计数据的SQL实现

问题背景 #

我有一张带timestamp字段的对象数据表，需要按10分钟间隔分区统计每个区间的对象数量。已经能用generate_series生成指定时间范围的10分钟时间序列，想知道怎么把这个序列和数据表关联实现统计。

示例数据表 #

已实现的时间序列SQL #

SELECT 
*
FROM
(
SELECT *
FROM generate_series(
    '2025-05-23 07:00'::timestamp, 
    '2025-05-23 08:00'::timestamp,
    '10 minutes'::INTERVAL
    ) dd
) timeseries

实现方案 #

用**左连接（LEFT JOIN）**关联时间序列和数据表，结合区间判断匹配数据，最后分组统计即可。

完整SQL代码 #

WITH timeseries AS (
    SELECT 
        dd AS interval_start,
        dd + '10 minutes'::INTERVAL AS interval_end
    FROM generate_series(
        '2025-05-23 07:00'::timestamp, 
        '2025-05-23 08:00'::timestamp,
        '10 minutes'::INTERVAL
    ) dd
)
SELECT 
    t.interval_start,
    t.interval_end,
    COUNT(obj.id) AS object_count
FROM timeseries t
LEFT JOIN your_table obj 
    ON obj.timestamp >= t.interval_start 
    AND obj.timestamp < t.interval_end
GROUP BY t.interval_start, t.interval_end
ORDER BY t.interval_start;

关键细节 #

• 扩展时间序列：在CTE里给每个时间点生成对应的区间结束时间（起始时间+10分钟），明确每个10分钟窗口的范围。
• 左连接保证完整性：用LEFT JOIN确保即使某个区间没有数据，也会显示该区间并统计为0，不会漏掉时间序列里的任何区间。
• 精准区间匹配：用>=和<判断时间，避免边界数据重复统计（比如刚好在10分钟整的数据会被归到下一个区间）。
• 分组排序：按区间起始时间分组统计，最后排序得到有序的结果。

示例数据输出结果 #

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