SQL查询问题：如何正确获取员工职级变动记录

问题背景 #

我正在学习SQL的PARTITION BY和CTE，需要查询2022年初至今的员工职级晋升记录。现有两张核心表：

• EMPLOYEES：存储员工当前职级信息
• PERIODS：每月员工职级快照表，包含PeriodID（快照时间标识，格式如YYYYMM）、RankID（职级ID），员工在同一个月内可能存在多次晋升记录

我参考网络代码修改了CTE查询，但结果不符合预期：出现重复的当前职级记录，且PeriodID始终显示为同一个固定值，求帮忙排查错误。

当前查询代码 #

WITH RankChanges AS (
    SELECT 
        e.EmployeeID,
        e.RankID AS CurrentRank,
        p.RankID AS HistoricalRank,
        p.PeriodID,
        ROW_NUMBER() OVER (PARTITION BY e.EmployeeID ORDER BY p.PeriodID DESC) AS rn
    FROM EMPLOYEES e
    JOIN PERIODS p ON e.EmployeeID = p.EmployeeID
    WHERE p.PeriodID >= '202201'
)
SELECT 
    EmployeeID,
    CurrentRank,
    HistoricalRank,
    PeriodID
FROM RankChanges
WHERE rn > 1
ORDER BY EmployeeID, PeriodID;

结果对比 #

预期结果 #

实际结果 #

问题根源分析 #

1. 重复职级问题：直接将EMPLOYEES表的当前职级与PERIODS表的所有历史快照做关联，导致每一条历史记录都匹配同一个当前职级，无法体现职级的阶段性变化。
2. 固定PeriodID问题：ROW_NUMBER() OVER (PARTITION BY e.EmployeeID ORDER BY p.PeriodID DESC)是给每个员工的快照按时间倒序排号，rn=1是最新快照，但筛选rn>1时，并没有正确定位职级变动的时间节点，反而错误地输出了所有历史快照，且PeriodID显示异常（本质是逻辑未聚焦于相邻快照的对比，只是简单过滤掉了最新快照）。

修正后的查询代码 #

WITH RankHistory AS (
    SELECT 
        EmployeeID,
        RankID,
        PeriodID,
        -- 获取同一员工上一次快照的职级
        LAG(RankID) OVER (PARTITION BY EmployeeID ORDER BY PeriodID) AS PreviousRank
    FROM PERIODS
    WHERE PeriodID >= '202201'
),
RankPromotions AS (
    SELECT 
        EmployeeID,
        PreviousRank AS HistoricalRank,
        RankID AS NewRank,
        PeriodID AS PromotionPeriod
    FROM RankHistory
    -- 筛选职级提升的记录（若需包含所有职级变动，去掉`RankID > PreviousRank`即可）
    WHERE PreviousRank IS NOT NULL AND RankID > PreviousRank
)
SELECT 
    rp.EmployeeID,
    e.RankID AS CurrentRank,
    rp.HistoricalRank,
    rp.PromotionPeriod AS PeriodID
FROM RankPromotions rp
JOIN EMPLOYEES e ON rp.EmployeeID = e.EmployeeID
ORDER BY rp.EmployeeID, rp.PromotionPeriod;

修正说明 #

1. 聚焦历史快照的相邻对比：用LAG()函数在PERIODS表内按员工分组、按PeriodID正序排序，精准获取每条快照对应的上一次职级，定位每次职级变动的时间节点。
2. 精准筛选晋升记录：通过对比当前快照职级与上一次职级，只保留职级提升的记录，避免无效的重复职级输出。
3. 正确关联当前职级：最后关联EMPLOYEES表获取员工当前职级，确保当前职级只显示一次，同时清晰展示每次晋升的历史职级和时间。

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