聚合、HAVING 与条件统计

本章目录

本章学习目标

区分行筛选和组筛选
正确理解 COUNT、SUM 与空组
用条件聚合表达指标的分子分母

分组改变结果粒度

GROUP BY 把行按键归入组，聚合函数从每组产生一个值。orders 按 user_id 分组后，结果变成每个订单用户一行；一个用户十笔订单也只输出一行。空 user_id 会归入同一个空键组，但不能据此认定这些订单来自同一个真实用户。按 country,channel 分组时，粒度是国家与渠道组合，报告标题与列名应体现这一点。

WHERE 先过滤来源行，再分组；HAVING 在聚合后筛选组。例如先筛选支付过的订单，再计算每用户净额，最后只保留净额超过一百的用户。把条件写错层级会改变业务问题：WHERE amount>100 是只累计大订单，HAVING SUM(amount)>100 是累计全部订单后找总额大的用户，两者并不等价。

逐步演算计数与均值

一组有金额十、二十、NULL 三行，COUNT(*) 为三，COUNT(amount) 为二，SUM(amount) 为三十，AVG(amount) 为十五。聚合函数通常忽略参数的空值，所以平均值分母不是总行数。AVG(COALESCE(amount,0)) 为十，对应另一种明确把未知金额当零的口径。不能为了让计算方便而悄悄切换分母。

COUNT(DISTINCT user_id) 统计已知的不同用户，忽略空用户键。没有任何输入行且不分组时，COUNT(*) 为零，而 SUM(amount) 为 NULL；有 GROUP BY 且没有输入行时，通常根本不输出组。想保留零订单用户需要从 users 出发进行外连接，这将在连接章实现；不能期望订单表自行生成从未出现的用户。

条件聚合让口径显式可见

SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) 数 paid 状态，SUM(CASE WHEN status IN ('paid','refunded') THEN amount-refund_amount ELSE 0 END) 计算支付净额。将多个指标放在同一次分组可以让它们共用同一来源范围，同时用 CASE 写清不同条件。MySQL 不支持其他方言常见的 FILTER (WHERE ...) 聚合语法，本课程统一用 CASE。

支付订单占比的分母如果是全部订单，则取消订单留在来源中，只在分子判断支付状态；如果 WHERE 提前排除取消订单，占比会被误算成百分之百。比率表达式使用 1.0 乘分子并除以 NULLIF(分母,0)，分母为零时得到未知而非报错。显示百分号与乘一百是展示工作，应与原始比例数值区分。

严格分组与诊断反例

SELECT user_id,order_date,SUM(amount) FROM orders GROUP BY user_id 在严格分组模式下不合法：一个用户可能有多个订单时间，数据库无法知道你要哪个。不能关掉 ONLY_FULL_GROUP_BY 让数据库任意选择。若要首次下单日，用 MIN(order_date)；若要最后订单的完整记录，需窗口排序或连接，不应拼凑 MAX(order_date) 与任意 order_id。

分组后连接维度表时检查维度键唯一，避免聚合结果再次放大。金额用 DECIMAL 有助于避免二进制浮点的微小误差，但依然需要处理退款口径和空值。核对时可以先求总体净额，再求分组净额之和，看是否守恒；如果某些组被 HAVING 排除，应明确整体与筛选后总体不是同一个范围。分组正确性来自定义，不来自输出整齐。

从结果到验证

阅读一段查询时，先写下一行结果代表什么，再列出来源表的一行代表什么。把过滤、连接、聚合和排序分别检查，比仅凭输出看起来合理更可靠。对于金额应同时保留订单数量和用户数量，以便发现连接引起的重复累计；对于比率应同时展示分子与分母，以便发现筛选范围不同。结果少并不等于正确，结果多也不等于错误，判断依据是预先确定的业务口径。

先用纸笔构造三个用户、两三个订单的小表，再用数据库运行对应查询。至少包含没有订单的用户、同一用户多笔订单、一个未知值，以及筛选后没有记录的情形。用这些反例解释每个子句造成的变化，再扩大到课程数据。未写 ORDER BY 的结果没有稳定顺序；需要核对输出时总要明确排序。课程默认 MySQL 8.4，开启严格分组规则，不要通过关闭规则掩盖数据粒度问题。

完成练习后，先自行解释结果再查看答案。记录的检查标准应是行数、键是否唯一、是否有空值、金额是否守恒，以及日期边界是否符合定义，而不只是查询没有语法错误。答案中的每个条件都应能追溯到题目口径；如果口径改变，先调整定义再改查询。这样形成的推理过程可以直接用于面试中解释思路和实际工作中复核分析报告。

完整示例与实际输出

SELECT user_id,COUNT(*) AS all_orders,
  SUM(CASE WHEN status IN ('paid','refunded') THEN 1 ELSE 0 END) AS paid_orders,
  SUM(CASE WHEN status IN ('paid','refunded') THEN amount-refund_amount ELSE 0 END) AS net_amount,
  1.0*SUM(CASE WHEN status IN ('paid','refunded') THEN 1 ELSE 0 END)/NULLIF(COUNT(*),0) AS paid_share
FROM orders GROUP BY user_id ORDER BY user_id;
SELECT user_id,SUM(amount-refund_amount) AS net_amount
FROM orders WHERE status IN ('paid','refunded')
GROUP BY user_id HAVING SUM(amount-refund_amount)>100 ORDER BY user_id;
SELECT COUNT(*) AS n,SUM(amount) AS total FROM orders WHERE 1=0;

实际运行通过Actual MySQL 8.4.11 execution; strict grouping mode.

user_id	all_orders	paid_orders	net_amount	paid_share
NULL	1	1	12.50	1.00000
1	3	3	144.00	1.00000
2	3	3	720.00	1.00000
3	3	2	25.00	0.66667
4	3	3	144.00	1.00000
5	3	3	720.00	1.00000
6	3	2	25.00	0.66667
7	3	3	120.00	1.00000
8	3	3	720.00	1.00000
9	3	2	18.75	0.66667
10	3	3	144.00	1.00000
11	3	3	600.00	1.00000
12	2	1	12.50	0.50000
13	2	2	96.00	1.00000
14	2	2	480.00	1.00000
15	2	2	25.00	1.00000
16	2	2	96.00	1.00000
17	2	2	480.00	1.00000
18	2	1	12.50	0.50000
19	2	2	96.00	1.00000
20	2	2	360.00	1.00000
21	2	1	12.50	0.50000
22	2	2	72.00	1.00000
23	2	2	480.00	1.00000
24	2	2	25.00	1.00000

user_id	net_amount
1	144.00
2	720.00
4	144.00
5	720.00
7	120.00
8	720.00
10	144.00
11	600.00
14	480.00
17	480.00
20	360.00
23	480.00

n	total
0	NULL

不应关闭；应确定 MIN、MAX 或窗口选择等明确语义。

两项动手任务

任务 1 · 按日期统计全部订单数、支付订单数与支付净额。

提示

DATE(order_date) 在分组中使用。

完整参考解答与检查方式

SELECT DATE(order_date) AS day,COUNT(*) AS all_orders,SUM(CASE WHEN status IN ('paid','refunded') THEN 1 ELSE 0 END) AS paid_orders,SUM(CASE WHEN status IN ('paid','refunded') THEN amount-refund_amount ELSE 0 END) AS net_amount FROM orders GROUP BY DATE(order_date) ORDER BY day; 日期缺席时不会自动补零。

任务 2 · 找出至少两笔已支付订单的已知用户，展示首次支付日。

提示

先过滤状态与空键。

完整参考解答与检查方式

SELECT user_id,COUNT(*) AS paid_orders,MIN(order_date) AS first_paid_at FROM orders WHERE status IN ('paid','refunded') AND user_id IS NOT NULL GROUP BY user_id HAVING COUNT(*)>=2 ORDER BY user_id; MIN 给出明确最早时间。

官方资料与掌握状态

完成两项任务后，按独立完成程度记录。状态会尽量保存在当前浏览器，可导出备份。

动手任务记录

记录两项任务的方法、检查结果和卡点

掌握状态与复习

关掉资料独立完成后记 A；提示后完成记 B；需要重学记 C。