PostgreSQL中如何检测日期连续性并按规则合并记录？

核心思路 #

解决这个问题的关键是两步：检测日期连续性并分组、按规则合并/过滤分组数据。我们可以借助PostgreSQL的窗口函数标记连续记录组，再对每组进行聚合，最后根据时间规则过滤不需要的历史记录。

步骤1：标记连续记录分组 #

使用LAG()窗口函数获取同一id下上一条记录的end_date，判断当前记录的start_date是否与上一条的end_date无缝衔接（即当前start_date = 上一条end_date + 1天，匹配你场景中“无间隙”的定义）。通过累计求和生成分组ID，把连续的记录归为同一组。

WITH grouped_records AS (
    SELECT
        id,
        -- 若日期是字符串类型，先转为DATE：TO_DATE(start_date, 'DD/MM/YYYY')
        start_date,
        end_date,
        status,
        -- 标记新分组：上一条end_date的次日不等于当前start_date，则为新组起始
        SUM(CASE WHEN start_date = LAG(end_date) OVER (PARTITION BY id ORDER BY start_date) + INTERVAL '1 day' THEN 0 ELSE 1 END) 
            OVER (PARTITION BY id ORDER BY start_date) AS group_id
    FROM your_table_name
    ORDER BY id, start_date
)

步骤2：合并连续分组的记录 #

对每个分组，取最早的start_date、最晚的end_date，并以分组内的最终活跃状态为准（只要分组内有Active记录，就显示Active）：

, merged_groups AS (
    SELECT
        id,
        MIN(start_date) AS start_date,
        MAX(end_date) AS end_date,
        -- 优先保留Active状态，匹配连续记录最终活跃的规则
        CASE WHEN BOOL_OR(status = 'Active') THEN 'Active' ELSE 'Inactive' END AS status
    FROM grouped_records
    GROUP BY id, group_id
)

步骤3：按场景规则过滤记录 #

最后处理有间隙的历史记录：对于Inactive的记录，如果其end_date距离当前日期超过12个月，则过滤掉；否则保留。

SELECT
    id,
    start_date,
    end_date,
    status
FROM merged_groups
WHERE
    -- 保留所有活跃记录
    status = 'Active'
    -- 保留未超过12个月的非活跃历史记录
    OR (status = 'Inactive' AND end_date >= CURRENT_DATE - INTERVAL '12 months')
ORDER BY id, start_date;

场景验证 #

场景1：连续且最终活跃

输入数据：

执行SQL后，三条记录被归为同一组，输出：

场景2：有间隙且历史记录在12个月内

输入数据：

两条记录分属不同分组，且非活跃记录的结束时间未超12个月，输出与输入一致。

场景3：有间隙且历史记录超12个月

输入数据：

非活跃记录的结束时间超12个月，会被过滤，仅保留活跃记录。

注意事项 #

• 若你的“无间隙”定义是上一条end_date等于当前start_date（比如当天结束当天开始），只需把start_date = LAG(end_date) OVER (...) + INTERVAL '1 day'改为start_date = LAG(end_date) OVER (...)即可。
• 若日期存储为字符串，必须先用TO_DATE()转换为DATE类型，否则无法进行日期运算。

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