PostgreSQL：如何统计每年的在职员工总数？

需求说明 #

需统计每年的在职员工总数，规则为：某一年的在职员工指入职时间不晚于当年年底，且满足以下任一条件：

• 仍在职（end_date为NULL）
• 离职日期落在该年范围内

完整解决方案（含连续年份） #

如果需要覆盖从最早入职年份到当前年份的所有连续年份（避免遗漏无新入职但有在职员工的年份），可以用以下SQL：

WITH years AS (
    SELECT generate_series(
        (SELECT EXTRACT(YEAR FROM MIN(start_date))::INT FROM employees),
        (SELECT EXTRACT(YEAR FROM CURRENT_DATE)::INT)
    ) AS year
)
SELECT
    y.year AS "年份",
    COUNT(e.id) AS "在职员工总数"
FROM years y
LEFT JOIN employees e
    ON e.start_date <= (y.year || '-12-31')::DATE
    AND (e.end_date IS NULL OR (e.end_date >= (y.year || '-01-01')::DATE AND e.end_date <= (y.year || '-12-31')::DATE))
GROUP BY y.year
ORDER BY y.year ASC;

代码说明 #

• years公共表表达式（CTE）生成连续年份序列，范围从员工最早入职年份到当前年份。
• LEFT JOIN保证每个年份都出现在结果中，哪怕当年无在职员工（此时总数为0）。
• 连接条件精准判断员工是否在对应年份在职：
  1. 入职时间不晚于当年12月31日（确保员工在当年已入职）
  2. 要么仍在职，要么离职日期在当年1月1日至12月31日之间

简化方案（仅统计有入职/离职记录的年份） #

如果只需要统计存在员工入职或离职的年份，可以用以下写法：

SELECT
    y.year AS "年份",
    COUNT(e.id) AS "在职员工总数"
FROM (
    SELECT DISTINCT EXTRACT(YEAR FROM start_date)::INT AS year
    FROM employees
    UNION
    SELECT DISTINCT EXTRACT(YEAR FROM end_date)::INT AS year
    FROM employees
    WHERE end_date IS NOT NULL
) y
LEFT JOIN employees e
    ON e.start_date <= (y.year || '-12-31')::DATE
    AND (e.end_date IS NULL OR (e.end_date >= (y.year || '-01-01')::DATE AND e.end_date <= (y.year || '-12-31')::DATE))
GROUP BY y.year
ORDER BY y.year ASC;

单一年份查询示例（以2022年为例） #

如果只需要查询某一年的在职员工数，比如2022年：

SELECT COUNT(*) AS "2022年在职员工总数"
FROM employees
WHERE start_date <= '2022-12-31'
  AND (end_date IS NULL OR (end_date >= '2022-01-01' AND end_date <= '2022-12-31'));

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