24. 聚合函数与 GROUP BY

为什么需要聚合（Aggregate）

到现在为止，我们的查询基本是”一行进、一行出”——SELECT 从表里取出哪些行，结果集就是哪些行。但很多业务问题问的不是”具体哪一行”，而是”整体怎么样”：

• 用户表里一共有多少人？
• 所有订单的金额加起来是多少？
• 用户的平均年龄多大？
• 年纪最大和最小的人分别是多少岁？
• 每个用户各自花了多少钱？

这类”把很多行压缩成一个数字”的问题，靠普通 WHERE 过滤解决不了，需要聚合函数（Aggregate Function）。聚合函数的作用，就是接收一个”值的集合”（一组行里的某一列），吐出一个”汇总后”的单一结果。

五个最常用的聚合函数

• COUNT(X)：统计列 X 中非 NULL 值的个数。
• COUNT(*)：统计行数（不管列里是不是 NULL，只要有这一行就数）。
• SUM(X)：把列 X 的所有非 NULL 值相加。
• AVG(X)：列 X 的平均值（非 NULL 值）。
• MIN(X) / MAX(X)：列 X 的最小值 / 最大值。

用全局统一的 users 表（id INTEGER PRIMARY KEY, name TEXT, age INTEGER, email TEXT）演示：

sqlite> SELECT COUNT(*) FROM users;
sqlite> SELECT COUNT(email) FROM users;
sqlite> SELECT AVG(age) FROM users;
sqlite> SELECT MIN(age), MAX(age) FROM users;

注意 COUNT(*) 和 COUNT(email) 的区别：COUNT(*) 数所有行；COUNT(email) 只数 email 不是 NULL 的行。如果某些用户没填邮箱，两者结果会不一样。这点很关键，别混用。

SUM 与 TOTAL 的坑：空结果和整数溢出

SUM(X) 有个容易踩的陷阱：当没有任何非 NULL 的输入行时，SUM(X) 返回 NULL，而不是 0。这在做”有就加、没有就算 0”的逻辑时会出问题。SQLite 因此额外提供了一个 TOTAL(X)，它和 SUM 几乎一样，但没有输入时返回 0.0，而且结果永远是浮点数，永远不会抛整数溢出错误。

sqlite> SELECT SUM(amount) FROM orders WHERE user_id = 999;
sqlite> SELECT TOTAL(amount) FROM orders WHERE user_id = 999;

如果 user_id = 999 没有任何订单，第一条返回 NULL，第二条返回 0.0。写报表、统计金额时，通常更想要 TOTAL 的”没有就是 0”行为。

AVG(X) 的结果永远是浮点数（只要至少有一个非 NULL 输入），哪怕所有年龄都是整数。它的本质就是 TOTAL(X) / COUNT(X)。

GROUP BY：把行分组后再聚合

单独聚合只能得到”全表一个数字”。但最实用的需求往往是”按某个维度分别统计”，比如”每个用户各自下了多少单、花了多少钱”。这就需要 GROUP BY。

GROUP BY 的逻辑是：按你指定的列，把值相同的行归到同一个组，然后对每个组分别跑一次聚合函数，每个组返回一行汇总结果。

以全局统一的 orders 表（id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL, created_at TEXT）为例，统计每个用户的总消费：

sqlite> SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
   ...> FROM orders
   ...> GROUP BY user_id;

这条语句会按 user_id 的值分组：所有 user_id = 1 的订单成一组，算出它的订单数和总金额；user_id = 2 的成另一组，依次类推。结果集里每个 user_id 只出现一行。

为什么 GROUP BY 后 SELECT 里不能乱写列

一个容易困惑的规则：一旦用了 GROUP BY，SELECT 后面出现的列，要么是 GROUP BY 里的分组列（如 user_id），要么是包在聚合函数里的列（如 SUM(amount)）。你不能写 SELECT user_id, name, SUM(amount) 而 name 既不在 GROUP BY 也不在聚合里——因为同一个 user_id 组里可能有多个 name，SQLite 不知道该取哪一个，结果在不同版本/配置下未定义。

多列分组

GROUP BY 可以跟多列，用逗号分隔。这时”分组键”是这些列的组合：只有多列的值都相同的行，才算同一组。

sqlite> SELECT user_id, substr(created_at, 1, 7) AS month, SUM(amount) AS total
   ...> FROM orders
   ...> GROUP BY user_id, month;

这里 created_at 是 TEXT 类型（我们始终用 TEXT 存日期，SQLite 没有 DATE 类型），substr(created_at,1,7) 取出”年-月”前缀，于是能算出”每个用户每个月”的消费额。

配合 ORDER BY 让结果有序

聚合结果默认顺序不保证美观，通常再接一个 ORDER BY 排序：

sqlite> SELECT user_id, COUNT(*) AS c FROM orders
   ...> GROUP BY user_id
   ...> ORDER BY c DESC;

这样消费最多的用户排在最前面。

GROUP_CONCAT / STRING_AGG：把组内的值拼成字符串

除了数字统计，SQLite 还提供把组内文本拼起来的聚合函数：group_concat(X) 或它的别名 string_agg(X, Y)，其中 Y 是分隔符（默认逗号）。

sqlite> SELECT user_id, group_concat(amount, '|') AS amounts
   ...> FROM orders
   ...> GROUP BY user_id;

这会把这个用户所有订单的 amount 用 | 连成一串。适合做”把一组值摊平展示”的小需求。

聚合函数遇到 NULL 怎么算

所有聚合函数都会先忽略 NULL 再计算：AVG(age) 不会把”没填年龄”的 NULL 当成 0 去拉低平均值，而是直接跳过这些行；SUM(amount) 也只累加非 NULL 的金额。唯一例外是 COUNT(*)——它数的是”行”，跟列里有没有 NULL 无关。这正好解释了为什么 COUNT(email) 和 COUNT(*) 可能不同：前者跳过了邮箱为空的那些行，后者照单数不误。写统计时脑子里要始终装着”NULL 被无视”这条规则，否则平均值、总和都会和你预期对不上。

一个完整点的示例：把两张表连起来统计

把全局统一的 users 和 orders 两张表结合起来（连接 JOIN 的写法后续章节会细讲），就能算出”每个用户的名字、订单数、总消费”：

sqlite> SELECT u.name, COUNT(o.id) AS orders, TOTAL(o.amount) AS spent
   ...> FROM users u LEFT JOIN orders o ON u.id = o.user_id
   ...> GROUP BY u.id, u.name
   ...> ORDER BY spent DESC;

这里用 LEFT JOIN，保证”还没下过单的用户”也出现在结果里（订单数 0、消费 0.0），而不是被悄悄地丢掉了。它能直观展示”聚合 + 分组”的威力：一眼看出谁是大客户、谁还是零消费。注意 GROUP BY 同时写了 u.id 和 u.name——因为 name 不在聚合里，就必须进分组键，否则 SQLite 不知道同一个 id 该取哪个 name。

适用场景小结

• 全表总览：COUNT(*)、AVG(age)、MIN/MAX 直接用在整张表上。
• 分维度报表：GROUP BY + 聚合，是 BI 报表、后台统计的核心写法。
• 金额累加：优先 TOTAL 而非 SUM，规避 NULL 与溢出。
• 去重计数：COUNT(DISTINCT 列)。

类比小结

把聚合函数想成”榨汁机”：把一堆水果（一组行）丢进去，出来一杯果汁（一个汇总值）。GROUP BY 则是”先按颜色把水果分筐，再每筐各榨一杯”——于是你得到的是”每筐一杯”，而不是”全部混在一起一杯”。COUNT 数个数、SUM 加起来、AVG 求平均、MIN/MAX 取两端，五个函数各司其职；金额累加记得用 TOTAL 躲开 NULL 和溢出的坑。