PostgreSQL如何实现多列分别分组的单查询统计？

实现方案 #

你要的按不同列单独分组的需求，在PostgreSQL中可以直接用GROUPING SETS语法实现，这是专门用于指定多组独立分组规则的语法，执行效率比拼接多个子查询更高，具体写法如下：

SELECT
  name,
  surname,
  year,
  count(*) AS count
FROM Humans
GROUP BY GROUPING SETS (
  (name),    -- 仅按name分组
  (surname), -- 仅按surname分组
  (year)     -- 仅按year分组
)
-- 排序规则匹配你期望的展示顺序，NULLS LAST保证空值排在对应列的末尾
ORDER BY
  name NULLS LAST,
  surname NULLS LAST,
  year NULLS LAST;

如果你的PostgreSQL版本过低不支持GROUPING SETS，也可以用UNION ALL拼接三个独立分组查询的结果实现同等效果：

-- 按name分组的结果
SELECT name, NULL AS surname, NULL AS year, count(*) AS count FROM Humans GROUP BY name
UNION ALL
-- 按surname分组的结果
SELECT NULL AS name, surname, NULL AS year, count(*) AS count FROM Humans GROUP BY surname
UNION ALL
-- 按year分组的结果
SELECT NULL AS name, NULL AS surname, year, count(*) AS count FROM Humans GROUP BY year
ORDER BY
  name NULLS LAST,
  surname NULLS LAST,
  year NULLS LAST;

以上两种写法执行后的输出结果和你给出的期望效果完全一致。

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