PostgreSQL多表INNER JOIN后按职位统计各面试类型数量的实现问题

调整方案 #

你需要做行转列统计，PostgreSQL 中可以直接使用聚合函数的 FILTER 子句实现需求，调整后的查询语句如下：

SELECT 
    job_description,
    hired,
    open_positions - hired AS remaining,
    COUNT(*) FILTER (WHERE i1.interview_type = 'L1') AS L1,
    COUNT(*) FILTER (WHERE i1.interview_type = 'L2') AS L2,
    COUNT(*) FILTER (WHERE i1.interview_type = 'HR') AS HR
FROM job
INNER JOIN job_application j1 ON job.id = j1.job_id
INNER JOIN interview i1 ON j1.id = i1.jobapplication_id
GROUP BY 
    job_description,
    hired,
    open_positions;

调整说明 #

1. 移除了原GROUP BY子句中的i1.interview_type字段，现在按岗位维度分组，同一岗位的所有面试记录会聚合到同一行
2. 每个面试类型单独使用带过滤条件的COUNT统计，不存在该类型面试的岗位对应列会自动返回0
3. 保留了你原有的剩余岗位数计算逻辑

如果你的面试类型是动态变化的，没有固定枚举值，可以使用PostgreSQL的crosstab行转列函数实现，需要先启用tablefunc扩展：

-- 先启用扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 动态行转列查询
SELECT * FROM crosstab(
    'SELECT 
        job_description,
        interview_type,
        COUNT(*) AS cnt
    FROM job
    INNER JOIN job_application j1 ON job.id = j1.job_id
    INNER JOIN interview i1 ON j1.id = i1.jobapplication_id
    GROUP BY job_description, interview_type
    ORDER BY 1',
    'SELECT DISTINCT interview_type FROM interview ORDER BY 1'
) AS ct (
    job_description TEXT,
    hired INT,
    remaining INT,
    L1 INT,
    L2 INT,
    HR INT
    -- 此处列定义需要和实际返回的面试类型数量、顺序匹配
);

内容的提问来源于stack exchange，提问作者Ashok.N