PostgreSQL优化含多COUNT CASE列的员工年度统计查询

问题背景 #

需要统计各部门在指定年份的任职员工数量，现有表Table包含员工ID、部门名称department_name、入职日期date_start、离职日期date_end字段。当前通过重复的COUNT(CASE)语句实现按年份列展示结果，但年份增多时脚本会极度冗长，需简化查询逻辑。

当前使用的查询代码：

select department_name,
       count(
             case 
             when employee.date_start <= '2018-01-01' and employee.date_end >= '2018.12.31'
             then 1 end) as emp2018, 
       count(
             case 
             when employee.date_start <= '2019-01-01' and employee.date_end >= '2019.12.31'
             then 1 end) as emp2019, 
       count(
             case 
             when employee.date_start <= '2020-01-01' and employee.date_end >= '2020.12.31'
             then 1 end) as emp2020
from Table
group by department_name;

简化方案 #

1. 固定年份场景：集中定义年份，减少重复代码 #

通过CTE（公共表表达式）统一定义需要统计的年份，再通过关联和聚合生成结果，新增年份仅需修改CTE和SELECT中的对应行：

WITH years AS (
    SELECT 2018 AS year UNION ALL
    SELECT 2019 UNION ALL
    SELECT 2020 -- 新增年份仅需在此添加UNION ALL行
)
SELECT 
    t.department_name,
    SUM(CASE WHEN y.year = 2018 THEN 1 ELSE 0 END) AS `2018`,
    SUM(CASE WHEN y.year = 2019 THEN 1 ELSE 0 END) AS `2019`,
    SUM(CASE WHEN y.year = 2020 THEN 1 ELSE 0 END) AS `2020` -- 新增年份对应添加此行
FROM years y
CROSS JOIN (SELECT DISTINCT department_name FROM `Table`) d
LEFT JOIN `Table` t
    ON d.department_name = t.department_name
    AND t.date_start <= CONCAT(y.year, '-01-01')
    AND t.date_end >= CONCAT(y.year, '-12-31') -- 统一日期格式避免解析错误
GROUP BY t.department_name;

2. 支持PIVOT的数据库（如SQL Server、Oracle）：用PIVOT转置结果 #

利用数据库原生的PIVOT功能，先按部门和年份统计数量，再转置为列格式：

WITH years AS (
    SELECT 2018 AS year UNION ALL
    SELECT 2019 UNION ALL
    SELECT 2020
),
emp_year_stats AS (
    SELECT 
        t.department_name,
        y.year,
        COUNT(t.employee_id) AS emp_count
    FROM years y
    LEFT JOIN `Table` t
        ON t.date_start <= CONCAT(y.year, '-01-01')
        AND t.date_end >= CONCAT(y.year, '-12-31')
    GROUP BY t.department_name, y.year
)
SELECT *
FROM emp_year_stats
PIVOT (
    SUM(emp_count)
    FOR year IN ([2018], [2019], [2020]) -- 新增年份在此添加对应项
) AS pivot_result;

3. 动态年份场景：用动态SQL自动适配年份列表 #

如果年份需要频繁变动，可使用动态SQL，仅需修改年份列表变量即可生成对应查询：

以MySQL为例：

-- 仅需修改此处的年份列表
SET @target_years = '2018,2019,2020';
SET @sql = '';

-- 生成年份对应的统计列语句
SELECT GROUP_CONCAT(
    CONCAT('SUM(CASE WHEN y.year = ', year, ' THEN 1 ELSE 0 END) AS `', year, '`')
) INTO @sql
FROM (
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@target_years, ',', n), ',', -1) AS year
    FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) numbers
    WHERE n <= LENGTH(@target_years) - LENGTH(REPLACE(@target_years, ',', '')) + 1
) AS year_list;

-- 拼接完整查询语句
SET @sql = CONCAT(
    'WITH years AS (',
    GROUP_CONCAT(CONCAT('SELECT ', year, ' AS year') SEPARATOR ' UNION ALL '),
    ') SELECT t.department_name, ', @sql, 
    ' FROM years y CROSS JOIN (SELECT DISTINCT department_name FROM `Table`) d ',
    ' LEFT JOIN `Table` t ON d.department_name = t.department_name ',
    ' AND t.date_start <= CONCAT(y.year, ''-01-01'') ',
    ' AND t.date_end >= CONCAT(y.year, ''-12-31'') ',
    ' GROUP BY t.department_name;'
);

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意事项 #

• 统一日期格式：原查询中混用'2018-01-01'和'2018.12.31'，需改为一致的'YYYY-MM-DD'格式，避免数据库解析错误。
• 左连接确保无数据的部门也能显示：通过CROSS JOIN生成所有部门和年份的组合，再LEFT JOIN原表，保证即使部门在某年份无任职员工，也会显示0值。

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