第 5 章:聚合与分组
前面查的都是"某一行长什么样"。这一章开始查"整体是什么情况":一共卖了多少、每本书卖了几本、哪个城市贡献最大。
学习目标
- 会用
COUNT、SUM、AVG、MIN、MAX做整体统计 - 分清
COUNT(*)和COUNT(列),知道空值会漏算 - 会用
GROUP BY按维度切桶、用HAVING过滤分完组的结果 - 理解
WHERE和HAVING的分工:一个筛行,一个筛组
5.1 五个聚合函数
聚合函数把多行压成一行:
| 函数 | 作用 | 例子 |
|---|---|---|
COUNT | 数行数 / 非空值个数 | COUNT(*)、COUNT(city) |
SUM | 求和 | SUM(quantity) |
AVG | 平均值 | AVG(price) |
MIN / MAX | 最小值 / 最大值 | MIN(price)、MAX(ordered_at) |
先算全店的总销量和总营收:
SELECT
SUM(quantity) AS 总销量,
ROUND(SUM(quantity * unit_price), 2) AS 总营收
FROM order_items;┌────────┬────────┐
│ 总销量 │ 总营收 │
├────────┼────────┤
│ 26 │ 1695.0 │
└────────┴────────┘26 件、1695 元。注意 SUM(quantity * unit_price):聚合函数里可以先算表达式,先乘出每行的金额,再求和。这是算营收的标准写法,不需要在表里存一个"小计"字段。
但这个数字是错的——它包含了退款订单。要排除,得先按订单状态过滤,这就需要 JOIN(下一章)。先用一个近似:把退款那笔 98 元减掉,实际营收 1597 元。
COUNT 的两种用法
SELECT COUNT(*) AS 客户数, COUNT(city) AS 填了城市的客户数 FROM customers;┌────────┬──────────────────┐
│ 客户数 │ 填了城市的客户数 │
├────────┼──────────────────┤
│ 5 │ 4 │
└────────┴──────────────────┘COUNT(*) 数行,COUNT(city) 只数非 NULL。王五的城市是 NULL,所以少一个。
这个差别在做数据质量统计时非常关键:"有多少用户填了手机号""有多少订单有备注",都必须用 COUNT(列),用 COUNT(*) 会把空值也算成"有"。
AVG 也会被 NULL 影响
AVG(price) 的分母是非 NULL 的行数,不是总行数。如果某本书价格为空,它不会拉低平均值,而是直接被排除在分母外。这通常是想要的,但要知道它在发生。
5.2 GROUP BY:按维度切桶
整体统计只有一行,用处有限。真正有用的是"分组统计":每本书卖了多少。
SELECT
b.title,
SUM(i.quantity) AS 销量,
ROUND(SUM(i.quantity * i.unit_price), 2) AS 营收
FROM order_items i
JOIN books b ON b.id = i.book_id
GROUP BY b.id, b.title
ORDER BY 销量 DESC;┌──────────────────────┬──────┬────────┐
│ title │ 销量 │ 营收 │
├──────────────────────┼──────┼────────┤
│ SQL 必知必会 │ 7 │ 343.0 │
│ 活着 │ 4 │ 156.0 │
│ 深入理解计算机系统 │ 3 │ 384.0 │
│ 平凡的世界 │ 3 │ 294.0 │
│ 代码整洁之道 │ 3 │ 177.0 │
│ 无名诗集 │ 3 │ 75.0 │
│ 数据库系统概念 │ 2 │ 198.0 │
│ 人类简史 │ 1 │ 68.0 │
└──────────────────────┴──────┴────────┘GROUP BY b.id, b.title 的意思是:把 b.id 和 b.title 都相同的行归成一组,每组输出一行。同一本书的 4 条明细被压成一行,SUM 在这一组内求和。
看结果会发现两件事:
- 销量最高的不是营收最高的。《SQL 必知必会》卖 7 本只有 343 元,而《深入理解计算机系统》卖 3 本就 384 元。单价差异让两个指标排序不同——这就是为什么不能只看一个指标。
GROUP BY要带上b.id。只写b.title也能跑,但书名理论上可能重复(两本同名书),带上id才是严格按"这本书"分组。
分组的规则:SELECT 里只能出现分组列和聚合
-- 错误示范
SELECT b.title, b.author, SUM(i.quantity)
FROM order_items i JOIN books b ON b.id = i.book_id
GROUP BY b.id;b.author 没有出现在 GROUP BY 里,也不是聚合结果。数据库不知道该输出这一组里哪一行的作者。
SQLite 对这种情况很宽容,会随便挑一行的值返回;PostgreSQL 会直接报错。别依赖 SQLite 的宽容,按标准写:SELECT 里的非聚合列,必须全部出现在 GROUP BY 里。
多列分组
SELECT category, COUNT(*) AS 书目数
FROM books
GROUP BY category
ORDER BY 书目数 DESC;┌──────────┬────────┐
│ category │ 书目数 │
├──────────┼────────┤
│ 文学 │ 3 │
│ 计算机 │ 2 │
│ 数据库 │ 2 │
│ 历史 │ 1 │
└──────────┴────────┘想更细,加一列:GROUP BY category, author 会按"分类 + 作者"的组合分组,组合不同的就分成不同组。分组的粒度由你列的多少决定,越少越粗。
NULL 会自成一个组
SELECT city, COUNT(*) AS 客户数 FROM customers GROUP BY city;┌──────────┬────────┐
│ city │ 客户数 │
├──────────┼────────┤
│ NULL │ 1 │
│ 上海 │ 1 │
│ 北京 │ 1 │
│ 广州 │ 1 │
│ 石家庄 │ 1 │
└──────────┴────────┘王五的城市是 NULL,没有被丢掉,而是单独变成一组。GROUP BY 把"所有 NULL"视为同一类值——注意这和三值逻辑不矛盾:分组比的是"是否同类",不是"是否相等"。
这一组在报表里通常要单独处理:要么改名为"未填写",要么在 HAVING 里过滤掉。直接把它当城市名展示会很奇怪。
5.3 HAVING:对分完组的结果再筛
WHERE 筛的是行,发生在分组之前;HAVING 筛的是组,发生在分组之后。
需求:只保留销量不低于 3 本的书。
SELECT b.title, SUM(i.quantity) AS 销量
FROM order_items i
JOIN books b ON b.id = i.book_id
GROUP BY b.id, b.title
HAVING SUM(i.quantity) >= 3
ORDER BY 销量 DESC;┌──────────────────────┬──────┐
│ title │ 销量 │
├──────────────────────┼──────┤
│ SQL 必知必会 │ 7 │
│ 活着 │ 4 │
│ 深入理解计算机系统 │ 3 │
│ 平凡的世界 │ 3 │
│ 代码整洁之道 │ 3 │
│ 无名诗集 │ 3 │
└──────────────────────┴──────┘8 本书筛掉 2 本(数据库系统概念 2 本、人类简史 1 本)。
WHERE 和 HAVING 的分工
这是最容易混的一对,用一句话分清:
| 子句 | 作用对象 | 执行时机 | 能不能用聚合 |
|---|---|---|---|
WHERE | 每一行 | 分组之前 | 不能 |
HAVING | 每个组 | 分组之后 | 能 |
所以:
- "只算 2025 年的订单" →
WHERE ordered_at >= '2025-01-01'(先筛行) - "只保留消费超过 200 的客户" →
HAVING SUM(...) > 200(后筛组)
能放 WHERE 的条件尽量放 WHERE,因为越早过滤,后面要处理的数据越少。
HAVING 里用别名
SELECT b.title, SUM(i.quantity) AS 销量
FROM ... GROUP BY b.id, b.title
HAVING 销量 >= 3;SQLite 允许,但标准 SQL 不允许在 HAVING 里引用 SELECT 的别名。为了跨数据库一致,HAVING 里把聚合表达式重写一遍更稳。
5.4 聚合遇到 NULL:三个坑
坑一:SUM 全空返回 NULL,不是 0
SELECT SUM(price) FROM books WHERE price > 1000; -- NULL没有任何行符合条件时,SUM 返回 NULL。如果这个值要显示在报表上,得兜底:
SELECT COALESCE(SUM(price), 0) AS 合计 FROM books WHERE price > 1000; -- 0坑二:COUNT 的分母
前面说过,AVG、COUNT(列) 都只算非空行。如果"平均消费"要包含没消费的人(算作 0),得用 COALESCE 或显式补零:
AVG(COALESCE(amount, 0))坑三:GROUP BY 之后 COUNT(*) 数的是行,不是组
SELECT category, COUNT(*) FROM books GROUP BY category;这里的 COUNT(*) 是每个分类里的行数(因为已经在组内了)。想数"一共有几个分类",用 COUNT(DISTINCT category):
SELECT COUNT(DISTINCT category) FROM books; -- 45.5 一个完整的例子:每个客户的消费
把学到的拼起来,算"每个客户的有效消费额"(排除退款):
SELECT
c.name,
COUNT(DISTINCT o.id) AS 订单数,
COALESCE(ROUND(SUM(i.quantity * i.unit_price), 2), 0) AS 消费额
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'refunded'
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY c.id, c.name
ORDER BY 消费额 DESC;┌──────┬────────┬────────┐
│ name │ 订单数 │ 消费额 │
├──────┼────────┼────────┤
│ 王五 │ 3 │ 467.0 │
│ 李四 │ 2 │ 385.0 │
│ 张三 │ 3 │ 383.0 │
│ 赵六 │ 3 │ 362.0 │
│ 孙七 │ 0 │ 0 │
└──────┴────────┴────────┘四个细节值得注意:
LEFT JOIN让孙七出现。她从没下过单,但作为客户必须出现在报表里(第 6 章细讲);- 过滤条件写在
ON里(AND o.status <> 'refunded'),不是WHERE里。写进WHERE会把孙七也筛掉——这是LEFT JOIN的经典坑,下一章专门讲; - 李四只有 2 张有效订单。他有 3 张订单,其中订单 4 是退款,被
ON里的条件排除了,所以COUNT和SUM都只算剩下 2 张; COUNT(DISTINCT o.id):因为一张订单有多条明细,LEFT JOIN之后订单会被重复成多行,直接COUNT(*)会数成明细数。
本章小结
| 要点 | 说明 |
|---|---|
| 聚合把多行压成一行 | SUM 里可以先写表达式 |
COUNT(*) ≠ COUNT(列) | 后者跳过 NULL,常用于数据质量统计 |
GROUP BY 决定分组粒度 | 列越少组越粗 |
SELECT 非聚合列必须进 GROUP BY | SQLite 宽容,PostgreSQL 严格 |
WHERE 筛行,HAVING 筛组 | 能早筛的放 WHERE |
SUM 空集返回 NULL | 报表要 COALESCE 兜底 |
分组后 COUNT(*) 是组内行数 | 数组数用 COUNT(DISTINCT 列) |
下一章
到这一章为止,所有查询都还在一张表里打转(除了 5.5 那个预告)。真实的业务数据散在多张表里,下一章讲 JOIN——四种连接方式的区别、笛卡尔积是怎么冒出来的、以及 LEFT JOIN 把条件写错位置会有什么后果。