如何按年度分性别统计含离职扣减的在职员工滚动累计数

现有SQL的核心问题梳理 #

• 第一版基础查询：仅按dept_emp.from_date的年份分组计数，本质统计的是各年度分性别的新入职人数，完全没有利用to_date字段核算当年离职人员，输出结果是纯新增数，不是年度实际在岗存量
• 第二版用户变量查询：存在两个逻辑硬伤，一是没有把离职人员纳入扣减计算，累计值不是真实在岗数；二是没有按性别维度对累计值做分组重置，导致男女员工的计数串算，无法独立输出各性别的累计结果

实现核心逻辑 #

要得到真实的滚动累计在岗数，需要把统计链路拆成三步：

1. 按「统计年度+性别」维度拆分两类人员变动数据：
   • 当年新入职人数：匹配dept_emp.from_date落在对应年度的分性别人员数
   • 当年离职人数：匹配dept_emp.to_date落在对应年度的分性别人员数，注意在职人员的to_date通常为数据库预设的超大固定值（示例库中为9999-01-01），这类记录不计入离职统计
2. 计算每年度的人员净变动值：当年新入职人数 - 当年离职人数
3. 按性别独立分组，对同组内的年度净变动值做从公司成立起的滚动累加，得到的就是对应年度该性别下的实际在岗总人数

可落地SQL实现 #

适配MySQL 8.0+版本（优先推荐，计算稳定无逻辑漏洞） #

使用窗口函数实现分组内的滚动累计，完全规避自定义用户变量的各类串值问题：

-- 生成覆盖公司全运营周期的年度序列，避免无入职/离职记录的年份出现漏算
WITH year_range AS (
    SELECT YEAR(MIN(from_date)) AS stat_year FROM dept_emp
    UNION ALL
    SELECT stat_year + 1 FROM year_range WHERE stat_year < YEAR(CURDATE())
),
-- 按年度+性别汇总当年的新增、离职人数
year_gender_change AS (
    -- 年度新入职分性别统计
    SELECT
        YEAR(de.from_date) AS stat_year,
        e.gender,
        COUNT(DISTINCT de.emp_no) AS new_hired_cnt,
        0 AS resigned_cnt
    FROM dept_emp de
    JOIN employees e USING(emp_no)
    GROUP BY stat_year, e.gender

    UNION ALL

    -- 年度离职分性别统计
    SELECT
        YEAR(de.to_date) AS stat_year,
        e.gender,
        0 AS new_hired_cnt,
        COUNT(DISTINCT de.emp_no) AS resigned_cnt
    FROM dept_emp de
    JOIN employees e USING(emp_no)
    WHERE de.to_date < '9999-01-01' -- 按实际业务调整在职人员的to_date判定规则
    GROUP BY stat_year, e.gender
)
-- 按性别分组滚动计算累计在岗数
SELECT
    gender,
    stat_year,
    SUM(new_hired_cnt) AS annual_new_hired,
    SUM(resigned_cnt) AS annual_resigned,
    SUM(SUM(new_hired_cnt - resigned_cnt)) OVER (
        PARTITION BY gender
        ORDER BY stat_year
        ROWS UNBOUNDED PRECEDING
    ) AS current_onboarded_total
FROM year_gender_change
GROUP BY gender, stat_year
ORDER BY stat_year, gender;

适配低版本MySQL（无窗口函数）实现 #

通过增加性别判定逻辑，解决原有用户变量跨性别串算的问题：

SET @prev_gender := '';
SET @running_total := 0;

SELECT
    gender,
    stat_year,
    new_hired_cnt,
    resigned_cnt,
    -- 切换性别时重置累计值，从0开始重新计算该性别的滚动累计
    CASE
        WHEN @prev_gender = gender THEN @running_total := @running_total + (new_hired_cnt - resigned_cnt)
        WHEN @prev_gender := gender THEN @running_total := new_hired_cnt - resigned_cnt
    END AS current_onboarded_total
FROM (
    -- 基础统计逻辑和窗口函数版本完全一致
    SELECT
        YEAR(de.from_date) AS stat_year,
        e.gender,
        COUNT(DISTINCT de.emp_no) AS new_hired_cnt,
        0 AS resigned_cnt
    FROM dept_emp de
    JOIN employees e USING(emp_no)
    GROUP BY stat_year, e.gender

    UNION ALL

    SELECT
        YEAR(de.to_date) AS stat_year,
        e.gender,
        0 AS new_hired_cnt,
        COUNT(DISTINCT de.emp_no) AS resigned_cnt
    FROM dept_emp de
    JOIN employees e USING(emp_no)
    WHERE de.to_date < '9999-01-01'
    GROUP BY stat_year, e.gender
) AS base_stat
ORDER BY gender, stat_year;

结果校验要点 #

• 输出的在岗数为扣除当年离职人员后的实际存量，不是单纯新入职人数的累加值
• 分性别维度独立计算累计值，不存在男女计数串算的问题
• 覆盖全运营周期的人员变动场景，年中入职/离职的人员均会被对应年度的统计值准确计入

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