合并连续重复时间范围记录：需实际结束日期而非NULL的解决方案

我明白你的需求——你需要合并连续时间区间内属性完全相同的员工记录，但要保留每组最后一条记录的实际结束日期，而不是用NULL填充。原方案的问题在于它依赖下一组的起始日期来计算当前组的结束日期，导致最后一组没有后续记录时只能返回NULL，这显然不符合你的要求。

源数据 #

EmployeeId StartDate  EndDate    Column1 Column2
1000       2009/05/01 2010/04/30 X       Y
1000       2010/05/01 2011/04/30 X       Y
1000       2011/05/01 2012/04/30 X       X
1000       2012/05/01 2013/04/30 X       Y
1000       2013/05/01 2014/04/30 X       X
1000       2014/05/01 2014/06/01 X       X

期望结果 #

EmployeeId StartDate  EndDate    Column1 Column2
1000       2009/05/01 2011/04/30 X       Y
1000       2011/05/01 2012/04/30 X       X
1000       2012/05/01 2013/04/30 X       Y
1000       2013/05/01 2014/06/01 X       X

修改后的解决方案 #

下面的SQL使用窗口函数来给连续的相同属性记录分组，直接取每组的实际结束日期，完美解决NULL的问题：

WITH ranked_records AS (
    SELECT 
        *,
        -- 标记连续相同属性的记录组：当前记录与上一条属性一致且时间连续时，属于同一组
        SUM(CASE 
                WHEN LAG(Column1) OVER (PARTITION BY EmployeeId ORDER BY StartDate) = Column1 
                     AND LAG(Column2) OVER (PARTITION BY EmployeeId ORDER BY StartDate) = Column2
                     AND DATEADD(day, 1, LAG(EndDate) OVER (PARTITION BY EmployeeId ORDER BY StartDate)) = StartDate
                THEN 0 
                ELSE 1 
            END) OVER (PARTITION BY EmployeeId ORDER BY StartDate) AS group_id
    FROM test_table
)
SELECT 
    EmployeeId,
    MIN(StartDate) AS StartDate,
    MAX(EndDate) AS EndDate, -- 直接取组内最后一条记录的实际结束日期
    Column1,
    Column2
FROM ranked_records
GROUP BY EmployeeId, group_id, Column1, Column2
ORDER BY EmployeeId, StartDate;

方案逻辑解释 #

1. 分组标记：通过LAG()窗口函数获取当前记录的上一条记录，检查两个条件：
   • 上一条记录的Column1和Column2与当前完全相同
   • 上一条记录的EndDate加1天刚好等于当前记录的StartDate（保证时间区间连续无间隙）
     如果满足这两个条件，说明属于同一组，标记为0；否则开启新组，标记为1。
2. 生成组ID：用SUM()累加标记值，为每个连续组生成唯一的group_id。
3. 合并记录：按EmployeeId、group_id、属性字段分组，取每组的最小StartDate（组的起始时间）和最大EndDate（组的实际结束时间，不会出现NULL）。

这个方案相比原方案更直观，且完全满足你需要保留实际结束日期的要求。

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