DB2如何从审计表获取rank字段最新值、上一值及变更时间戳

问题原因 #

你的原有SQL存在两个核心问题：

1. 自连接逻辑会匹配到所有早于当前日志时间的历史记录，没有过滤出和当前记录最近的前一次rank值，返回的行存在大量冗余
2. 窗口函数row_number按时间正序排序，取到的是最早的变更记录而非最近一次的rank变更记录

正确实现方案 #

推荐直接用LAG窗口函数实现，无需自关联，性能更优且天然适配NULL值处理场景：

WITH rank_log AS (
    SELECT
        id,
        rank AS current_rank,
        -- 取同id按时间排序的上一行rank值，即为变更前值
        LAG(rank) OVER (PARTITION BY id ORDER BY log_timestamp) AS previous_rank,
        log_timestamp,
        log_username
    FROM `user`
),
rank_change_filter AS (
    SELECT
        *,
        -- 过滤出rank发生变更的记录，NULL值兼容判断
        CASE 
            WHEN rank = previous_rank OR (rank IS NULL AND previous_rank IS NULL) THEN 0
            ELSE 1 
        END AS is_changed
    FROM rank_log
),
latest_change AS (
    SELECT
        *,
        -- 按id分组，对变更记录按时间倒序排，取最新的一条
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY log_timestamp DESC) AS rnum
    FROM rank_change_filter
    WHERE is_changed = 1
)
SELECT 
    id,
    current_rank,
    previous_rank,
    log_timestamp,
    log_username
FROM latest_change
WHERE rnum = 1;

如果你的数据库支持IS NOT DISTINCT FROM语法，NULL值判断逻辑可以简化为：

CASE WHEN rank IS NOT DISTINCT FROM previous_rank THEN 0 ELSE 1 END AS is_changed

该SQL会返回每个id最新一次rank变更对应的当前值、变更前值、变更时间戳、操作人信息，rank为NULL时也会正确返回空值，完全符合预期输出要求。

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