SQL实现每日数据表转换为带ID、状态、日期区间的单行记录

问题原因 #

你之前使用的DENSE_RANK Over(Partition by Id, Status order by Effective Date)逻辑存在缺陷：如果同一个ID的状态出现过多次往返变更（例如先Inactive→Active→再次切回Inactive），两次Inactive时段的记录会被分到同一个排名组，无法区分不同时段的同状态连续区间，因此无法得到正确的起止日期结果。

具体实现思路 #

这是SQL场景中典型的「岛屿问题」，可以通过双行号差值法实现连续同状态的分组，步骤如下：

• 第一步：对每个ID单独按生效日期升序排序，生成全局行号rn
• 第二步：对每个ID+Status的组合按生效日期升序排序，生成状态内行号rn_status
• 第三步：计算两个行号的差值作为分组标识group_id：如果状态连续未变更，两个行号同步递增，差值固定不变；如果状态发生变更，状态内行号会重新计数，差值发生变化，以此区分不同的状态区间
• 第四步：按ID、Status、group_id分组，取生效日期的最小值作为起始日期、最大值作为结束日期即可

示例代码 #

WITH step1 AS (
    SELECT
        ID,
        Status,
        EffectiveDate,
        -- 每个ID内的全局行号
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY EffectiveDate) AS rn,
        -- 每个ID+状态内的行号
        ROW_NUMBER() OVER(PARTITION BY ID, Status ORDER BY EffectiveDate) AS rn_status
    FROM your_daily_table
),
step2 AS (
    SELECT
        *,
        rn - rn_status AS group_id
    FROM step1
)
SELECT
    ID,
    Status,
    MIN(EffectiveDate) AS BeginDate,
    MAX(EffectiveDate) AS EndDate
FROM step2
GROUP BY ID, Status, group_id
ORDER BY ID, BeginDate;

注意事项 #

• 若Status存在大小写不一致的情况（如示例中的Active和active），可先通过UPPER(Status)或LOWER(Status)统一格式后再计算行号，避免同状态被误拆分
• 若需要标识当前仍生效的最新状态，可将对应区间的EndDate替换为当前日期，或自定义9999-12-31这类特殊值标记存续状态

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