PostgreSQL按时间间隔分组行：基于30天差合并连续事件

针对你需求的按id分组、合并间隔小于30天的连续事件场景，经典的解决方式是窗口函数+累加分组，以下是具体实现：

第一步：模拟测试数据 #

假设你的表名为event_records，测试数据如下：

CREATE TABLE event_records (
    id INT,
    start DATE,
    end DATE
);

INSERT INTO event_records VALUES
(1, '2023-09-01', '2023-09-05'),
(1, '2023-09-20', '2023-09-25'),
(1, '2023-11-01', '2023-11-05'),
(2, '2023-08-01', '2023-08-10'),
(2, '2023-08-25', '2023-08-30'),
(2, '2023-09-20', '2023-09-25');

第二步：完整SQL实现 #

WITH ranked_records AS (
    SELECT
        id,
        start,
        end,
        -- 判断当前记录是否开启新事件：与上一条记录的间隔≥30天则标记为新组
        CASE
            WHEN start - LAG(end) OVER (PARTITION BY id ORDER BY start) < INTERVAL '30 days'
            THEN 0
            ELSE 1
        END AS is_new_group
    FROM event_records
),
grouped_records AS (
    SELECT
        id,
        start,
        end,
        -- 累加新组标记，为每个连续事件生成唯一组ID
        SUM(is_new_group) OVER (PARTITION BY id ORDER BY start) AS event_group_id
    FROM ranked_records
)
-- 按id和组ID聚合，得到合并后的事件
SELECT
    id,
    MIN(start) AS event_start,
    MAX(end) AS event_end
FROM grouped_records
GROUP BY id, event_group_id
ORDER BY id, event_start;

关键逻辑说明 #

1. 排序与窗口范围：PARTITION BY id确保仅在同一id内处理，ORDER BY start保证记录按时间顺序排列，避免LAG函数取到错误的前置记录。
2. 新组标记：is_new_group字段标记当前记录是否属于新事件——如果当前记录的start与上一条记录的end间隔小于30天，则归为同一组（标记0），否则开启新组（标记1）。
3. 生成组ID：通过累加is_new_group值，同一连续事件的所有记录会得到相同的event_group_id，不同事件的ID自动递增。
4. 聚合结果：最后按id和组ID分组，取组内最早的start和最晚的end，即为合并后的事件区间。

常见问题排查 #

你之前用LAG函数未得到预期结果，大概率是以下原因：

• 未对每个id内的记录按start排序，导致LAG取到的不是时间上的前一条记录。
• 仅用LAG标记了新组，但未通过累加生成连续的组ID，无法将多条连续记录归为同一组。

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