SQL Server如何获取每个client_sysid最新package_level变更首条记录

表结构 #

现有hcp_funding_packages表结构如下：

client_sysid (int)
package_level (int)
created_at (datetime)

需求说明 #

针对每个client_sysid，获取package_level发生最近一次变更对应的第一条记录。
参考示例数据如下，client_sysid为1的用户，package_level最近一次变更是从3改回1，对应首条记录就是2021-01-04的粗体行：

实现SQL #

通过窗口函数标记相邻行的等级变更，再筛选最近一次变更的首条记录即可：

WITH mark_changed AS (
    -- 标记当前行和前一行的package_level是否不同，不同视为发生变更
    SELECT 
        *,
        CASE WHEN LAG(package_level) OVER (PARTITION BY client_sysid ORDER BY created_at) <> package_level THEN 1 ELSE 0 END AS is_changed
    FROM hcp_funding_packages
),
rank_changed AS (
    -- 对每个client的变更记录按时间倒序排序，最近的变更排第一位
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY client_sysid ORDER BY CASE WHEN is_changed = 1 THEN created_at END DESC) AS rn
    FROM mark_changed
)
-- 筛选每个client最近一次变更的记录
SELECT client_sysid, package_level, created_at
FROM rank_changed
WHERE rn = 1

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