基于单日期字段计算员工角色任职天数的技术问询

问题分析 #

你提供的原有SQL查询存在核心逻辑缺陷：按employeeId和effectiveFrom分组后，每组仅包含单条角色变动记录，导致MAX(effectiveFrom)与MIN(effectiveFrom)为同一日期，计算结果永远为1天，完全未考虑角色任期的结束时间。

解决方案 #

使用窗口函数LEAD()获取角色变动的结束时间，再通过递归CTE拆分跨月任期，最终按员工、角色、月份统计任职天数，完美匹配你提出的三个场景需求。

完整SQL代码 #

WITH EmployeeRoleTerms AS (
    -- 获取每个角色任期的开始/结束日期
    SELECT
        employeeId,
        grade,
        effectiveFrom AS term_start,
        -- 下一次角色变动日期作为任期结束；无后续变动则设为次月1日，确保统计当月完整天数
        COALESCE(
            LEAD(effectiveFrom) OVER (PARTITION BY employeeId ORDER BY effectiveFrom),
            LAST_DAY(effectiveFrom) + INTERVAL 1 DAY
        ) AS term_end
    FROM EmployeeRoles
),
MonthlyTermSplits AS (
    -- 递归拆分跨月任期
    SELECT
        employeeId,
        grade,
        term_start,
        term_end,
        term_start AS month_start,
        LEAST(LAST_DAY(term_start), term_end) AS month_end
    FROM EmployeeRoleTerms
    UNION ALL
    SELECT
        employeeId,
        grade,
        term_start,
        term_end,
        month_start + INTERVAL 1 MONTH,
        LEAST(LAST_DAY(month_start + INTERVAL 1 MONTH), term_end)
    FROM MonthlyTermSplits
    WHERE month_start + INTERVAL 1 MONTH < term_end
)
-- 按员工、角色、月份统计任职天数
SELECT
    employeeId,
    grade,
    DATE_FORMAT(month_start, '%Y-%m') AS `month`,
    DATEDIFF(month_end, month_start) AS days_worked
FROM MonthlyTermSplits
WHERE grade = 'A'
GROUP BY employeeId, grade, `month`
ORDER BY employeeId, `month`;

逻辑说明 #

1. EmployeeRoleTerms CTE

   • 用LEAD()窗口函数获取同一位员工的下一次角色变动日期，作为当前角色任期的结束时间。
   • 若为最后一次角色变动（无后续记录），用LAST_DAY(effectiveFrom) + INTERVAL 1 DAY作为任期结束，确保能统计到当月完整天数。

2. MonthlyTermSplits CTE

   • 递归拆分跨月的任期：例如一个从2021-01-01到2021-03-01的任期，会拆分为2021-01、2021-02两个独立的月度区间。
   • 每次递归生成下一个月的起始/结束日期，直到覆盖整个任期周期。

3. 最终统计

   • 按员工、角色、月份分组，通过DATEDIFF(month_end, month_start)计算当月任职天数：
     • 场景1：角色A任期2021-01-01至2021-01-15，计算得14天。
     • 场景2：角色A任期2021-01-01至2021-03-01，拆分后1月得31天、2月得28天。
     • 场景3：角色A的两个任期分别计算14天和6天，总和20天。

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