如何用SQL合并同一ID下连续相同状态的日期区间数据

原始数据 #

期望结果 #

解决方案SQL #

思路 #

通过窗口函数标记每个id下连续相同status的分组：

1. 按id和startdate排序生成全局行号
2. 按id、status和startdate排序生成分组内行号
3. 两者的差值即为连续相同status的分组标识
4. 按id、status和分组标识聚合，取最小startdate和最大enddate

代码实现 #

WITH original_data AS (
    SELECT '111' AS id, 0 AS status, '2023-01-13'::date AS startdate, '2023-01-17'::date AS enddate 
    UNION ALL
    SELECT '111', 1, '2023-01-18', '2023-01-22' 
    UNION ALL
    SELECT '111', 1, '2023-01-23', '2023-01-23' 
    UNION ALL
    SELECT '111', 1, '2023-01-24', '2023-01-26' 
    UNION ALL
    SELECT '111', 0, '2023-01-27', '9999-12-31' 
    UNION ALL
    SELECT '222', 0, '2023-01-19', '2023-01-22' 
    UNION ALL
    SELECT '222', 0, '2023-01-23', '2023-01-27' 
    UNION ALL
    SELECT '222', 0, '2023-01-28', '9999-12-31'
),
grouped_data AS (
    SELECT 
        id,
        status,
        startdate,
        enddate,
        -- 生成连续相同status的分组标识
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY startdate) 
        - ROW_NUMBER() OVER (PARTITION BY id, status ORDER BY startdate) AS group_id
    FROM original_data
)
SELECT 
    id,
    status,
    MIN(startdate) AS startdate,
    MAX(enddate) AS enddate
FROM grouped_data
GROUP BY id, status, group_id
ORDER BY id, startdate;

说明 #

• 用UNION ALL替代原代码的UNION，避免不必要的去重，提升执行效率
• 窗口函数差值的分组逻辑可准确识别同一id下连续的相同status记录
• 聚合后按id和startdate排序，保证结果顺序与期望一致

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