通过SQL审计表定位Location变更：解决自连接查询重复问题

我在SQL数据库中有如下结构的audit table：

需要编写查询语句，找出表中location发生变更的记录，展示id、旧location、新location以及变更时间。

我尝试过自连接查询，但会返回重复记录，不符合需求：

SELECT
    a.id,
    a.location       AS old_loc, 
    a.dtm_utc_action AS FirstModifyDate,
    b.location       AS new_loc, 
    b.dtm_utc_action AS SecondModifyDate  

FROM
    audit_table a
    JOIN audit_table b ON a.id = b.id

WHERE
    a.audit_id <> b.audit_id
    AND
    a.dtm_utc_action < b.dtm_utc_action
    AND
    a.location <> b.location
    AND
    a.id = '2105'

期望结果格式：

要避免重复记录，关键是只关联每个id的相邻版本记录，可以用窗口函数LAG()获取上一条记录的location值，直接对比当前与上一条的location是否变更：

SELECT
    id,
    prev_location AS old_loc,
    location AS new_loc,
    dtm_utc_action
FROM (
    SELECT
        id,
        location,
        dtm_utc_action,
        -- 按id分组、操作时间排序，获取当前行的上一行location值
        LAG(location) OVER (PARTITION BY id ORDER BY dtm_utc_action) AS prev_location
    FROM audit_table
    WHERE id = '2105' -- 若要查询所有id可去掉此条件
) AS sub
-- 过滤出location变更的记录，排除无前置记录的首行
WHERE prev_location IS NOT NULL AND prev_location <> location
ORDER BY dtm_utc_action;

说明： #

1. LAG(location) OVER (PARTITION BY id ORDER BY dtm_utc_action)：按id分组、操作时间排序，提取当前行的上一行location值。
2. 子查询后过滤掉prev_location为空的行（即第一条无旧值的记录），只保留前后location不同的行，得到每次变更的有效记录。
3. 若需查询所有id的变更记录，删除WHERE id = '2105'条件即可。

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