PostgreSQL：按部门统计完成/未完成占比的交叉表查询问题

问题描述 #

现有completions表结构及数据如下：

需要编写PostgreSQL的crosstab查询，按部门统计各状态（Completed、Not Completed）的部门内占比，期望输出：

原查询的百分比计算逻辑错误，无法得到正确结果。

错误原因分析 #

原查询中使用sum(count(*)) over()计算的是全局总记录数（5条），而非每个部门的总人数，导致百分比是相对于全局的占比，而非部门内的占比。此外，原查询未处理不存在的状态-部门组合（如Tech部门的Not Completed），会导致crosstab返回NULL，无法显示0%。

正确解决方案 #

完整查询语句 #

SELECT * FROM crosstab(
    $$
    WITH all_combinations AS (
        -- 生成所有状态与部门的笛卡尔积，确保每个状态在每个部门都有对应行
        SELECT DISTINCT status FROM completions
        CROSS JOIN SELECT DISTINCT department FROM completions
    ),
    dept_totals AS (
        -- 计算每个部门的总人数
        SELECT department, COUNT(*) AS total
        FROM completions
        GROUP BY department
    ),
    status_dept_counts AS (
        -- 统计每个状态-部门组合的人数，无记录则为0
        SELECT 
            ac.status,
            ac.department,
            COUNT(c.name) AS status_count
        FROM all_combinations ac
        LEFT JOIN completions c 
            ON ac.status = c.status AND ac.department = c.department
        GROUP BY ac.status, ac.department
    )
    -- 计算部门内占比并格式化百分比字符串
    SELECT 
        sdc.status,
        sdc.department,
        ROUND((sdc.status_count * 100.0 / dt.total)::numeric, 0) || '%' AS percentage
    FROM status_dept_counts sdc
    JOIN dept_totals dt ON sdc.department = dt.department
    ORDER BY sdc.status, sdc.department
    $$,
    -- 指定交叉表的列（部门）顺序
    $$SELECT DISTINCT department FROM completions ORDER BY department$$
) AS ct(
    "Status" varchar,
    "HR" varchar,
    "Sales" varchar,
    "Tech" varchar
);

逻辑拆解 #

1. all_combinations：生成所有状态与部门的组合，确保即使某状态在某部门无记录，也能生成对应行，避免crosstab返回NULL。
2. dept_totals：单独计算每个部门的总人数，作为占比计算的分母（部门内总数）。
3. status_dept_counts：通过LEFT JOIN统计每个状态-部门组合的人数，无匹配记录时人数为0。
4. 占比计算：用状态人数除以部门总人数，乘以100后取整并拼接'%'符号，得到格式化的百分比字符串。
5. crosstab转换：将行式的状态-部门-占比数据转换为指定的交叉表格式。

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