如何处理SQL Server历史表中有无状态变更的行

表结构与测试数据 #

现有一张跟踪ID状态变化的历史表，包含历史数据（HistoryData=1）和当前数据（HistoryData=0）。StatusUpdateDate仅填充历史记录，CreateDate为所有记录的创建时间。表结构及测试数据如下：

CREATE TABLE #history_Card_status (
    HistoryData BIT,
    StatusUpdateDate DATETIME,
    CreateDate DATETIME,
    Id BIGINT,
    Status NVARCHAR(50)
);

INSERT INTO #history_Card_status (HistoryData, StatusUpdateDate, CreateDate, Id, Status)
VALUES
-- 当前数据
(0, NULL, '2023-06-13 05:07:38.700', 222, 'Open'),
(0, NULL, '2021-07-16 00:44:46.740', 111, 'Closed'),
-- 历史数据
(1, '2024-08-20 21:10:57.093', '2021-07-16 00:44:46.740', 111, 'Closed'),
(1, '2024-07-05 13:22:04.220', '2021-07-16 00:44:46.740', 111, 'Closed'),
(1, '2024-07-05 13:13:02.133', '2021-07-16 00:44:46.740', 111, 'Inactive/Block'),
(1, '2024-07-02 03:01:12.467', '2021-07-16 00:44:46.740', 111, 'Inactive/Block'),
(1, '2024-06-24 02:12:00.773', '2021-07-16 00:44:46.740', 111, 'Inactive/Block'),
(1, '2024-06-24 02:12:00.687', '2021-07-16 00:44:46.740', 111, 'Inactive/Block'),
(1, '2024-06-24 02:00:46.040', '2021-07-16 00:44:46.740', 111, 'Open'),
(1, '2024-06-24 02:00:44.303', '2021-07-16 00:44:46.740', 111, 'Open'),
(1, '2024-04-14 11:57:21.133', '2021-07-16 00:44:46.740', 111, 'Open'),
(1, '2024-04-14 11:52:09.073', '2021-07-16 00:44:46.740', 111, 'Open'),
(1, '2024-07-01 04:28:08.213', '2023-06-13 05:07:38.700', 222, 'Open'),
(1, '2024-07-01 03:39:54.607', '2023-06-13 05:07:38.700', 222, 'Open'),
(1, '2024-04-24 11:18:59.380', '2023-06-13 05:07:38.700', 222, 'Open'),
(1, '2024-04-24 11:18:59.227', '2023-06-13 05:07:38.700', 222, 'Open');

预期结果 #

需要返回每个ID的状态及对应日期范围，无论状态是否发生变更，预期输出如下：

StatusUpdateDate         StatusTillDate          Id      Status
-----------------------  ----------------------  ------  -----------
2024-07-05 13:13:02.133  2024-08-24 08:49:31.233  111     Closed
2024-06-24 02:00:46.040  2024-07-05 13:13:02.133  111     Inactive/Block
2021-07-16 00:44:46.740  2024-06-24 02:00:46.040  111     Open
2023-06-13 05:07:38.700  2024-08-24 08:49:31.233  222     Open

现有尝试及问题 #

第一次尝试 #

仅能返回有状态变更的ID，无法覆盖无变更的情况：

WITH Step1 AS (
    SELECT 
        StatusUpdateDate = ISNULL(StatusUpdateDate, ISNULL(history.CreateDate, '1970-01-01 00:00:00.000')),
        Id,
        Status,
        lag_status = LAG(Status) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC), 
        ChangeFlag = CASE 
            WHEN Status <> LAG(Status) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC) 
            THEN 1 ELSE 0 END 
    FROM #history_Card_status AS history
)
SELECT 
    StatusUpdateDate,
    StatusTillDate = ISNULL(LAG(StatusUpdateDate) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC), DATEADD(DAY, 2, GETDATE())),
    Id,
    Status = ISNULL(LAG(Status) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC), lag_status)
FROM Step1
WHERE ChangeFlag = 1;

返回结果仅包含ID111的状态变更记录，缺少ID222的无变更记录。

第二次尝试 #

尝试添加无变更记录的处理，但出现Status为NULL的问题：

WITH Step1 AS (
    SELECT 
        StatusUpdateDate = ISNULL(StatusUpdateDate, ISNULL(history.CreateDate, '1970-01-01 00:00:00.000')),
        Id,
        Status,
        lag_status = LAG(Status) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC), 
        ChangeFlag = CASE 
            WHEN Status <> LAG(Status) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC) 
            THEN 1 ELSE 0 END,
        rownum = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC) 
    FROM #history_Card_status AS history
)
SELECT 
    StatusUpdateDate,
    StatusTillDate = ISNULL(LAG(StatusUpdateDate) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC), DATEADD(DAY, 2, GETDATE())),
    Id,
    ChangeFlag,
    Status = ISNULL(LAG(Status) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC), lag_status),
    rownum
FROM Step1
WHERE ChangeFlag = 1
OR (ChangeFlag = 0 AND rownum = 1); 

返回结果中ID111和ID222的首行Status为NULL，不符合预期。

正确解决方案 #

通过窗口函数对连续相同状态的记录进行分组，再聚合得到每个状态的日期范围：

WITH AllRecords AS (
    -- 统一历史与当前记录的时间字段，标记当前记录
    SELECT 
        Id,
        Status,
        RecordDate = ISNULL(StatusUpdateDate, CreateDate),
        IsCurrent = HistoryData ^ 1
    FROM #history_Card_status
),
OrderedRecords AS (
    -- 按ID和时间排序，计算连续相同状态的分组ID
    SELECT 
        Id,
        Status,
        RecordDate,
        IsCurrent,
        StatusGroup = SUM(CASE WHEN Status = LAG(Status) OVER (PARTITION BY Id ORDER BY RecordDate) THEN 0 ELSE 1 END)
                     OVER (PARTITION BY Id ORDER BY RecordDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM AllRecords
),
StatusPeriods AS (
    -- 按分组聚合，得到每个状态的起始与结束日期
    SELECT 
        Id,
        Status,
        StatusStartDate = MIN(RecordDate),
        StatusEndDate = CASE
                            WHEN MAX(IsCurrent) = 1 THEN DATEADD(DAY, 2, GETDATE())
                            ELSE LEAD(MIN(RecordDate)) OVER (PARTITION BY Id ORDER BY MIN(RecordDate))
                        END
    FROM OrderedRecords
    GROUP BY Id, StatusGroup, Status
)
-- 输出最终结果，按ID和起始日期倒序排列
SELECT 
    StatusUpdateDate = StatusStartDate,
    StatusTillDate = StatusEndDate,
    Id,
    Status
FROM StatusPeriods
ORDER BY Id, StatusStartDate DESC;

逻辑说明 #

1. AllRecords：将历史记录的StatusUpdateDate和当前记录的CreateDate统一为RecordDate，用IsCurrent标记当前记录。
2. OrderedRecords：按ID和时间排序，通过窗口函数计算连续相同状态的分组ID，当状态与前一条不同时分组ID递增。
3. StatusPeriods：按分组聚合，取每个状态的最早时间作为起始日期；若分组包含当前记录，结束日期设为未来两天，否则取下一个状态的起始日期作为当前状态的结束日期。
4. 最后按ID和起始日期倒序输出，完全匹配预期结果。

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