使用MySQL 8窗口函数计算职级对应薪资的问题

原查询仅通过employeeId关联两张表，未考虑职级与薪资的生效/失效区间重叠关系，导致天数匹配错误，且出现无重叠的无效关联结果。需先明确各职级、薪资的完整生效区间，再计算两者的重叠区间天数及对应总薪资。

修正后的查询语句 #

WITH EmployeeRoleWithEnd AS (
    SELECT 
        employeeId,
        grade,
        roleEffectiveFrom,
        -- 计算职级失效日期：下一个职级生效日的前一天，若无则取当月最后一天
        COALESCE(
            DATE_SUB(LEAD(roleEffectiveFrom) OVER (PARTITION BY employeeId ORDER BY roleEffectiveFrom), INTERVAL 1 DAY),
            LAST_DAY(roleEffectiveFrom)
        ) AS roleEndDate
    FROM EmployeeRole
    WHERE employeeId = 19966
),
EmployeeSalaryWithEnd AS (
    SELECT 
        employeeId,
        salary,
        salaryEffectiveFrom,
        -- 计算薪资失效日期：下一个薪资生效日的前一天，若无则取当月最后一天
        COALESCE(
            DATE_SUB(LEAD(salaryEffectiveFrom) OVER (PARTITION BY employeeId ORDER BY salaryEffectiveFrom), INTERVAL 1 DAY),
            LAST_DAY(salaryEffectiveFrom)
        ) AS salaryEndDate
    FROM EmployeeSalary
    WHERE employeeId = 19966
)
SELECT 
    er.employeeId,
    es.salary,
    er.grade,
    er.roleEffectiveFrom,
    es.salaryEffectiveFrom,
    GREATEST(er.roleEffectiveFrom, es.salaryEffectiveFrom) AS periodStart,
    LEAST(er.roleEndDate, es.salaryEndDate) AS periodEnd,
    -- 计算重叠区间天数（包含首尾日期）
    DATEDIFF(LEAST(er.roleEndDate, es.salaryEndDate), GREATEST(er.roleEffectiveFrom, es.salaryEffectiveFrom)) + 1 AS `#Days`,
    ROUND((es.salary * 12) / 365, 2) AS dailyRate,
    -- 计算该区间总薪资
    ROUND((es.salary * 12) / 365 * (DATEDIFF(LEAST(er.roleEndDate, es.salaryEndDate), GREATEST(er.roleEffectiveFrom, es.salaryEffectiveFrom)) + 1), 2) AS totalSalary
FROM EmployeeRoleWithEnd er
JOIN EmployeeSalaryWithEnd es 
    ON er.employeeId = es.employeeId
    -- 仅保留职级与薪资区间有重叠的记录
    AND er.roleEffectiveFrom <= es.salaryEndDate
    AND es.salaryEffectiveFrom <= er.roleEndDate
ORDER BY er.roleEffectiveFrom, es.salaryEffectiveFrom;

查询结果 #

employeeId  salary  grade    roleEffectiveFrom  salaryEffectiveFrom  periodStart  periodEnd   #Days  dailyRate  totalSalary
19966       10000   grade 3  2022-07-01         2022-07-01           2022-07-01   2022-07-09  9      328.77     2958.93
19966       10000   grade 2  2022-07-10         2022-07-01           2022-07-10   2022-07-14  5      328.77     1643.85
19966       20000   grade 2  2022-07-10         2022-07-15           2022-07-15   2022-07-31  17     657.53     11178.01

结果说明 #

• grade 3的生效区间为2022-07-01至2022-07-09，与薪资10000的区间完全重叠，共9天，总薪资为328.77*9=2958.93
• grade 2的生效区间为2022-07-10至2022-07-31，其中2022-07-10至2022-07-14与薪资10000重叠，共5天；2022-07-15至2022-07-31与薪资20000重叠，共17天，分别计算对应总薪资

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