Skip to content
Lv1SQL 与数据库进度 5 / 12

第 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)

先算全店的总销量和总营收:

sql
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 的两种用法 ​

sql
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:按维度切桶 ​

整体统计只有一行,用处有限。真正有用的是"分组统计":每本书卖了多少。

sql
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 在这一组内求和。

看结果会发现两件事:

  1. 销量最高的不是营收最高的。《SQL 必知必会》卖 7 本只有 343 元,而《深入理解计算机系统》卖 3 本就 384 元。单价差异让两个指标排序不同——这就是为什么不能只看一个指标。
  2. GROUP BY 要带上 b.id。只写 b.title 也能跑,但书名理论上可能重复(两本同名书),带上 id 才是严格按"这本书"分组。

分组的规则:SELECT 里只能出现分组列和聚合 ​

sql
-- 错误示范
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 里。

多列分组 ​

sql
SELECT category, COUNT(*) AS 书目数
FROM books
GROUP BY category
ORDER BY 书目数 DESC;
┌──────────┬────────┐
│ category │ 书目数 │
├──────────┼────────┤
│ 文学     │      3 │
│ 计算机   │      2 │
│ 数据库   │      2 │
│ 历史     │      1 │
└──────────┴────────┘

想更细,加一列:GROUP BY category, author 会按"分类 + 作者"的组合分组,组合不同的就分成不同组。分组的粒度由你列的多少决定,越少越粗。

NULL 会自成一个组 ​

sql
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 本的书。

sql
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 里用别名 ​

sql
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 ​

sql
SELECT SUM(price) FROM books WHERE price > 1000;  -- NULL

没有任何行符合条件时,SUM 返回 NULL。如果这个值要显示在报表上,得兜底:

sql
SELECT COALESCE(SUM(price), 0) AS 合计 FROM books WHERE price > 1000;  -- 0

坑二:COUNT 的分母 ​

前面说过,AVG、COUNT(列) 都只算非空行。如果"平均消费"要包含没消费的人(算作 0),得用 COALESCE 或显式补零:

sql
AVG(COALESCE(amount, 0))

坑三:GROUP BY 之后 COUNT(*) 数的是行,不是组 ​

sql
SELECT category, COUNT(*) FROM books GROUP BY category;

这里的 COUNT(*) 是每个分类里的行数(因为已经在组内了)。想数"一共有几个分类",用 COUNT(DISTINCT category):

sql
SELECT COUNT(DISTINCT category) FROM books;  -- 4

5.5 一个完整的例子:每个客户的消费 ​

把学到的拼起来,算"每个客户的有效消费额"(排除退款):

sql
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 │
└──────┴────────┴────────┘

四个细节值得注意:

  1. LEFT JOIN 让孙七出现。她从没下过单,但作为客户必须出现在报表里(第 6 章细讲);
  2. 过滤条件写在 ON 里(AND o.status <> 'refunded'),不是 WHERE 里。写进 WHERE 会把孙七也筛掉——这是 LEFT JOIN 的经典坑,下一章专门讲;
  3. 李四只有 2 张有效订单。他有 3 张订单,其中订单 4 是退款,被 ON 里的条件排除了,所以 COUNT 和 SUM 都只算剩下 2 张;
  4. COUNT(DISTINCT o.id):因为一张订单有多条明细,LEFT JOIN 之后订单会被重复成多行,直接 COUNT(*) 会数成明细数。

本章小结 ​

要点说明
聚合把多行压成一行SUM 里可以先写表达式
COUNT(*) ≠ COUNT(列)后者跳过 NULL,常用于数据质量统计
GROUP BY 决定分组粒度列越少组越粗
SELECT 非聚合列必须进 GROUP BYSQLite 宽容,PostgreSQL 严格
WHERE 筛行,HAVING 筛组能早筛的放 WHERE
SUM 空集返回 NULL报表要 COALESCE 兜底
分组后 COUNT(*) 是组内行数数组数用 COUNT(DISTINCT 列)

下一章 ​

到这一章为止,所有查询都还在一张表里打转(除了 5.5 那个预告)。真实的业务数据散在多张表里,下一章讲 JOIN——四种连接方式的区别、笛卡尔积是怎么冒出来的、以及 LEFT JOIN 把条件写错位置会有什么后果。

👉 第 6 章:JOIN 连接多张表

本文阅读--次|全站访问--次|访客--人