如何编写SQL查询合并连续日期区间的重复记录

原始数据 #

目标结果 #

需求说明 #

对相同id和status的记录，合并连续日期区间：当某条记录的start_date恰好等于上一条记录end_date的次日时，将这两条（或多条连续的）记录合并为一条，取该组最早的start_date和最晚的end_date。

解决方案SQL #

通过窗口函数生成分组标识后聚合，兼容PostgreSQL等支持窗口函数的数据库：

WITH ranked_data AS (
    SELECT
        id,
        status,
        start_date,
        end_date,
        -- 标记当前记录是否开启新分组，不连续则生成新分组
        CASE
            WHEN start_date = LAG(end_date) OVER (PARTITION BY id, status ORDER BY start_date) + INTERVAL '1 day'
            THEN 0
            ELSE 1
        END AS is_new_group
    FROM (
        -- 测试数据，实际使用时替换为你的表名
        SELECT '111' AS id, '1' AS status,  '2023-04-14'::date AS start_date, '2023-04-14'::date AS end_date UNION 
        SELECT '111', '1', '2023-04-15', '2023-04-18' UNION 
        SELECT '111', '1', '2023-04-30', '9999-12-31' UNION 
        SELECT '222', '1', '2023-04-01', '2023-04-02' UNION 
        SELECT '222', '1', '2023-04-03', '2023-04-05' UNION 
        SELECT '222', '1', '2023-04-15', '2023-04-20' UNION 
        SELECT '222', '1', '2023-04-25', '9999-12-31'
    ) AS test_data
),
grouped_data AS (
    SELECT
        id,
        status,
        start_date,
        end_date,
        -- 累计分组标识，得到每个合并组的唯一ID
        SUM(is_new_group) OVER (PARTITION BY id, status ORDER BY start_date) AS group_id
    FROM ranked_data
)
SELECT
    id,
    status,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date
FROM grouped_data
GROUP BY id, status, group_id
ORDER BY id, start_date;

代码说明 #

1. ranked_data 临时表：按id和status分组、start_date排序，用LAG函数获取上一条记录的end_date，判断当前记录是否与上一条连续，生成is_new_group标识（1表示新分组，0表示属于当前分组）。
2. grouped_data 临时表：对is_new_group累计求和，生成每个合并组的group_id，同一连续区间的记录会拥有相同的group_id。
3. 最终聚合：按id、status和group_id分组，取每组的最小start_date和最大end_date，得到合并后的结果。

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