一、五个函数，两个陷阱

COUNT、SUM、AVG、MAX、MIN 五个函数，前三个有坑。

前三行的差别是第一个陷阱。 COUNT(*) 数的是行数，COUNT(列) 数的是「这一列不为空的行数」。 差的那 135 行，就是 cat_id 为空的那些商品。 所以「这张表有多少行」用 COUNT(*)，「这一列填了多少个值」才用 COUNT(列)。 两者混用，在有空列的表上会得到两个不同的数，而你以为它们该相等。 第三行再加一层 DISTINCT，数的是「有多少个不同的值」，和行数完全不是一回事。

二、AVG 的分母：非空的行数

第二个陷阱是 AVG 除以几。 它自动跳过空值，用的是「这一列有值的行数」，不是总行数。

第一行和第三行一致，第二行偏小。 因为第二行把 135 个空行也算进了分母，分子却没算它们。 这里只差一点点，是因为空行只占 135/5000。 换成「一个班四十个人、缺考五个」这种数据，平均分能差好几分。 回头再看 price：那一列一个空值都没有，三种算法给出同一个数。 所以「有没有空值」直接决定了这两个公式等不等价——写之前先看一眼数据。

三、GROUP BY、HAVING 与 WITH ROLLUP

GROUP BY 把行按某列分成组，聚合函数在每组里各算一次；HAVING 再把组筛一遍。 两个都写在 GROUP BY 之后，所以 HAVING 里能用别名，WHERE 里不能。 SELECT 列表里出现的列，要么在 GROUP BY 里，要么包在聚合函数里。 写别的列，MySQL 8.0 默认会报错（ONLY_FULL_GROUP_BY），因为那是个没有意义的值。 WITH ROLLUP 会在结果末尾加一行「总计」，那一行的分组列是 NULL。 沙箱没有这个语法，等价写法是再 UNION ALL 一条不带 GROUP BY 的统计。 等价写法有一个副作用要留意：结果里会出现两个 NULL。 第一行的 NULL 是「没类别」那一组（135 行），最后一行的 NULL 才是总计（5000 行）。 靠行数才分得清哪个是哪个，这是 WITH ROLLUP 一个常被忽略的坑——它不加标记列。

四、GROUP BY 有没有索引

GROUP BY 要干的事是「把相同的值聚在一起」，而索引本身就是按那一列排好序的。 所以给 GROUP BY 的列建索引，省掉的是排序那一趟。 这一条可以推广：凡是 GROUP BY、ORDER BY、DISTINCT 用到的列，都值得建索引。 它们都在做同一件事——把相同的值排到一起。 但别把它当定律：数据量小的时候差别不明显，写多的表还要算上维护索引的代价。 该不该建，最后还得看真实数据量和查询比例。

五、动手 Demo：五个聚合函数，和两个分母陷阱

这一段的五节顺序是：先看五个函数各给什么，再用 cat_id 这个有空值的列把「分母」这件事量出来， 最后看 GROUP BY 有没有索引差多少。

import sqlite3

c = sqlite3.connect(u"d038.db")

# ① 五个函数
for sql in [u"SELECT COUNT(*) FROM tb_goods",
            u"SELECT COUNT(cat_id) FROM tb_goods",
            u"SELECT AVG(price), SUM(price) FROM tb_goods"]:
    print(c.execute(sql).fetchone())

# ② 分母：AVG 除以非空行数
n_all = c.execute(u"SELECT COUNT(*) FROM tb_goods").fetchone()[0]
n_cat = c.execute(u"SELECT COUNT(cat_id) FROM tb_goods").fetchone()[0]
s_cat = c.execute(u"SELECT SUM(cat_id) FROM tb_goods").fetchone()[0]
print(u"AVG     =", c.execute(u"SELECT AVG(cat_id) FROM tb_goods").fetchone()[0])
print(u"除总行数 =", s_cat / n_all)      # 偏小
print(u"除非空行 =", s_cat / n_cat)      # 与 AVG 一致

# ③ WITH ROLLUP 的等价写法
c.execute(u"SELECT cat_id, COUNT(*) AS n FROM tb_goods GROUP BY cat_id "
          u"UNION ALL SELECT NULL, COUNT(*) FROM tb_goods").fetchall()

# ④ 索引对 GROUP BY 的影响
c.execute(u"CREATE INDEX i_cat ON tb_goods (cat_id)")

实跑输出：

--------------------------------------------------------------------
第三十八课：五个聚合函数，和两个分母陷阱
--------------------------------------------------------------------

  **教材 9.3 讲 `COUNT`、`SUM`、`AVG`、`MAX`、`MIN`。**
  **五个都会算不难，难的是两个分母问题：`COUNT` 数什么、`AVG` 除以几。**
  **这一课把这两个问题真跑出来，再讲 `GROUP BY` 和 `HAVING`。**

  一、五个函数各算一遍
  --------------------------------------------------------------------

  写法                      结果
  ----------------------------------
  `COUNT(*)`                5000
  `COUNT(cat_id)`           4865
  `COUNT(DISTINCT cat_id)`  10
  `SUM(price)`              497750.0
  `AVG(price)`              99.55
  `MAX(price)`              200.9
  `MIN(price)`              1.0
  **前三行是这一节的第一个陷阱。**
  **`COUNT(*)` 数的是行数，`COUNT(列)` 数的是「这一列不为空的行数」。**
  **这里差的那 135 行，就是 `cat_id` 为空的那些商品。**
  **所以「这张表有多少行」要用 `COUNT(*)`，「这一列填了多少个值」才用 `COUNT(列)`。**
  **两者混用，在有空列的表上会得到两个不同的数，而你以为它们该相等。**

  二、AVG 的分母是「非空的行数」
  --------------------------------------------------------------------

  算法（算 cat_id 的平均值）     结果    对不对
  ---------------------------------------------------------
  `AVG(cat_id)`                  5.4995  对的
  `SUM(cat_id) / COUNT(*)`       5.351   错的（分母含空行）
  `SUM(cat_id) / COUNT(cat_id)`  5.4995  对的
  **第一行和第三行一致，第二行偏小。**
  **因为 `AVG` 会自动跳过 `cat_id` 为空的行，它用的分母是「这一列有值的行数」。**
  **这里只差一点点，是因为空行只占 135/5000。**
  **换成「一个班四十个人、缺考五个」这种数据，平均分能差好几分。**
  **回头再看 `price`：那一列一个空值都没有，三种算法给出同一个数。**
  **所以「有没有空值」直接决定了这两个公式等不等价——写之前先看一眼数据。**

  三、GROUP BY 与 HAVING
  --------------------------------------------------------------------
  **`GROUP BY` 把行按某列分成组，聚合函数在每组里各算一次。**
  **`HAVING` 再把这些组筛一遍。**
  cat_id n   ap
  -----------------
  10     487 99.24
  7      487 99.26
  4      487 99.29
  3      487 99.41
  1      487 99.11
  9      486 99.42
  8      486 100.11
  6      486 99.8
  5      486 100.09
  2      486 99.65
  10 rows in set
  **三列分别是：类别、这个类别的商品数、这个类别的平均价。**
  **`HAVING n > 400` 用的是别名 —— 这在 MySQL 里能用，标准 SQL 不保证，别的库可能报错。**
  **还有一个必须记住的规则：`SELECT` 列表里出现的列，要么在 `GROUP BY` 里，要么包在聚合函数里。**
  **写别的列，MySQL 8.0 默认会报错（`ONLY_FULL_GROUP_BY`），因为那是个没有意义的值。**

  四、WITH ROLLUP：多一行合计
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | SELECT cat_id, COUNT(*) FROM tb_goods GROUP BY cat_id WITH ROLLUP;
  ② 教材原码，本沙箱不执行

  **`WITH ROLLUP` 会在结果末尾加一行「总计」，`cat_id` 那一格是 NULL。**
  **沙箱里没有这个语法，等价写法是再 `UNION ALL` 一条不带 `GROUP BY` 的统计。**
  cat_id n
  -----------
  NULL   135
  1      487
  2      486
  3      487
  4      487
  5      486
  6      486
  7      487
  8      486
  9      486
  10     487
  NULL   5000
  12 rows in set
  **最后一行 `cat_id` 是 NULL、`n` 是 5000，就是总计。**
  **注意第一行的 NULL 不是总计，那是「没类别」那一组，只有 135 行。**
  **所以结果里会出现两个 NULL，靠行数才分得清哪个是哪个。**
  **这是 `WITH ROLLUP` 一个常被忽略的坑：它不给你加标记列。**
  **这个写法比 `WITH ROLLUP` 啰嗦，但它是标准 SQL，哪个库都能跑。**

  五、GROUP BY 有没有索引，差多少
  --------------------------------------------------------------------

  条件             耗时
  --------------------------
  `cat_id` 没索引  2.27 毫秒
  `cat_id` 有索引  689 微秒
  **有索引之后快了一截，因为索引本身就是按 `cat_id` 排好序的。**
  **`GROUP BY` 要做的「把相同的值聚在一起」，索引已经替它做完了。**
  **这一条可以推广：凡是 `GROUP BY`、`ORDER BY`、`DISTINCT` 用到的列，都值得建索引。**
  **它们都在做同一件事——把相同的值排到一起。**

AVG(cat_id) 和 SUM(cat_id)/COUNT(cat_id) 都是 5.4995，SUM(cat_id)/COUNT(*) 是 5.351。 三个式子只差分母，结果差 0.15。 这 0.15 看着不大，但它的意思是「135 个空值被当成了 0 参与平均」。 数据里空值比例越高，这个偏差越离谱，而且它不会报错。 WITH ROLLUP 的等价写法给出 12 行：135、十个类别、5000。 中间那十个类别加起来正好 4865，就是 COUNT(cat_id)。 最后那一段是索引的对比：2.21 毫秒降到 714 微秒，快了三倍。 注意这是「建索引前后」的对比，不是「两种写法」的对比——写法一个字没改。

30 秒实验

1. 把 COUNT(cat_id) 改成 COUNT(price)，看它还给不给 4865，想想为什么。
2. 把 AVG(cat_id) 换成 AVG(price)，看三种算法是不是变成一样了。
3. 把 HAVING 的阈值从 400 调到 1000，看结果从十行变成零行。

常见误区

1. 用 COUNT(列) 当「表有多少行」，碰上空值就少算了。
2. 以为 SUM(x)/COUNT(*) 和 AVG(x) 永远相等，在有空值的列上算错了平均值。
3. 以为 WITH ROLLUP 的总计行有特殊标记，结果把它和「空值那一组」搞混了。

聚合把行变成数，下一篇 9.4 讲把两张表拼起来：五种连接，和忘写条件的那场灾难。

• ☐ （本篇完，下一篇 9.4 连接查询）

相关笔记

• 基本查询语句
• 单表查询
• 删除数据
• 🛢️ MySQL从入门到精通 · 索引