如何使用SQL LAG函数查询员工当前及历任岗位职位职级数据

实现逻辑说明 #

基于员工任职表asg，通过窗口函数按员工分区、按任职起始时间排序，拉取每段任职对应的上一段任职信息，筛选当前生效的任职记录中岗位编码（JOB_CODE）或职级编码（GRADE_CODE）发生变动的人员，最终计算上一岗位的任职时长，按要求格式输出。

注：原表字段POS_CDOE为拼写笔误，以下SQL统一修正为POS_CODE；表中END_DATE='31-DEC-4712'为HR系统通用的当前生效记录标识，代表该段任职至今有效。

可直接运行的SQL代码（Oracle语法，适配该类HR系统表结构） #

WITH asg_with_prev AS (
    SELECT
        ASG_NUMBER,
        START_DATE,
        END_DATE,
        JOB_CODE,
        GRADE_CODE,
        POS_CODE,
        -- 用LAG取同一位员工上一段任职的对应字段，按任职开始时间正序排序
        LAG(JOB_CODE) OVER (PARTITION BY ASG_NUMBER ORDER BY START_DATE) AS PREV_JOB_CODE,
        LAG(GRADE_CODE) OVER (PARTITION BY ASG_NUMBER ORDER BY START_DATE) AS PREV_GRADE_CODE,
        LAG(POS_CODE) OVER (PARTITION BY ASG_NUMBER ORDER BY START_DATE) AS PREV_POS_CODE,
        LAG(START_DATE) OVER (PARTITION BY ASG_NUMBER ORDER BY START_DATE) AS PREV_START_DATE,
        LAG(END_DATE) OVER (PARTITION BY ASG_NUMBER ORDER BY START_DATE) AS PREV_END_DATE
    FROM asg
)
SELECT
    ASG_NUMBER,
    POS_CODE AS CUR_POS_CODE,
    JOB_CODE AS CUR_JOB_CODE,
    GRADE_CODE AS CUR_GRADE_CODE,
    PREV_JOB_CODE,
    PREV_GRADE_CODE,
    -- 上一职位如果和当前一致可按需置空，和样例输出对齐
    CASE WHEN PREV_POS_CODE != POS_CODE THEN PREV_POS_CODE ELSE NULL END AS PREV_POS_CODE,
    TO_CHAR(START_DATE, 'DD-MON-YYYY') AS Curr_date,
    TO_CHAR(PREV_START_DATE, 'DD-MON-YYYY') AS Prev_date,
    -- 计算上一段任职时长，按年、月格式拼接
    CASE
        WHEN TRUNC(MONTHS_BETWEEN(PREV_END_DATE, PREV_START_DATE)/12) > 0
        THEN TRUNC(MONTHS_BETWEEN(PREV_END_DATE, PREV_START_DATE)/12) || ' y '
             || MOD(MONTHS_BETWEEN(PREV_END_DATE, PREV_START_DATE), 12) || ' m'
        ELSE MOD(MONTHS_BETWEEN(PREV_END_DATE, PREV_START_DATE), 12) || ' m'
    END AS "Time in previous pos(Y m)"
FROM asg_with_prev
-- 筛选当前生效的记录
WHERE END_DATE = DATE '4712-12-31'
-- 筛选JOB_CODE或GRADE_CODE发生变动的记录，排除首次入职无上一段任职的情况
AND PREV_JOB_CODE IS NOT NULL
AND (JOB_CODE != PREV_JOB_CODE OR GRADE_CODE != PREV_GRADE_CODE);

关键逻辑点说明 #

• LAG()窗口函数可避免低效自关联，单次扫描即可按排序规则取同分组内的上一行数据：这里按ASG_NUMBER分区保证只取同一位员工的历史任职，按START_DATE排序保证任职顺序匹配实际任职轨迹。
• 任职时长计算用MONTHS_BETWEEN直接计算两个日期的整月差，拆分年、月时做格式适配：当年数为0时只显示月数，和期望输出格式对齐。
• 筛选条件仅判定JOB_CODE、GRADE_CODE的变动，POS_CODE如果未发生变动可以按样例要求置空，不需要作为变动判定条件。

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