PostgreSQL中如何合并COUNT与GROUP BY结果实现行转列统计

你需要的是把行级的类型统计转成同一日期行内的多列统计，这在PostgreSQL里用条件聚合就能轻松实现，有两种常用写法：

方法1：使用CASE语句（兼容多数SQL数据库） #

这是通用的SQL写法，适配所有支持CASE语法的数据库：

SELECT
  created_at::date AS created_at,
  COUNT(CASE WHEN type = 'full' THEN 1 END) AS full_count,
  COUNT(CASE WHEN type = 'virtual' THEN 1 END) AS virtual_count
FROM txns
GROUP BY created_at::date
ORDER BY created_at::date;

原理说明： #

• CASE WHEN type = 'full' THEN 1 END：当type匹配'full'时返回1，否则返回NULL
• COUNT()函数会自动忽略NULL值，最终统计的就是对应type的有效记录数
• 分组仅保留created_at::date，确保每个日期只会生成一行结果

方法2：使用PostgreSQL专属的FILTER子句（更简洁） #

从PostgreSQL 9.4版本开始支持FILTER子句，写法更直观易懂：

SELECT
  created_at::date AS created_at,
  COUNT(*) FILTER (WHERE type = 'full') AS full_count,
  COUNT(*) FILTER (WHERE type = 'virtual') AS virtual_count
FROM txns
GROUP BY created_at::date
ORDER BY created_at::date;

原理说明： #

• FILTER (WHERE type = 'full')直接指定统计的过滤条件，仅对符合条件的行计数
• 这种写法比CASE语法更清晰，是PostgreSQL官方推荐的条件聚合方式

预期输出 #

执行上述任一查询后，你会得到完全符合需求的结果格式：

created_at | full_count | virtual_count
------------+------------+---------------
 2017-05-20 |          2 |             2
 2017-05-21 |          1 |             1
 2017-05-22 |          3 |             0

注：当某个日期没有对应type的记录时，计数会自动显示为0，因为COUNT在无符合条件行时默认返回0。

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