如何用T-SQL为非连续重复Type序列生成唯一Num列？

一、生成Table_1中的[Num]列 #

要实现同一Type连续日期对应相同Num，间隔1天以上则序号递增，可通过窗口函数标记连续区间再累计序号的方式实现：

WITH RankedData AS (
    SELECT 
        Date,
        Type,
        -- 标记当前行是否为新的连续区间起点：同Type的上一行日期与当前日期差超过1天，或为该Type的第一行
        CASE 
            WHEN LAG(Date) OVER (PARTITION BY Type ORDER BY Date) IS NULL 
                THEN 1
            WHEN DATEDIFF(day, LAG(Date) OVER (PARTITION BY Type ORDER BY Date), Date) > 1
                THEN 1
            ELSE 0
        END AS IsNewGroup
    FROM YourSourceTable -- 替换为你的源表名
)
SELECT 
    Date,
    Type,
    -- 累计求和得到每个Type的连续区间序号
    SUM(IsNewGroup) OVER (PARTITION BY Type ORDER BY Date) AS Num
FROM RankedData
ORDER BY Date;

逻辑说明： #

• LAG(Date) OVER (PARTITION BY Type ORDER BY Date)：获取当前行同Type的上一行日期
• IsNewGroup：标记是否为新的连续区间起点（首次出现或间隔超过1天）
• SUM(IsNewGroup) OVER (...)：对IsNewGroup累计求和，得到每个连续区间的唯一序号Num

二、直接生成目标SCD维度表Table_2（无需先生成Num列） #

如果最终目标是生成带起止日期的SCD表，可跳过Num列，直接通过分组连续区间实现，效率更高：

WITH GroupedData AS (
    SELECT 
        Date,
        Type,
        -- 计算每个行的分组ID：用当前日期减去该Type下的行号，连续日期会得到相同的基准值
        DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Date), Date) AS GroupID
    FROM YourSourceTable -- 替换为你的源表名
)
SELECT 
    MIN(Date) AS [Date], -- 取区间首行日期作为展示用的Date
    Type,
    MIN(Date) AS [Start Date],
    MAX(Date) AS [End Date]
FROM GroupedData
GROUP BY Type, GroupID
ORDER BY [Start Date];

逻辑说明： #

• ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Date)：对每个Type的行按日期排序编号
• DATEADD(day, -ROW_NUMBER(), Date)：连续日期的行，该计算结果会相同（比如2022-01-01减1=2021-12-31，2022-01-02减2=2021-12-31），以此作为分组标识
• 按Type和GroupID分组后，取最小/最大日期即为区间的起止日期

三、特殊场景处理 #

针对示例中2022-01-12同时存在Resting和Traveling的情况，上述两种方案均能正确识别，因为分组是基于Type和日期的组合，不同Type的行不会互相干扰。

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