PostgreSQL如何按间隔生成时间戳范围并查询重叠事件

实现方案 #

你直接把生成时间范围的逻辑作为子查询替换原有的时间点grid，关联时使用&&重叠运算符即可，完整可运行的查询如下：

SELECT 
    row_number() OVER (ORDER BY grid.time_range) AS id,
    grid.time_range,
    count(DISTINCT t.id) AS id_count
FROM (
    SELECT tsrange((lag(bin) OVER (ORDER BY bin)), bin, '[)') AS time_range
    FROM generate_series(
        '2021-08-01 00:00:00'::timestamp,
        '2021-08-07 23:59:59'::timestamp, 
        interval '60 min'
    ) AS bin
    OFFSET 1 -- 跳过第一行lag生成的空区间，也可以在外层加WHERE time_range IS NOT NULL过滤
) grid
LEFT JOIN event t ON t.order_dates && grid.time_range
GROUP BY grid.time_range
ORDER BY grid.time_range;

说明 #

• 内层子查询直接复用了你写的时间范围生成逻辑，产出每个左闭右开的60分钟时间区间
• 关联条件替换为&&运算符，会匹配所有和当前时间区间存在重叠的事件
• 额外优化：你之前写的generate_series后加了FROM event，会生成和event表行数一致的重复时间序列，直接去掉即可，generate_series本身可以独立生成所需的时间序列，不需要依赖物理表
• 可以根据需求调整generate_series的起止时间和间隔，或者新增聚合字段（比如统计区间内的流量总和加sum(t.flow) AS total_flow即可）

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