基于Staff_table与Leavers_Table的滚动12个月员工流失率T-SQL问题

问题背景 #

现有两张业务表：

• Staff_table：存储在职员工月度快照数据
• Leavers_Table：存储员工离职记录

需按**组织(Organisation)、职位(Jobrole)、职位代码(jobCode)、职级(Grade)**维度，计算滚动12个月员工流失率，公式为：

流失率 = 滚动12个月内离职人数 / [(期初（12个月前）员工数 + 期末（当前）员工数)/2]

原CTE脚本计算结果不符合预期，核心问题是分母的职级拆分员工数与对应月份同职级离职数维度不匹配，以下是修正方案：

原脚本核心问题点 #

1. 关联条件存在语法错误（如ON子句中遗漏逻辑运算符AND）
2. 离职人数统计时，窗口函数的日期字段与关联字段不统一（混用CensusDate和date）
3. 维度关联不完整，遗漏Cust_ID等关键分组字段
4. 离职表未先按维度聚合月度离职人数，直接使用窗口函数导致统计逻辑错误

修正后的T-SQL脚本 #

WITH Staff AS (
    -- 按维度统计每月在职员工数，确保date为月度维度日期（如每月最后一天）
    SELECT 
        date,
        Cust_ID,
        Organisation,
        Jobrole,
        jobCode,
        Grade,
        CAST(COUNT(*) AS FLOAT) AS NoOfStaff
    FROM staff_table
    GROUP BY date, Cust_ID, Organisation, Jobrole, jobCode, Grade
),
Staffing AS (
    -- 关联当前月与12个月前的在职员工数，获取期初（12个月前）和期末（当前）人数
    SELECT 
        a.date,
        a.Cust_ID,
        a.Organisation,
        a.Jobrole,
        a.jobCode,
        a.Grade,
        a.NoOfStaff AS CurrentMonth,
        ISNULL(b.NoOfStaff, 0) AS PreviousMonth -- 处理12个月前无数据的情况
    FROM Staff a
    LEFT JOIN Staff b 
        ON a.Cust_ID = b.Cust_ID
        AND a.Organisation = b.Organisation
        AND a.Jobrole = b.Jobrole
        AND a.jobCode = b.jobCode
        AND a.Grade = b.Grade
        AND a.date = DATEADD(month, 11, b.date) -- 当前月 = 12个月前 + 11个月（即滚动12期的期初）
),
Denominator AS (
    -- 计算流失率分母：(期初人数 + 期末人数)/2
    SELECT 
        date,
        Cust_ID,
        Organisation,
        Jobrole,
        jobCode,
        Grade,
        (CurrentMonth + PreviousMonth) / 2 AS Metric_Denominator
    FROM Staffing
),
MonthlyLeavers AS (
    -- 先按维度统计每月离职人数，确保date为离职所属月份
    SELECT 
        DATEFROMPARTS(YEAR(CensusDate), MONTH(CensusDate), 1) AS date, -- 统一为每月第一天作为维度日期
        Cust_ID,
        Organisation,
        Jobrole,
        jobCode,
        Grade,
        CAST(COUNT(*) AS FLOAT) AS MonthlyLeaverCount
    FROM Leavers_Table
    GROUP BY DATEFROMPARTS(YEAR(CensusDate), MONTH(CensusDate), 1), Cust_ID, Organisation, Jobrole, jobCode, Grade
),
Numerator AS (
    -- 计算滚动12个月离职总数，按维度分区、日期排序
    SELECT 
        date,
        Cust_ID,
        Organisation,
        Jobrole,
        jobCode,
        Grade,
        SUM(MonthlyLeaverCount) OVER (
            PARTITION BY Cust_ID, Organisation, Jobrole, jobCode, Grade
            ORDER BY date
            ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
        ) AS Metric_Numerator
    FROM MonthlyLeavers
)
-- 关联分母与分子，计算最终流失率，处理离职数为NULL的情况
SELECT 
    d.date,
    d.Cust_ID,
    d.Organisation,
    d.Jobrole,
    d.jobCode,
    d.Grade,
    d.Metric_Denominator,
    ISNULL(n.Metric_Numerator, 0) AS Metric_Numerator,
    -- 计算流失率，避免除以0的情况
    CASE WHEN d.Metric_Denominator = 0 THEN 0 ELSE ISNULL(n.Metric_Numerator, 0) / d.Metric_Denominator END AS AttritionRate
FROM Denominator d
LEFT JOIN Numerator n
    ON d.date = n.date
    AND d.Cust_ID = n.Cust_ID
    AND d.Organisation = n.Organisation
    AND d.Jobrole = n.Jobrole
    AND d.jobCode = n.jobCode
    AND d.Grade = n.Grade
WHERE d.date >= '2018-01-01'
ORDER BY d.date, d.Cust_ID, d.Organisation, d.Jobrole, d.Grade;

关键修正说明 #

• 统一日期维度：将离职表的CensusDate转换为与在职表一致的月度日期，确保维度匹配
• 完整维度关联：所有关联和分区都包含Cust_ID、Organisation、Jobrole、jobCode、Grade全维度，避免数据错位
• 处理边界情况：用ISNULL处理12个月前无在职数据、无离职数据的场景，避免NULL导致计算错误
• 增加除以0保护：通过CASE语句避免分母为0时的报错

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