如何在SQL Server中用CTE计算分区行的End_Date（含重复Type场景）

需求说明 #

• 分区规则：按Person+连续Type划分，同一Person下Type发生变更（包括Type重复但中断后再次出现）时，创建新分区
• End_Date定义：同一Person下下一个Type分区的Start_Date，每个Person的最后一个分区End_Date为NULL

源数据 #

Person    Type     dt_eff
123       ABC      2018-10-23
123       DEF      2018-12-19
124       ABC      2020-01-01
124       ABC      2020-02-15
124       ABC      2020-05-14
124       DEF      2020-10-13
124       ABC      2021-01-15

预期输出 #

Person    Type     Start_Date   End_Date
123       ABC      2018-10-23   2018-12-19
123       DEF      2018-12-19   NULL
124       ABC      2020-01-01   2020-10-13
124       ABC      2020-02-15   2020-10-13
124       ABC      2020-05-14   2020-10-13
124       DEF      2020-10-13   2021-01-15
124       ABC      2021-01-15   NULL

解决方案 #

通过嵌套CTE分三步实现：

WITH PartitionedData AS (
    -- 第一步：标记同一Person下的连续Type分区ID
    SELECT 
        Person,
        Type,
        dt_eff,
        SUM(CASE WHEN LAG(Type) OVER (PARTITION BY Person ORDER BY dt_eff) = Type THEN 0 ELSE 1 END) 
            OVER (PARTITION BY Person ORDER BY dt_eff) AS PartitionId
    FROM YourTableName
),
PartitionDates AS (
    -- 第二步：获取每个分区的起始日期，以及下一个分区的起始日期作为当前分区的End_Date
    SELECT 
        Person,
        PartitionId,
        MIN(dt_eff) AS PartitionStart,
        LEAD(MIN(dt_eff)) OVER (PARTITION BY Person ORDER BY MIN(dt_eff)) AS End_Date
    FROM PartitionedData
    GROUP BY Person, PartitionId
)
-- 第三步：关联原始数据与分区日期信息，输出最终结果
SELECT 
    pd.Person,
    pd.Type,
    pd.dt_eff AS Start_Date,
    pdates.End_Date
FROM PartitionedData pd
JOIN PartitionDates pdates 
    ON pd.Person = pdates.Person 
    AND pd.PartitionId = pdates.PartitionId
ORDER BY pd.Person, pd.dt_eff;

关键逻辑说明 #

• LAG(Type) OVER (PARTITION BY Person ORDER BY dt_eff)：获取同一Person中上一行的Type，判断当前行是否属于新分区
• SUM(...) OVER (PARTITION BY Person ORDER BY dt_eff)：累加分区变更标记，生成每个Person的唯一分区ID
• LEAD(MIN(dt_eff)) OVER (...)：获取同一Person中下一个分区的起始日期，作为当前分区的End_Date
• 最终通过关联将原始数据行映射到对应分区的日期信息

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