34 GROUP BY

GROUP BY 会按一个或多个表达式的值把行拆分成组，然后对每组应用聚合函数。不使用 GROUP BY 时，聚合会把整个结果压缩为一行。使用它后，每个不同的组得到一行——每位客户的 count、每月的 sum、每个类别的 avg。SELECT 列表中的每个非聚合列都必须出现在 GROUP BY 中（或者函数依赖于分组表达式）。

本章要点

• 按一个或多个列分组
• 对每个分组应用聚合
• 非聚合列必须出现在 GROUP BY 中的规则
• 按表达式而不只是列分组
• 使用 GROUPING SETS、ROLLUP、CUBE 生成多层汇总

示例

-- One row per customer with order count and total
SELECT customer_id,
       count(*)             AS order_count,
       sum(total)           AS total_spent,
       avg(total)::numeric(10,2) AS avg_order
FROM orders_example
GROUP BY customer_id
ORDER BY customer_id;

-- Group by an expression (first letter of fruit name)
SELECT substring(name, 1, 1) AS letter, count(*) AS cnt
FROM fruits
GROUP BY substring(name, 1, 1)
ORDER BY letter;

-- Group by multiple columns
SELECT schemaname, count(*) AS table_count
FROM pg_tables
GROUP BY schemaname
ORDER BY table_count DESC
LIMIT 5;

-- GROUPING SETS: several groupings in one query
SELECT customer_id, count(*) AS orders, sum(total) AS spent
FROM orders_example
GROUP BY GROUPING SETS ((customer_id), ())   -- per-customer rows AND a grand total
ORDER BY customer_id NULLS LAST;

-- ROLLUP: hierarchical subtotals
SELECT customer_id, sum(total) AS spent
FROM orders_example
GROUP BY ROLLUP (customer_id);

规则很直接：在带有 GROUP BY 的查询中，SELECT 列表中的每一列必须出现在 GROUP BY 子句中，或者位于某个聚合函数内。PostgreSQL 确实支持函数依赖——如果 id 是主键，就可以 GROUP BY id，并直接引用 name，因为 name 由 id 决定。但是你不能写类似 SELECT customer_id, total FROM orders GROUP BY customer_id; 的查询——total 在组内会变化。

GROUPING SETS 及其简写 ROLLUP 和 CUBE 可以在一次查询中产生多个分组，这是 OLAP 风格报表的基础。ROLLUP (a, b, c) 会产生分组 (a, b, c)、(a, b)、(a)、()——连续的小计一直到总计。CUBE (a, b) 会产生所有组合——(a, b)、(a)、(b)、()。

运行示例

运行方式（需要连接课程中的 orders_example 表）：

$ psql -f source/group-by.sql postgres
 customer_id | order_count | total_spent | avg_order
-------------+-------------+-------------+-----------
           1 |           2 |       80.00 |     40.00
          99 |           1 |       10.00 |     10.00
(2 rows)
 ...

常见陷阱

• “column ‘x’ must appear in the GROUP BY clause or be used in an aggregate function”——最常见的 GROUP BY 错误。要么按 x 分组，要么用 min(x)、max(x) 等包住它。
• WHERE 在分组之前过滤行；HAVING 在分组之后过滤组（下一课介绍）。把两者混淆是常见问题。
• 对可为空的列使用 GROUP BY 时，所有 NULL 都会作为一个分组。如果想排除它们，加上 WHERE col IS NOT NULL。
• 按表达式分组（GROUP BY date_trunc('month', created_at)）没有问题，但要注意 PostgreSQL 必须为每一行计算它——如果查询很频繁，就为该表达式建立索引。

下载

SQL 文件：group-by.sql · 全部示例文件 · 准备工作