如何递归更新按ID分组的重叠日期数据？

需要处理按ID分组的日期表，解决行与前一行的日期重叠问题——且更新某一行后可能导致后续原本不重叠的行产生新重叠，因此需要递归更新直至无重叠。当前使用LAG窗口函数的方案仅基于原始行的日期计算，无法联动后续行的更新结果（比如ID=1的第三行结果不符合预期），以下是基于递归CTE的解决方案：

当前问题代码 #

select
     *
     , case
          when start_date <= lag(end_date) over(partition by id order by start_date) then dateadd(day, 1, lag(end_date) over(partition by id order by start_date))
          else start_date
     end as start_date_new
     , dateadd(day, Number_Days, case when start_date <= lag(end_date) over(partition by id order by start_date) then dateadd(day, 1, lag(end_date) over(partition by id order by start_date))
          else start_date
     end) as end_date_new
from
     my_table

递归CTE解决方案 #

递归CTE可以逐行基于前一行的更新后日期进行计算，完美解决连锁重叠问题：

-- 第一步：给每个ID内的行按start_date排序，添加行号
WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY start_date) AS rn
    FROM my_table
),
-- 第二步：递归CTE，逐行计算修正后的日期
recursive_dates AS (
    -- 锚点：每个ID的第一行，直接使用原始日期
    SELECT 
        id,
        start_date AS start_date_new,
        DATEADD(day, Number_Days, start_date) AS end_date_new,
        rn,
        Number_Days,
        start_date AS original_start,
        end_date AS original_end
    FROM ranked_data
    WHERE rn = 1

    UNION ALL

    -- 递归部分：基于前一行的修正后日期计算当前行
    SELECT 
        rd.id,
        -- 如果当前行原始开始日期 <= 前一行修正后的结束日期，就从次日开始；否则用原始开始日期
        CASE WHEN rd.start_date <= rd_prev.end_date_new THEN DATEADD(day, 1, rd_prev.end_date_new) ELSE rd.start_date END AS start_date_new,
        -- 计算修正后的结束日期
        DATEADD(day, rd.Number_Days, 
            CASE WHEN rd.start_date <= rd_prev.end_date_new THEN DATEADD(day, 1, rd_prev.end_date_new) ELSE rd.start_date END
        ) AS end_date_new,
        rd.rn,
        rd.Number_Days,
        rd.start_date AS original_start,
        rd.end_date AS original_end
    FROM ranked_data rd
    JOIN recursive_dates rd_prev 
        ON rd.id = rd_prev.id 
        AND rd.rn = rd_prev.rn + 1
)
-- 输出最终结果
SELECT 
    id,
    original_start AS start_date,
    original_end AS end_date,
    Number_Days,
    start_date_new,
    end_date_new
FROM recursive_dates
ORDER BY id, rn;

逻辑说明 #

• ranked_data：给每个ID分组内的行按原始start_date排序，生成行号rn，为递归遍历做准备。
• recursive_dates：
  • 锚点查询取每个ID的第一行，直接使用原始日期作为修正后日期。
  • 递归查询通过rn+1关联上一行，用上一行的修正后结束日期判断当前行是否需要顺延，确保每一行的修正都基于前一行的最终结果，彻底解决连锁重叠问题。

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