PostgreSQL统计指定日期及月份在职员工人数的实现方法

你的原有SQL逻辑已经实现了每月人员净变动的计算，只需要修正分组逻辑的小问题，再添加窗口累加函数即可得到每月累计在职人数，完整实现如下：

最终可运行SQL #

WITH monthly_movement AS (
    -- 统计每月入职人数
    SELECT
        date_trunc('month', date_hires) AS month,
        COUNT(*) AS net_change
    FROM ponto_mais_relatorio_colaboradores
    WHERE date_hires IS NOT NULL -- 过滤入职日期为空的异常数据
    GROUP BY date_trunc('month', date_hires)

    UNION ALL

    -- 统计每月离职人数，计为负数
    SELECT
        date_trunc('month', date_layoff) AS month,
        COUNT(*) * -1 AS net_change
    FROM ponto_mais_relatorio_colaboradores
    WHERE date_layoff IS NOT NULL -- 过滤仍在职的员工，不计入离职统计
    GROUP BY date_trunc('month', date_layoff)
),
full_monthly_data AS (
    -- 关联日历辅助表补全所有月份，避免无入职离职的月份断档
    SELECT
        c.ano_mes AS reference_month,
        COALESCE(SUM(m.net_change), 0) AS monthly_net
    FROM calendar_aux c
    LEFT JOIN monthly_movement m ON m.month = c.ano_mes
    -- 可按需添加时间范围过滤，例如仅统计2020-2023年数据
    -- WHERE c.ano_mes >= '2020-01-01'::date AND c.ano_mes < '2024-01-01'::date
    GROUP BY c.ano_mes
)
-- 累加每月净变动得到累计在职人数
SELECT
    reference_month,
    SUM(monthly_net) OVER (ORDER BY reference_month ASC) AS total_active_employees
FROM full_monthly_data
ORDER BY reference_month;

关键逻辑说明 #

• 修正了原SQL的分组错误：原SQL按原始日期字段date_hires/date_layoff分组，会把同一月的数据拆分为多条，改为按date_trunc处理后的月份分组保证统计正确
• 新增了空值过滤逻辑：避免将离职日期为空的在职员工计入离职统计
• 通过左关联日历辅助表，保证没有入职、离职记录的月份也能正常显示对应在职人数，不会出现月份断档
• 用SUM() OVER (ORDER BY reference_month)窗口函数实现逐月累加净变动值，直接得到当月累计在职人数

额外说明 #

• 如果需要查询单个指定月份的在职人数，直接在最终查询语句中添加过滤条件即可，例如WHERE reference_month = '2021-01-01'::date即可获取2021年1月的在职员工总数
• 如果你的calendar_aux表的ano_mes字段为字符串格式（如2021-01），需要将date_trunc返回的日期类型转为对应格式的字符串再关联，例如用to_char(date_trunc('month', date_hires), 'YYYY-MM')生成关联键
• 首次计算后可通过如下SQL校验结果准确性：

-- 校验2021年1月在职人数是否正确
SELECT COUNT(*) AS verify_2021_jan_active
FROM ponto_mais_relatorio_colaboradores
WHERE 
    date_hires <= '2021-01-31'::date
    AND (date_layoff > '2021-01-31'::date OR date_layoff IS NULL);

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