# GROUP BY

> 按组聚合行，并使用 ROLLUP 和 GROUPING SETS 构建小计。

---

LLMS 索引： [llms.txt](/llms.txt)

---

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

## 本章要点 {#learn}

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

## 示例 {#example}

```sql {num="34.1" caption="GROUP BY"}
-- 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)`、`()`。

## 运行示例 {#run}

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

```text
$ 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)
 ...
```

## 常见陷阱 {#pitfalls}

- *"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 必须为每一行计算它——如果查询很频繁，就为该表达式建立索引。

> [!TIP] 提示
> `GROUP BY 1, 2` 按 `SELECT` 列表中的第一列和第二列分组。临时查询这样写很简洁；在提交到代码库的代码中，应写出列名以提高清晰度。

> [!NOTE] 动手练习
> 按 name 的首字母对 `fruits` 分组：`SELECT substring(name, 1, 1) AS letter, count(*) FROM fruits GROUP BY substring(name, 1, 1) ORDER BY letter;`。然后试试 `GROUP BY 1`。再加入 `HAVING count(*) > 1`（下一课预览），只查看匹配次数超过一次的字母。

## 下载 {#downloads}

SQL 文件：[group-by.sql](/examples/source/group-by.sql) · [全部示例文件](/examples/examples.zip) · [准备工作](/#prerequisites)