药品日期无重叠前移SQL查询异常：ID234数据失效的修正咨询

需求回顾 #

针对同一ID、同一药品的用药记录，生成修正后的日期列Nw_Start和Nw_End，规则如下：

• 组内首行记录：Nw_Start = 原Start_dt，Nw_End = 原End_dt
• 当前行Start_dt > 上一行修正后的Nw_End：无重叠，保持原日期
• 当前行Start_dt ≤ 上一行修正后的Nw_End：存在重叠，Nw_Start = 上一行Nw_End + 1天，Nw_End = Nw_Start + days_supply - 1天

问题根源 #

原SQL大概率使用了普通窗口函数（如LAG()）直接引用上一行的原始End_dt进行判断，而非依赖上一行已经修正后的Nw_End。这种方式无法处理累积推移的场景——当前面的记录已经被向后推移后，后续行的判断基准应该是修正后的日期，而非原始日期，这就是ID234从第4行开始计算错误的核心原因。

修正方案：递归CTE实现逐行累积计算 #

递归CTE可以逐行处理每个分组内的记录，确保每一行的修正值都基于上一行的最终修正结果，完美适配这种依赖前置行计算结果的场景。

通用SQL代码（适用于PostgreSQL、SQL Server等支持递归CTE的数据库）

WITH ranked_data AS (
    -- 按ID、药品分组，按用药起始日期排序，生成行号
    SELECT 
        ID,
        drug_code,
        Start_dt,
        End_dt,
        days_supply,
        ROW_NUMBER() OVER (PARTITION BY ID, drug_code ORDER BY Start_dt) AS rn
    FROM your_table_name
),
recursive_cte AS (
    -- 锚点成员：初始化每组的第一行修正日期
    SELECT 
        ID,
        drug_code,
        Start_dt,
        End_dt,
        days_supply,
        rn,
        Start_dt AS Nw_Start,
        End_dt AS Nw_End
    FROM ranked_data
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归成员：逐行计算后续记录的修正日期
    SELECT 
        rd.ID,
        rd.drug_code,
        rd.Start_dt,
        rd.End_dt,
        rd.days_supply,
        rd.rn,
        -- 判断是否重叠，确定新的起始日期
        CASE 
            WHEN rd.Start_dt > rc.Nw_End THEN rd.Start_dt
            ELSE rc.Nw_End + INTERVAL '1 day'
        END AS Nw_Start,
        -- 计算新的结束日期
        CASE 
            WHEN rd.Start_dt > rc.Nw_End THEN rd.End_dt
            ELSE (rc.Nw_End + INTERVAL '1 day') + (rd.days_supply - 1) * INTERVAL '1 day'
        END AS Nw_End
    FROM ranked_data rd
    JOIN recursive_cte rc 
        ON rd.ID = rc.ID 
        AND rd.drug_code = rc.drug_code 
        AND rd.rn = rc.rn + 1
)
-- 输出最终结果
SELECT 
    ID,
    drug_code,
    Start_dt,
    End_dt,
    days_supply,
    Nw_Start,
    Nw_End
FROM recursive_cte
ORDER BY ID, drug_code, rn;

MySQL适配版本

MySQL的日期计算语法略有不同，调整后的代码如下：

WITH ranked_data AS (
    SELECT 
        ID,
        drug_code,
        Start_dt,
        End_dt,
        days_supply,
        ROW_NUMBER() OVER (PARTITION BY ID, drug_code ORDER BY Start_dt) AS rn
    FROM your_table_name
),
recursive_cte AS (
    SELECT 
        ID,
        drug_code,
        Start_dt,
        End_dt,
        days_supply,
        rn,
        Start_dt AS Nw_Start,
        End_dt AS Nw_End
    FROM ranked_data
    WHERE rn = 1
    
    UNION ALL
    
    SELECT 
        rd.ID,
        rd.drug_code,
        rd.Start_dt,
        rd.End_dt,
        rd.days_supply,
        rd.rn,
        CASE 
            WHEN rd.Start_dt > rc.Nw_End THEN rd.Start_dt
            ELSE DATE_ADD(rc.Nw_End, INTERVAL 1 DAY)
        END AS Nw_Start,
        CASE 
            WHEN rd.Start_dt > rc.Nw_End THEN rd.End_dt
            ELSE DATE_ADD(DATE_ADD(rc.Nw_End, INTERVAL 1 DAY), INTERVAL (rd.days_supply - 1) DAY)
        END AS Nw_End
    FROM ranked_data rd
    JOIN recursive_cte rc 
        ON rd.ID = rc.ID 
        AND rd.drug_code = rc.drug_code 
        AND rd.rn = rc.rn + 1
)
SELECT 
    ID,
    drug_code,
    Start_dt,
    End_dt,
    days_supply,
    Nw_Start,
    Nw_End
FROM recursive_cte
ORDER BY ID, drug_code, rn;

方案说明 #

1. ranked_data CTE：先对数据按ID、药品分组，再按Start_dt排序生成行号，确保递归时能按顺序处理每一行。
2. 递归CTE的锚点成员：初始化每组第一行的修正日期，直接使用原始日期。
3. 递归成员：通过关联上一行的递归结果，用修正后的Nw_End作为判断基准，计算当前行的修正日期，彻底解决累积推移的依赖问题。

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