如何识别客户表中当前最新ID的前一个历史变更ID值？

原始客户历史表 #

当前最新ID记录 #

期望输出（最新ID的前一个变更ID记录） #

尝试的方法及问题 #

直接使用LAG函数时，会包含同一ID的重复记录，导致结果不符合预期：

select * from (
select Name, id, lag(id,1) over (partition by Name order by date) as lag_id 
from customer_history
) t

执行结果：

解决方案 #

需要先过滤掉同一ID的重复记录，只保留每个ID的最新日期记录，再获取最新ID的前一个变更记录：

WITH ranked_ids AS (
    -- 对每个客户的每个ID，保留最新的一条记录
    SELECT 
        Name,
        ID,
        Date,
        ROW_NUMBER() OVER (PARTITION BY Name, ID ORDER BY Date DESC) AS rn
    FROM customer_history
),
distinct_id_history AS (
    -- 得到每个客户不同ID的唯一最新记录
    SELECT Name, ID, Date
    FROM ranked_ids
    WHERE rn = 1
),
final_ranked AS (
    -- 对每个客户的ID变更记录按日期降序排序
    SELECT 
        Name,
        ID,
        Date,
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date DESC) AS rn
    FROM distinct_id_history
)
-- 取排序后的第二条，即最新ID的前一个变更记录
SELECT Name, ID, Date
FROM final_ranked
WHERE rn = 2;

思路说明 #

1. 去重同一ID的记录：同一个ID可能有多条历史记录，只保留每个ID的最新日期记录，避免重复干扰。
2. 排序ID变更记录：对每个客户的不同ID记录按日期降序排列，最新的ID排在第1位，前一个变更的ID就是第2位。
3. 筛选目标记录：提取排序后的第2条记录，即为所需结果。

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