基于SQL计算多维度员工流失率（含滚动累计与移动平均）

假设表结构 #

为适配需求，先明确各表核心字段（若实际字段名不同，自行替换即可）：

• Headcount：CreatedDate（年月日期，如'2023-07-01'）、Location、BusinessHead、ReportingManager、HeadcountNumber（当月员工人数）
• Leavers：CreatedDate（年月日期）、Location、BusinessHead、ReportingManager、LeaversCount（当月离职人数）
• Year-Month：Year（自然年）、Month（自然月，1-12）、Number（财年月份，1对应7月，2对应8月…12对应次年6月）、CreatedDate（年月日期）

单条SQL查询语句 #

WITH combined_data AS (
    SELECT
        ym.CreatedDate,
        ym.Number AS FY_Month,
        -- 计算财年：7月及以后为当年财年，之前为上一年财年
        CASE WHEN ym.Month >=7 THEN ym.Year ELSE ym.Year -1 END AS FiscalYear,
        COALESCE(h.Location, l.Location) AS Location,
        COALESCE(h.BusinessHead, l.BusinessHead) AS "Business Head",
        COALESCE(h.ReportingManager, l.ReportingManager) AS "Reporting Manager",
        COALESCE(l.LeaversCount, 0) AS "Leavers Count",
        COALESCE(h.HeadcountNumber, 0) AS Monthly_Headcount
    FROM "Year-Month" ym
    LEFT JOIN Headcount h ON ym.CreatedDate = h.CreatedDate
        AND ym.Location = h.Location
        AND ym.BusinessHead = h.BusinessHead
        AND ym.ReportingManager = h.ReportingManager
    LEFT JOIN Leavers l ON ym.CreatedDate = l.CreatedDate
        AND ym.Location = l.Location
        AND ym.BusinessHead = l.BusinessHead
        AND ym.ReportingManager = l.ReportingManager
)
SELECT
    CreatedDate,
    Location,
    "Business Head",
    "Reporting Manager",
    "Leavers Count",
    -- 滚动平均员工人数（财年起始到当前月的平均值）
    ROUND(AVG(Monthly_Headcount) OVER (
        PARTITION BY FiscalYear, Location, "Business Head", "Reporting Manager"
        ORDER BY FY_Month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ), 2) AS "Avg. Headcount",
    -- 流失率计算：(累计离职数/平均人数)*12/财年已过月份数
    ROUND(
        (SUM("Leavers Count") OVER (
            PARTITION BY FiscalYear, Location, "Business Head", "Reporting Manager"
            ORDER BY FY_Month
        ) / NULLIF(AVG(Monthly_Headcount) OVER (
            PARTITION BY FiscalYear, Location, "Business Head", "Reporting Manager"
            ORDER BY FY_Month
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ), 0)) * 12 / FY_Month,
        4
    ) AS Attrition
FROM combined_data
ORDER BY FiscalYear, FY_Month, Location, "Business Head", "Reporting Manager";

关键逻辑说明 #

1. 财年分区：通过CASE语句将自然年转换为财年（7月至次年6月为一个财年），确保滚动计算仅在当前财年内生效。
2. 数据合并：用LEFT JOIN关联三张表，通过COALESCE处理维度匹配时的空值，保证每个年月-维度组合都有完整数据。
3. 滚动累计离职数：借助SUM(...) OVER (PARTITION BY ... ORDER BY FY_Month)实现从财年起始到当前月的离职人数累计。
4. 移动平均员工人数：通过AVG(...) OVER (PARTITION BY ... ORDER BY FY_Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)计算财年起始到当前月的员工人数平均值。
5. 流失率计算：严格遵循给定公式，用NULLIF避免除数为0的错误，ROUND控制结果精度。

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