PostgreSQL如何编写聚合查询将分组状态值转为表格表头

解决方案 #

PostgreSQL中实现你需要的行转列（Pivot）效果，有两种常用实现方式：

方式1：条件聚合（推荐，无需额外扩展）

直接在聚合函数中通过CASE WHEN判断状态，分别统计不同状态的数量，写法最通用兼容：

SELECT 
  p.name,
  SUM(CASE WHEN t.status = 'foo' THEN 1 ELSE 0 END) AS foo,
  SUM(CASE WHEN t.status = 'bar' THEN 1 ELSE 0 END) AS bar
FROM public.projects AS p 
JOIN public.tasks AS t ON p.id = t.project_id
GROUP BY p.name;

如果有更多状态值，只需要新增对应的SUM(CASE ...)列即可。

方式2：使用crosstab函数

如果状态值较多，或者需要动态读取状态值生成列，可以使用PostgreSQL内置扩展tablefunc提供的crosstab函数：

1. 首先启用扩展（仅需执行一次）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 执行行转列查询：

SELECT * FROM crosstab(
  -- 第一个参数：原始聚合查询，按项目名、状态排序
  'SELECT p.name, t.status, COUNT(t.id) AS size 
   FROM public.projects AS p 
   JOIN public.tasks AS t ON p.id = t.project_id
   GROUP BY p.name, t.status
   ORDER BY 1, 2',
  -- 第二个参数：获取所有状态值的查询，决定最终表头顺序
  'SELECT DISTINCT status FROM public.tasks ORDER BY 1'
) AS ct(
  -- 定义返回结果的列名和类型，和上面查询的输出一一对应
  name text, 
  foo int,
  bar int
);

说明 #

• 如果状态值是固定的少量枚举，优先选择条件聚合方案，语法更简单无需额外依赖，执行效率也更高。
• 如果状态值会动态新增，需要写动态SQL拼接查询语句，避免手动新增列的维护成本。

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