如何用Lag/Lead函数判断员工是否重新雇佣并获取其雇佣周期？

需求说明 #

判断员工是否存在重新签订合同（重新雇佣）的情况：

• 若存在，返回其重新雇佣对应的Period记录；多名员工符合则返回所有相关记录
• 无重新雇佣情况时，返回NULL

示例数据 #

表名：Contract

Employee_id        Period         Contract 
111                  202204         1NA
111                  202205          1NA
111                  202206           1NA
112                  202207           1NA
112                  202208           1NA
111                  202209           1NA

示例输出 #

Employee_id       Period           Contract
111               202209            1NA

解决方案（优先使用LAG函数） #

逻辑思路 #

重新雇佣的核心特征是：员工之前有过合同记录，后续中断后再次签订（即当前Period与该员工上一次合同的Period间隔大于1）。利用LAG窗口函数可快速获取每个员工上一次的合同Period，再通过间隔判断筛选目标记录。

SQL代码 #

WITH employee_contracts AS (
    SELECT 
        Employee_id,
        Period,
        Contract,
        -- 获取当前员工上一次签订合同的Period
        LAG(Period) OVER (PARTITION BY Employee_id ORDER BY Period) AS previous_period
    FROM Contract
)
-- 筛选重新雇佣的记录
SELECT 
    Employee_id,
    Period,
    Contract
FROM employee_contracts
WHERE 
    previous_period IS NOT NULL 
    AND Period - previous_period > 1

-- 无符合记录时返回NULL
UNION ALL
SELECT NULL AS Employee_id, NULL AS Period, NULL AS Contract
FROM dual
WHERE NOT EXISTS (
    SELECT 1
    FROM employee_contracts
    WHERE previous_period IS NOT NULL AND Period - previous_period > 1
);

其他可行方案（自连接方式） #

如果无法使用窗口函数，可通过自连接实现相同逻辑：

-- 筛选重新雇佣的记录
SELECT 
    c1.Employee_id,
    c1.Period,
    c1.Contract
FROM Contract c1
JOIN Contract c2 ON c1.Employee_id = c2.Employee_id
WHERE 
    c1.Period > c2.Period
    -- 确保c2是c1之前最近的合同记录
    AND NOT EXISTS (
        SELECT 1 FROM Contract c3 
        WHERE c3.Employee_id = c1.Employee_id 
        AND c3.Period > c2.Period 
        AND c3.Period < c1.Period
    )
    -- 间隔大于1，说明存在中断
    AND c1.Period - c2.Period > 1

-- 无符合记录时返回NULL
UNION ALL
SELECT NULL, NULL, NULL
FROM dual
WHERE NOT EXISTS (
    SELECT 1
    FROM Contract c1
    JOIN Contract c2 ON c1.Employee_id = c2.Employee_id
    WHERE 
        c1.Period > c2.Period
        AND NOT EXISTS (
            SELECT 1 FROM Contract c3 
            WHERE c3.Employee_id = c1.Employee_id 
            AND c3.Period > c2.Period 
            AND c3.Period < c1.Period
        )
        AND c1.Period - c2.Period > 1
);

内容的提问来源于stack exchange，提问作者Ramya B S