如何在SQL中按id分组合并连续日期区间的行

原始数据 #

生成原始数据的R代码 #

df <- as.data.frame(cbind(
  id = c(1, 1, 1, 1, 1, 1, 1), 
  Start = c("2018-01-01", "2018-02-01", "2018-03-01", "2018-04-01", "2018-08-01", "2018-09-01", "2018-10-01"), 
  End = c("2018-01-31", "2018-02-28", "2018-03-31", "2018-04-30", "2018-08-31", "2018-09-30", "2018-10-31")
))

需求 #

按id分组，将后一行的Start日期与前一行的End日期加1天对比，若Start小于等于这个日期，则合并两行，最终得到连续的日期区间，预期结果如下：

SQL实现 #

核心思路是用窗口函数标记连续区间，再聚合得到结果：

1. 按id和Start排序，用LAG()函数获取上一行的End日期
2. 判断当前行是否属于新的区间：如果当前Start大于上一行End+1，则标记为新区间起点
3. 累加标记值得到每个区间的分组ID，最后按分组聚合出每个区间的最小Start和最大End

通用SQL示例（适配多数支持窗口函数的数据库） #

WITH ranked_data AS (
    SELECT 
        id,
        Start,
        End,
        CASE 
            WHEN Start <= DATE_ADD(LAG(End) OVER (PARTITION BY id ORDER BY Start), INTERVAL 1 DAY) 
            THEN 0 
            ELSE 1 
        END AS is_new_group
    FROM your_table_name
),
grouped_data AS (
    SELECT 
        id,
        Start,
        End,
        SUM(is_new_group) OVER (PARTITION BY id ORDER BY Start) AS group_id
    FROM ranked_data
)
SELECT 
    id,
    MIN(Start) AS Start,
    MAX(End) AS End
FROM grouped_data
GROUP BY id, group_id
ORDER BY id, Start;

数据库适配说明 #

• PostgreSQL：将DATE_ADD(..., INTERVAL 1 DAY)替换为LAG(End) OVER (...) + INTERVAL '1 day'
• SQL Server：替换为DATEADD(day, 1, LAG(End) OVER (...))
• 若Start和End是字符串类型，需先转换为日期：CAST(Start AS DATE)

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