PostgreSQL如何避免单查询多次全表扫描实现部门年人事统计

针对你的需求，以下两种方案可以实现仅单次全表扫描完成统计，避免原CTE方案的两次扫描开销：

方案1：用LATERAL拆分事件（逻辑直观） #

将每条员工部门记录拆分为「入职」和「离职」两个事件条目，一次扫描原表后直接分组统计：

SELECT
    event_year::INT AS year,
    d.department,
    COUNT(*) FILTER (WHERE event_type = 'hire') AS total_hiring,
    COUNT(*) FILTER (WHERE event_type = 'departure') AS total_departure
FROM employee_department ed
JOIN departments d 
    ON ed.department_id = d.department_id
CROSS JOIN LATERAL (
    VALUES
        (EXTRACT(YEAR FROM ed.effective_department_from_date), 'hire'),
        (EXTRACT(YEAR FROM ed.effective_department_end_date), 'departure')
) AS events(event_year, event_type)
WHERE event_year IS NOT NULL -- 过滤无有效日期的无效事件
GROUP BY event_year, d.department
ORDER BY year, d.department;

方案2：条件聚合（代码更简洁） #

利用PostgreSQL的条件聚合特性，在单次分组中同时计算两类统计值：

SELECT
    COALESCE(hire_year, departure_year) AS year,
    d.department,
    SUM(CASE WHEN hire_year IS NOT NULL THEN 1 ELSE 0 END) AS total_hiring,
    SUM(CASE WHEN departure_year IS NOT NULL THEN 1 ELSE 0 END) AS total_departure
FROM (
    SELECT
        EXTRACT(YEAR FROM ed.effective_department_from_date) AS hire_year,
        EXTRACT(YEAR FROM ed.effective_department_end_date) AS departure_year,
        d.department
    FROM employee_department ed
    JOIN departments d 
        ON ed.department_id = d.department_id
) AS base_data
GROUP BY COALESCE(hire_year, departure_year), d.department
ORDER BY year, d.department;

原方案的问题 #

原CTE写法中，两个独立的CTE会分别对employee_department和departments执行完整的扫描、关联与分组操作，相当于重复处理两次相同数据源。上述两种方案仅需一次扫描原表，所有统计逻辑在单次遍历中完成，大表场景下性能提升明显。

额外说明 #

如果需要确保结果包含所有部门（即使某年份无招聘/离职记录），可以结合generate_series生成年度序列并与部门表做笛卡尔积，再左关联统计结果，核心的单次扫描逻辑不受影响。

内容的提问来源于stack exchange，提问作者thuận bủi