如何调整SQL查询以获取列值变更时的目标修改日期？

表结构与数据 #

我们有Transmission_Status表，结构和数据如下：

需求 #

需要获取NEW_VALUE列每次值变更时的对应首次生效日期（例如从Inactive变更为Terminated的日期应为5/15/2024，而非后续同一状态的重复更新日期7/11/2024）。

当前查询的问题 #

原查询仅通过rank()按日期倒序取最新一条记录，得到的是同一状态的后续重复更新日期，无法识别状态变更的节点。

调整后的SQL查询 #

思路 #

1. 先对重复的状态-日期记录去重，避免同一状态同一日期的多条数据干扰判断；
2. 使用LAG()函数获取前一条记录的状态值，对比当前状态值，筛选出状态发生变化的行（包括初始状态的第一条记录）；
3. 最终得到所有状态变更的节点日期。

代码实现 #

WITH deduplicated AS (
    -- 去重同一状态同一日期的重复记录
    SELECT DISTINCT key_value, field, new_value, date_modified
    FROM transmission_status
    WHERE key_value = 45 AND field = 'Transmission Status'
),
status_changes AS (
    SELECT 
        key_value, field, new_value, date_modified,
        -- 获取上一条记录的状态值
        LAG(new_value) OVER (PARTITION BY key_value ORDER BY date_modified) AS previous_status
    FROM deduplicated
)
-- 筛选状态变更的行：初始状态（无前置状态）或当前状态与前置状态不同
SELECT key_value, field, new_value, date_modified
FROM status_changes
WHERE previous_status IS NULL OR new_value != previous_status
ORDER BY date_modified;

查询结果 #

执行后将得到所有状态变更的节点日期：

如果仅需特定状态的变更日期（如Terminated），可在最后添加WHERE new_value = 'Terminated'条件。

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