如何用Google BigQuery SQL计算部门月度活跃员工数及流失率？

核心思路 #

无需使用循环（LOOP/WHILE），利用BigQuery的日期序列生成函数GENERATE_DATE_ARRAY生成每个部门的统计月份范围，结合员工离职日期判断活跃状态，最后聚合得到目标统计结果。

完整SQL实现 #

WITH department_base AS (
    -- 加载部门初始信息表
    SELECT Department, StartingMonth, NumberOfEmployees AS initial_headcount
    FROM `your-project.your-dataset.department_initial_table` -- 替换为你的部门初始表路径
),
month_series AS (
    -- 生成每个部门从成立月到目标月的统计月份序列
    SELECT 
        db.Department,
        db.StartingMonth,
        stat_month
    FROM department_base db,
    UNNEST(GENERATE_DATE_ARRAY(
        db.StartingMonth,
        DATE_TRUNC(CURRENT_DATE(), MONTH), -- 统计至当前月首日，可替换为固定日期如DATE('2023-07-01')
        INTERVAL 1 MONTH
    )) AS stat_month
),
employee_active AS (
    -- 整理员工表关键字段
    SELECT 
        e.Department,
        e.EmployeeID,
        DATE_TRUNC(e.HireDate, MONTH) AS hire_month, -- 假设员工入职月与部门成立月一致
        e.EmployeeCloseDate
    FROM `your-project.your-dataset.Employee` -- 替换为你的员工表路径
)
-- 统计各部门月度活跃人数、离职人数及流失率
SELECT 
    ms.Department,
    ms.StartingMonth,
    ms.stat_month AS `NEW LOOPED MONTH`,
    COUNT(DISTINCT CASE 
        WHEN ea.EmployeeCloseDate IS NULL OR ea.EmployeeCloseDate >= ms.stat_month 
        THEN ea.EmployeeID 
        ELSE NULL 
    END) AS NumberOfEmployees,
    -- 月度离职人数 = 初始人数 - 当前活跃人数
    db.initial_headcount - COUNT(DISTINCT CASE 
        WHEN ea.EmployeeCloseDate IS NULL OR ea.EmployeeCloseDate >= ms.stat_month 
        THEN ea.EmployeeID 
        ELSE NULL 
    END) AS monthly_separations,
    -- 流失百分比（保留2位小数）
    ROUND(
        (db.initial_headcount - COUNT(DISTINCT CASE 
            WHEN ea.EmployeeCloseDate IS NULL OR ea.EmployeeCloseDate >= ms.stat_month 
            THEN ea.EmployeeID 
            ELSE NULL 
        END)) / db.initial_headcount * 100,
        2
    ) AS turnover_percentage
FROM month_series ms
JOIN department_base db 
    ON ms.Department = db.Department AND ms.StartingMonth = db.StartingMonth
LEFT JOIN employee_active ea 
    ON ms.Department = ea.Department 
    AND ea.hire_month = ms.StartingMonth -- 仅统计部门初始员工，排除后续新增
GROUP BY ms.Department, ms.StartingMonth, ms.stat_month, db.initial_headcount
ORDER BY ms.Department, ms.stat_month;

关键逻辑说明 #

1. 生成统计月份序列：通过GENERATE_DATE_ARRAY生成连续月份数组并UNNEST展开，替代循环逻辑，执行效率更高。
2. 活跃员工判定规则：员工满足以下任一条件则视为当前统计月活跃：
   • EmployeeCloseDate IS NULL：未离职
   • EmployeeCloseDate >= stat_month：离职日期晚于统计月首日（当月仍在职）
3. 数据精准关联：通过hire_month = StartingMonth过滤出部门成立时的初始员工，避免后续新入职员工干扰统计结果。
4. 流失率计算：基于部门初始员工数，直接推导月度离职人数及流失百分比，匹配业务需求。

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