外观
07 - GROUP BY 为什么经常算错:聚合、去重与窗口函数
摘要与阅读定位
同一批订单,为什么能算出不同的客户人数?为什么“最新时间”和“最大金额”拼在一起,却不属于任何一笔订单?这些问题往往来自三个定义:输入中一行代表什么、未知值怎样参与统计、并列时怎样选择。本文用同一份最小数据解释人数统计、每组最新、Top N、累计金额和日期补齐,先判断结果是否符合需求,再讨论执行成本。
- 前置:05 JOIN 基数、06 过滤阶段、10 NULL。
- 后续:15 排序聚合与临时空间;应用:04 范式与反范式,预计算报表还需讨论维护一致性。
- 基线:MySQL 8.0.16+ / 8.4、InnoDB;窗口函数及公共表表达式(CTE)为 MySQL 8.0 功能,本文的
EXPLAIN ANALYZE需 8.0.18+。实验方法见 A,以下结果均为预期输出。
初次先用三条订单理解分组与窗口,再读第 2—3 节的统计口径。每组最新、累计和排名按问题逐节验证;第 7 节补日期与第 8 节执行计划是可选进阶,不要求先会写递归查询。
先说清:要每组一行,还是每笔订单一行
客户 10 有三笔订单,金额 100、40、40。两种报告不同:
| 需求 | 输出行数 | 使用方式 |
|---|---|---|
| 每个客户的订单总额 | 客户 10 一行,总额 180 | GROUP BY 后 SUM |
| 保留每笔订单,并显示该客户总额 | 三行,每行附上总额 180 | SUM 配合窗口 OVER |
粒度指一行代表什么:一笔订单、一个客户,还是一个客户在某种状态下的汇总。GROUP BY 按指定字段分组,聚合函数再把组内多个值汇成一个结果,例如 SUM 求和、COUNT 计数。按客户分组后,结果就从“一笔订单一行”变成“一个客户一行”。
窗口函数则在现有行上计算附加结果,保留每笔订单。OVER 声明窗口计算规则,PARTITION BY customer_id 把输入按客户划成各自计算的分区;这里的分区只是查询内的计算范围,不是物理分库分表。
金额合计、订单条数、客户人数也不同:上例总额是 180、订单三笔、已知客户一人。先明确数什么,再选择 SUM、COUNT(*) 或 COUNT(DISTINCT customer_id)。
1. 初始化:同时间、同金额、NULL 和零都不能省略
先结束实验事务,在隔离库初始化本篇 agg_* 表。下面会删除并重建 agg_order,仅用于可丢弃的教学数据。NULL 表示值未知或缺失,金额 0 则是已知的零元;重复时间用于观察并列排序,重复金额用于区分明细与去重后的值。
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit=1;
DROP TABLE IF EXISTS agg_order;
CREATE TABLE agg_order (
id BIGINT PRIMARY KEY,
customer_id BIGINT NULL,
status VARCHAR(16) NOT NULL,
amount DECIMAL(10,2) NULL,
created_at DATETIME NOT NULL
) ENGINE=InnoDB;
INSERT INTO agg_order VALUES
(1,10,'PAID',100,'2026-09-01 10:00:00'),
(2,10,'CREATED',40,'2026-09-02 10:00:00'),
(3,10,'PAID',40,'2026-09-02 10:00:00'),
(4,20,'PAID',NULL,'2026-09-01 10:00:00'),
(5,20,'PAID',0,'2026-09-03 10:00:00'),
(6,NULL,'CREATED',50,'2026-09-01 11:00:00'),
(7,NULL,'CREATED',NULL,'2026-09-03 11:00:00');
SELECT @@session.sql_mode;确认启用 ONLY_FULL_GROUP_BY;如果没有,先停止相关反例验证,在独立教学会话按项目配置启用并记录原值,结束和失败时均还原。不要关闭它来使反例“能跑”。这个模式帮助拒绝一些无法确定输出值的分组写法,不替代业务统计口径。
先只取客户 10 的三笔订单,执行这两条基础对照:
sql
SELECT customer_id, COUNT(*) AS order_rows, SUM(amount) AS customer_total
FROM agg_order
WHERE customer_id = 10
GROUP BY customer_id;推导预期一行 (10,3,180)。窗口版本:
sql
SELECT id, customer_id, amount,
SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM agg_order
WHERE customer_id = 10
ORDER BY id;预期输出三行:id 1、2、3 的金额为 100、40、40,附加总额都为 180。这里没有窗口内 ORDER BY,三行计算时都使用整个客户分区,所以总额相同;按时间累计则需要为每行定义不同的参与范围,第 5 节再展开。
2. 三种客户数:数的对象不同
COUNT(*) 数输入行,COUNT(customer_id) 只数客户号非 NULL 的行,COUNT(DISTINCT customer_id) 则先排除 NULL,再数不同客户号。订单表中同一客户可以出现多次,因此前两种计数都不能直接当作客户人数。这里的“已知客户”仅指客户号非 NULL。
sql
SELECT COUNT(*) AS order_rows,
COUNT(customer_id) AS known_customer_rows,
COUNT(DISTINCT customer_id) AS known_customers,
COUNT(amount) AS known_amount_rows,
SUM(amount) AS total_amount,AVG(amount) AS avg_known_amount
FROM agg_order
WHERE created_at>='2026-09-01' AND created_at<'2026-10-01';按查询列顺序,预期输出为 7,5,2,5,230,46:七笔订单中五笔有客户号,但只涉及客户 10 和 20;两笔客户号未知的订单不能据此认定属于同一个真实客户。SUM(amount) 与 AVG(amount) 都忽略 NULL 金额,零元仍参与计算,所以已知金额合计为 230,平均值为 230 / 5 = 46,分母不是七笔订单。
sql
SELECT customer_id,COUNT(*) AS order_rows,SUM(amount) AS total_amount
FROM agg_order GROUP BY customer_id ORDER BY customer_id IS NULL,customer_id;
SELECT COUNT(*) AS order_rows,COUNT(amount) AS known_rows,SUM(amount) AS total_amount
FROM agg_order WHERE id=-1;
SELECT customer_id,COUNT(*) FROM agg_order WHERE id=-1 GROUP BY customer_id;第一条查询的预期输出:客户 10→3,180;20→2,0;NULL 组→2,50。GROUP BY 会把客户号为 NULL 的行放到同一组,这是分组规则,不代表已确认它们属于同一客户。客户 20 的金额为 NULL 和 0,忽略未知金额后仍有一个已知的 0,所以总额为 0。
后两条查询用 id=-1 构造空输入。无 GROUP BY 的聚合仍返回一行 0,0,NULL;有 GROUP BY 时没有任何组可输出,因此返回零行。全 NULL 组与空输入的 SUM 都是 NULL,但前者的 COUNT(*) 大于零,后者为零,可以据此区分,详见 10。
3. 分组粒度与函数依赖
按客户分组时,一组内可以有多个订单状态。金额可以求和,但如果还要输出一个未经聚合的 status,数据库就需要知道该取哪个值。
sql
SELECT customer_id,status,SUM(amount)
FROM agg_order GROUP BY customer_id;启用 ONLY_FULL_GROUP_BY 时,这是预期失败的反例:客户 10 下有两个 status,客户号不能唯一确定状态。函数依赖指一个字段或字段组合能唯一确定另一个值,例如本表主键能确定该行的其他列。
这个模式要求未聚合表达式是分组项、由分组项函数确定,或符合 MySQL 可证明的单值限制。主键确定本表其他列是常见合法情形,但 MySQL 对跨表达式和连接的函数依赖检测有边界,不能假设它能证明所有逻辑关系。
正确统计客户与状态:
sql
SELECT customer_id,status,SUM(amount) AS total_amount
FROM agg_order GROUP BY customer_id,status
ORDER BY customer_id IS NULL,customer_id,status;预期客户 10 分成 CREATED=40、PAID=140。增加 status 分组项后,一行代表“客户与状态组合”,统计粒度随之改变。ANY_VALUE(status) 或关闭该 SQL 模式,只会允许输出未指定选择规则的状态,不能实现“最新状态”。
去重也要先确定对象。SUM(DISTINCT amount) 去掉的是重复金额值,不是重复订单;客户 10 的两笔 40 元订单都是真实明细,去重金额会少算一笔。因此它不能修复 05中的连接放大,应该先查清连接后每笔订单为何重复。
同一查询混合聚合与窗口时,窗口处理的是分组、聚合及 HAVING 之后的结果行。例如先按客户汇总,再对汇总金额排名,排的是客户总额,窗口已经看不到每笔原始订单。
4. 每组最新:整行选择,不是多个 MAX 拼接
“最新”是选中一条真实记录,不是从不同记录各摘一个最大值。下面的 WITH 给中间查询取名 ranked,外层再筛选;这称为公共表表达式(CTE),此处不涉及递归。
sql
SELECT customer_id,MAX(created_at) AS latest_time,MAX(amount) AS amount
FROM agg_order WHERE customer_id=10 GROUP BY customer_id;预期最新时间是 9 月 2 日,但最大金额为 100,来自 9 月 1 日;这条组合记录不存在。两个 MAX 分别计算各自字段的最大值,不会约束它们来自同一行。要取得最新订单的状态与金额,应先按明确顺序选中整行:
ROW_NUMBER() 为分区内的行依次编号,从 1 开始。下面先按时间倒序排列;时间相同时再按 id 倒序决定先后,外层只取编号为 1 的行。
sql
WITH ranked AS (
SELECT id,customer_id,status,amount,created_at,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY created_at DESC,id DESC
) AS row_num
FROM agg_order
)
SELECT id,customer_id,status,amount,created_at
FROM ranked WHERE row_num=1
ORDER BY customer_id IS NULL,customer_id;预期 ID 为 3、5、7。这里约定未知客户也组成一组;若不应参与,需在 CTE 输入中加 customer_id IS NOT NULL。id DESC 是同时间的业务决胜规则,不证明“较大 ID 一定较晚提交”。若要返回所有并列最新,使用按时间排名的 RANK,而不是随意挑一条。
从查询的逻辑阶段看,WHERE 先过滤输入,窗口结果随后才形成,因此同级 WHERE 不能使用窗口结果。CTE 或派生表把编号变成外层可读取的列,外层才能用 row_num=1 过滤。
过滤位置也改变问题:先用 WHERE 限制本月,再排名,求的是“本月内最新”;先对全量订单排名,再在外层限制月份,求的是“全历史最新恰好在本月”。两者使用的排名输入不同。
5. ROWS 与 RANGE:先造重复排序键再验证
累计金额需要为当前行确定参与求和的范围,这个范围称为窗口帧。UNBOUNDED PRECEDING 表示从分区起点开始,CURRENT ROW 表示当前行边界。ROWS 按行位置定义范围,RANGE 按排序值定义范围;以下比较同一时间的两笔订单如何参与累计。
sql
SELECT id,amount,created_at,
SUM(amount) OVER (ORDER BY created_at) AS default_total,
SUM(amount) OVER (
ORDER BY created_at RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS range_total,
SUM(amount) OVER (
ORDER BY created_at,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS row_total
FROM agg_order WHERE customer_id=10
ORDER BY created_at,id;以下为预期输出:
| ID | 金额 | 默认帧 | RANGE | 稳定 ROWS |
|---|---|---|---|---|
| 1 | 100 | 100 | 100 | 100 |
| 2 | 40 | 180 | 180 | 140 |
| 3 | 40 | 180 | 180 | 180 |
对于这里的 SUM 窗口,有 ORDER BY 且未写帧时,MySQL 默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这个帧包含当前排序值相同的所有行,这些并列行称为 peers。因此计算 ID 2 时,同时间的 ID 3 也参与求和,金额从 100 一次增加到 180。
RANGE 的排序故意只用 created_at;若加入唯一 id,并列行被拆开,此样本就无法揭示差异。排名函数有自己的计算规则,不能以 SUM 的帧行为推断所有窗口函数。
ROWS 按行位置累计;只按 created_at 排序的 ROWS 在并列行间顺序不确定,因此本查询加 id 获得稳定顺序。外层 ORDER BY 只保证展示顺序,不修复窗口内部排序。
6. Top N:条数、名次与不同档位
“前两名”可能要求恰好两条订单,也可能要求保留第二名的所有并列订单,或者取最高的两个不同金额档位。对应选择是:ROW_NUMBER 为每行独立编号;RANK 让并列行共享名次,之后跳号;DENSE_RANK 也保留并列,但之后不跳号。
sql
WITH ranked AS (
SELECT id,amount,
ROW_NUMBER() OVER (ORDER BY amount DESC,id) AS row_num,
RANK() OVER (ORDER BY amount DESC) AS rank_num,
DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank_num
FROM agg_order WHERE customer_id=10
)
SELECT * FROM ranked ORDER BY row_num;预期 ID 1→1,1,1;ID 2→2,2,2;ID 3→3,2,2。过滤 row_num<=2 得两条;rank_num<=2 得三条;dense_rank_num<=2 得两个金额档位的三条。
三个名次都按“金额”定义,只有 ROW_NUMBER 添加 ID 决胜;如果给 RANK 也加唯一 ID,就抹掉金额并列。RANK 并列后跳号,如金额序列 100,100,40 得 1,1,3,DENSE_RANK 得 1,1,2。各客户 Top N 时再加 PARTITION BY customer_id。过滤 NULL 金额或规定其排序位置须显式表达。
7. 连续日期统计:三行不必等于三天
本节是可选进阶。三日累计要求输入一天一行;订单明细却可能一天多行,也可能某天没有订单。需要先按天汇总,再补齐没有记录的日期,最后计算窗口。
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 包含当前行及之前最多两行。直接用于订单明细,表示最多三笔订单;用于连续且每天一行的日期表,才表示当前日及之前两日。本例只生成三天日历;生产可使用日历维表(预先保存日期的辅助表)或有界日期范围,避免无限递归。
下面的 WITH RECURSIVE 定义递归 CTE:calendar 从 9 月 1 日开始,每次增加一天,到 9 月 3 日停止;daily 汇总每日订单;dense_days 从日历出发做左连接,保证每个日期都有一行。COALESCE 返回参数中第一个非 NULL 值,这里用于补齐缺少的统计值。
sql
WITH RECURSIVE calendar AS (
SELECT DATE('2026-09-01') AS day
UNION ALL
SELECT day+INTERVAL 1 DAY FROM calendar WHERE day<DATE('2026-09-03')
), daily AS (
SELECT DATE(created_at) AS day,SUM(amount) AS known_amount,
COUNT(*) AS order_rows,COUNT(amount) AS known_rows
FROM agg_order
WHERE created_at>='2026-09-01' AND created_at<'2026-09-04'
GROUP BY DATE(created_at)
), dense_days AS (
SELECT calendar.day,COALESCE(daily.known_amount,0) AS known_amount,
COALESCE(daily.order_rows-daily.known_rows,0) AS unknown_orders
FROM calendar LEFT JOIN daily ON daily.day=calendar.day
)
SELECT day,known_amount,unknown_orders,
SUM(known_amount) OVER (
ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS trailing_three_day_known_amount
FROM dense_days ORDER BY day;预期:9 月 1 日 150,1,150;2 日 80,0,230;3 日 0,1,230。这是已知金额累计,并显式暴露未知金额记录数,不是把未知订单宣称为零元。若要求每行完整三日窗口,输入日历应包含报告起日前两日,再在窗口外过滤报告日期;前两行这里仅有一/两天。
业务日切分沿用 06 的时区约定。日期稠密化后一天一行,ROWS 才能表达连续日窗口;同时要确认日历没有缺口或重复。
8. 验证、排障与成本边界
sql
EXPLAIN ANALYZE
SELECT customer_id,status,SUM(amount)
FROM agg_order GROUP BY customer_id,status;EXPLAIN ANALYZE 实际运行 SELECT 并报告执行信息,只在隔离环境执行。结果虽然可能只有几行,聚合仍要处理形成这些组的输入;所以应观察聚合前输入量、组数、排序和临时表,不能仅凭输出行数判断成本。
Sort Aggregate(排序聚合)和 Hash Aggregate(哈希聚合)是通用的物理执行模型,分别借助排序和哈希组织分组。MySQL 使用临时表聚合,不意味着可以直接套用其他产品的 Hash Aggregate 名称;应以目标版本执行计划中的算子为证据。DISTINCT 也可能通过索引、临时表或排序实现,不能保证固定算法。内存不足时部分中间数据可能写入磁盘,这类溢写及临时空间成本见 15。
| 症状 | 第一项核对 | 后续取证 |
|---|---|---|
| 总额成倍放大 | JOIN 后每笔业务重复次数 | 05输出左右主键 |
| 人数/平均数偏差 | NULL、空输入和分母口径 | 10与独立计数 |
| 最新记录拼错 | 是否多个 MAX 来自不同记录 | 整行排名与同时间决胜样本 |
| 累计跳增/排名抖动 | 并列行、窗口范围和排序键 | 同时间、同金额反例 |
| 报表耗时/磁盘膨胀 | 输入量、组数、溢写证据 | 14 执行计划、15 临时空间 |
基础验收先确认“总额一行”和“保留明细三行”都符合需求;再分别核对人数、最新记录、累计与排名,不一次混合所有公式。任何预期失败反例单独执行,保留原错误,不靠修改 SQL 模式跳过。
清理前结束事务,若改过教学会话 sql_mode,应恢复记录的旧值;然后仅在隔离库删除 agg_order。预计算可降低读取成本,但带来重复消费、更新与删除补偿、口径重算和一致性验收;这些取舍在 04继续,而非看到慢聚合就立即加冗余字段。
9. 名词解释
| 名词 | 含义 |
|---|---|
| 分组 / 窗口 | 按键汇总为组结果 / 在输入行上增加分组或排序计算结果 |
| PARTITION BY / OVER | 划分窗口计算组 / 声明窗口计算范围与排序等规则 |
| ROW_NUMBER / RANK / DENSE_RANK | 每行独立编号 / 并列同名次且后面跳号 / 并列同名次且不跳号 |
| 窗口帧 / peers | 当前行计算时参与的行范围 / 窗口排序值相同的并列行 |
| ROWS / RANGE | 按行位置定义范围 / 按排序值等规则定义范围;不是物理数据库页范围 |
| CTE / WITH | 可命名的中间查询 / 定义中间查询的写法,不一定递归 |
| 日期补齐 | 让需要统计的日期都存在一行,避免把三条记录当成三天 |
| ONLY_FULL_GROUP_BY | MySQL 的分组合法性检查模式,不能保证业务统计定义正确 |
参考资料
- MySQL 8.4 Reference Manual:§14.19.1 Aggregate Function Descriptions、§14.19.3 MySQL Handling of GROUP BY、§14.20.2 Window Function Concepts and Syntax、§14.20.3 Window Function Frame Specification。
- 适用条件:结果集为明确预期,实际计划和其他产品的窗口支持应在目标版本核验。