聚合与分组
统计报表、数据大盘、运营周报——这些需求的底层全是聚合查询。本章讲透 GROUP BY 的运行模型、5.7 迁移 8.0 时最常撞上的 ONLY_FULL_GROUP_BY 报错，以及条件聚合这个实战利器。
1. 聚合函数
| 函数 | 作用 | 对 NULL 的处理 |
|---|---|---|
| COUNT(*) | 统计行数 | 计入所有行 |
| COUNT(col) | 统计 col 非 NULL 的行数 | 跳过 NULL |
| SUM(col) | 求和 | 跳过 NULL |
| AVG(col) | 平均值 | 跳过 NULL（分母是非 NULL 行数） |
| MAX / MIN | 最大/最小 | 跳过 NULL |
| GROUP_CONCAT(col) | 组内拼接字符串 | 跳过 NULL |
SELECT COUNT(*) AS order_count,
SUM(total_amount) AS gmv,
AVG(total_amount) AS avg_amount,
MAX(total_amount) AS max_amount,
MIN(created_at) AS first_order_time
FROM orders
WHERE status IN (1, 2, 3);两个易错点：
- AVG 跳过 NULL：
AVG(score)的分母是非 NULL 行数。想把 NULL 当 0 算：AVG(IFNULL(score, 0))，两者业务含义完全不同，先想清楚要哪个。 - 空结果集：无分组聚合永远返回一行，
SUM对空集返回 NULL 而不是 0，程序侧记得IFNULL(SUM(x), 0)。
2. GROUP BY 分组
GROUP BY 的运行模型：按分组列把行分堆，每堆坍缩成一行输出，聚合函数在每堆内部计算。
-- 每个用户的订单数与消费总额
SELECT user_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_spent
FROM orders
GROUP BY user_id;
-- 多列分组按「列组合」分堆
SELECT user_id, status, COUNT(*) AS cnt
FROM orders
GROUP BY user_id, status;
-- 按表达式分组（日报表的标准写法）
SELECT DATE(created_at) AS order_date,
COUNT(*) AS order_count,
SUM(total_amount) AS daily_gmv
FROM orders
GROUP BY DATE(created_at)
ORDER BY order_date;3. ONLY_FULL_GROUP_BY：8.0 的严格模式
思考这条 SQL 有什么问题：
SELECT user_id, order_no, COUNT(*)
FROM orders
GROUP BY user_id;每个 user_id 分组里有多条订单，order_no 该显示哪一条的？逻辑上没有答案。5.7 默认会随机返回组内某一行的值（结果不确定却不报错，坑了无数人）；8.0 默认开启 ONLY_FULL_GROUP_BY，直接报错：
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'shop.orders.order_no' ...规则：SELECT 里的每一列，要么出现在 GROUP BY 里，要么被聚合函数包住。三种正确的改法：
-- 1. 你要的其实是聚合值
SELECT user_id, COUNT(*), MAX(order_no) FROM orders GROUP BY user_id;
-- 2. 你要的是「每组任取一个」，显式表达出来
SELECT user_id, ANY_VALUE(order_no), COUNT(*) FROM orders GROUP BY user_id;
-- 3. 你要的是「每组最新的一条完整行」→ 这是 Top-N 问题，用窗口函数（第 11 章）把 sql_mode 里的 ONLY_FULL_GROUP_BY 去掉确实能让老 SQL 跑起来，但那只是把「确定的报错」换成「不确定的结果」。正确做法是逐条修正 SQL 语义。
4. HAVING：过滤分组
WHERE 在分组前过滤行，HAVING 在分组后过滤组：
-- 消费总额超过 5000 的用户（只统计已支付以上状态）
SELECT user_id, SUM(total_amount) AS total_spent
FROM orders
WHERE status IN (1, 2, 3) -- 分组前：过滤行
GROUP BY user_id
HAVING SUM(total_amount) > 5000 -- 分组后：过滤组
ORDER BY total_spent DESC;原则：能写进 WHERE 的条件绝不放 HAVING。WHERE 先减少参与分组的行数，还能利用索引；HAVING 是对分组结果的兜底过滤，只放必须依赖聚合值的条件。
完整的逻辑执行顺序（和书写顺序不同，理解它能解释很多「为什么报错」）：
FROM → WHERE → GROUP BY → 聚合 → HAVING → SELECT → ORDER BY → LIMIT这也解释了：WHERE 里不能用聚合函数（还没算出来）、WHERE 里不能用 SELECT 别名（SELECT 还没执行），而 ORDER BY 可以用别名。
5. 条件聚合：一条 SQL 出多指标
CASE 塞进聚合函数，是报表 SQL 的核心技巧：
SELECT DATE(created_at) AS d,
COUNT(*) AS total_orders,
SUM(CASE WHEN status = 4 THEN 1 ELSE 0 END) AS cancelled,
SUM(CASE WHEN status IN (1,2,3)
THEN total_amount ELSE 0 END) AS paid_gmv,
SUM(status = 4) / COUNT(*) AS cancel_rate
FROM orders
GROUP BY DATE(created_at)
ORDER BY d;SUM(status = 4) 是 MySQL 特有的简写：布尔表达式结果是 0/1，SUM 就是满足条件的行数（等价于 COUNT(IF(status=4, 1, NULL))）。一条 SQL 同时算出总单量、取消量、支付 GMV、取消率——不需要跑四次查询。
6. WITH ROLLUP：自动加小计
SELECT user_id, status, COUNT(*) AS cnt, SUM(total_amount) AS amount
FROM orders
GROUP BY user_id, status WITH ROLLUP;+---------+--------+-----+----------+
| user_id | status | cnt | amount |
+---------+--------+-----+----------+
| 1 | 0 | 1 | 177.00 |
| 1 | 3 | 1 | 8127.00 |
| 1 | NULL | 2 | 8304.00 | ← user_id=1 的小计
| 2 | 1 | 1 | 3999.00 |
| 2 | NULL | 1 | 3999.00 |
| 3 | 4 | 1 | 8999.00 |
| 3 | NULL | 1 | 8999.00 |
| NULL | NULL | 4 | 21302.00 | ← 总计
+---------+--------+-----+----------+ROLLUP 按分组列从右往左逐层汇总，小计行的分组列显示 NULL。用 GROUPING(col) 可以区分「小计产生的 NULL」和「数据本身的 NULL」：
SELECT IF(GROUPING(user_id), '总计', user_id) AS u,
IF(GROUPING(status), '小计', status) AS s,
COUNT(*) AS cnt
FROM orders
GROUP BY user_id, status WITH ROLLUP;小结
- COUNT(*) 数行、COUNT(col) 数非 NULL；SUM 对空集返回 NULL 记得 IFNULL
- GROUP BY 把行分堆坍缩，SELECT 列必须「在分组里或被聚合」（ONLY_FULL_GROUP_BY）
- WHERE 分组前过滤、HAVING 分组后过滤，条件能前置就前置
- 条件聚合
SUM(CASE WHEN ...)一条 SQL 出全部指标 - WITH ROLLUP 免费得到小计与总计
- 统计每个商品分类的商品数、平均价、最高价，只保留商品数大于等于 2 的分类。
- 用条件聚合写一条 SQL：输出每天的订单总数、已完成数、取消数、取消率。
- 故意写一条违反 ONLY_FULL_GROUP_BY 的查询触发 1055 报错，然后分别用聚合函数和 ANY_VALUE 两种方式修正，并说明两者语义差异。