SQL中特定状态列变更时更新LastUpdatedDate的实现问询

原始数据 #

期望结果 #

问题原因 #

单纯用FIRST_VALUE按状态分组时，无法识别状态回退的新分组（比如从1变回2），只会复用之前同状态的历史更新时间。

解决方案SQL（兼容多数主流数据库） #

WITH status_groups AS (
    SELECT 
        DWKey,
        [Original Id] AS OriginalId,
        PartnerProgramStatusDWKey,
        UpdatedTime,
        -- 标记状态变更的起始行：当前行与上一行状态不同则记为1
        CASE 
            WHEN LAG(PartnerProgramStatusDWKey) OVER (PARTITION BY [Original Id] ORDER BY UpdatedTime) != PartnerProgramStatusDWKey 
            THEN 1 
            ELSE 0 
        END AS is_new_group,
        -- 累计生成分组ID：每遇到新状态就递增分组ID，把连续同状态的行归为一组
        SUM(CASE 
                WHEN LAG(PartnerProgramStatusDWKey) OVER (PARTITION BY [Original Id] ORDER BY UpdatedTime) != PartnerProgramStatusDWKey 
                THEN 1 
                ELSE 0 
            END) OVER (PARTITION BY [Original Id] ORDER BY UpdatedTime ROWS UNBOUNDED PRECEDING) AS group_id
    FROM your_table_name
)
SELECT 
    DWKey,
    OriginalId AS [Original Id],
    PartnerProgramStatusDWKey,
    UpdatedTime,
    -- 取每个分组的首次更新时间作为该组的LastUpdatedDate
    FIRST_VALUE(UpdatedTime) OVER (PARTITION BY OriginalId, group_id ORDER BY UpdatedTime) AS LastUpdatedDate
FROM status_groups
ORDER BY UpdatedTime;

逻辑说明 #

1. 标记新状态组：用LAG()函数对比当前行与上一行的状态，状态不同则标记为新组的起始行。
2. 生成分组ID：对标记的新组行做累计求和，让连续同状态的行拥有相同的分组ID——哪怕状态是回退的（比如从1变回2），也会生成全新的分组ID。
3. 提取分组首时间：在每个OriginalId + group_id的分组内，用FIRST_VALUE()取最早的UpdatedTime，就是该状态组的LastUpdatedDate。

这样处理后，无论状态如何变更，只要状态发生变化，新状态组就会用当前的UpdatedTime作为LastUpdatedDate，完全符合需求。

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