补全并修正员工薪资起止日期序列中的空值结束日期

问题概述 #

现有来自Paycor API的员工薪资数据，部分员工的生效起止日期逻辑混乱，需修正为仅保留当前生效薪资的effectiveEndDate为NULL，其余记录的effectiveEndDate设为下一条薪资生效日期的前一天，用于后续YTD、MTD人工成本计算。

原始数据 #

目标数据 #

解决方案 #

利用窗口函数LEAD()按员工分区、生效日期排序，自动推导每条记录的结束日期，无需依赖日历表。

SQL代码（MySQL示例） #

WITH sorted_salaries AS (
    SELECT
        EmployeeID,
        STR_TO_DATE(effectiveStartDate, '%Y-%m-%d') AS effective_start_date,
        effectiveEndDate,
        Rate,
        -- 获取同员工下一条薪资的生效日期
        LEAD(STR_TO_DATE(effectiveStartDate, '%Y-%m-%d')) OVER (
            PARTITION BY EmployeeID 
            ORDER BY STR_TO_DATE(effectiveStartDate, '%Y-%m-%d')
        ) AS next_start_date
    FROM your_table_name
)
SELECT
    EmployeeID,
    DATE_FORMAT(effective_start_date, '%Y-%m-%d') AS effectiveStartDate,
    -- 计算结束日期：下一条生效日的前一天，无下一条则为NULL
    CASE
        WHEN next_start_date IS NOT NULL THEN DATE_SUB(next_start_date, INTERVAL 1 DAY)
        ELSE NULL
    END AS effectiveEndDate,
    Rate
FROM sorted_salaries
ORDER BY EmployeeID, effective_start_date;

核心逻辑 #

1. 分区排序：通过PARTITION BY EmployeeID将数据按员工分组，ORDER BY effective_start_date确保同员工记录按生效时间先后排列。
2. 获取下一条生效日期：LEAD()函数提取当前记录的下一条薪资生效日期，作为当前薪资的失效节点。
3. 修正结束日期：用CASE判断，若存在下一条生效日期，则将当前记录的effectiveEndDate设为该日期的前一天；若无下一条记录（即最新生效薪资），则保留NULL。

适配其他SQL方言 #

• SQL Server：将DATE_SUB(next_start_date, INTERVAL 1 DAY)替换为DATEADD(DAY, -1, next_start_date)，日期转换用CONVERT(DATE, effectiveStartDate)。
• PostgreSQL：将DATE_SUB(next_start_date, INTERVAL 1 DAY)替换为next_start_date - INTERVAL '1 day'，日期转换用TO_DATE(effectiveStartDate, 'YYYY-MM-DD')。

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