SQL 聚合
聚合是把多行输入算成一个汇总值,比如行数、总额或平均值;配合 GROUP BY,就是每组算出一个汇总值。
聚合查询的错误往往不报错,只是数字不对:少算了 NULL,多算了连接后重复的明细,或者把「没有数据」显示成了空白。写聚合查询时,先确定每个输出行代表什么粒度,再区分过滤发生在分组之前还是之后,并检查 NULL 和空输入会得到什么。下文以 MySQL 8.0 为准。
1. 面试核心
先记住这几条,再读正文COUNT(*)统计行数,COUNT(column)只统计该列非空的行数。卡 · 面试核心- 非聚合的输出列必须由分组键唯一确定,MySQL 8.0 默认的
ONLY_FULL_GROUP_BY会拒绝不满足的查询。卡 · 分组列与函数依赖 WHERE在分组前过滤输入行,HAVING在聚合后过滤组。卡 · 分组前后过滤- 没有
GROUP BY时,空输入也返回一行,COUNT为 0,SUM为 NULL;有GROUP BY时空输入返回空结果集。卡 · 分组前后过滤 COUNT(DISTINCT column)统计去重后的非空值个数;用COUNT做条件计数时,未命中的分支要返回 NULL 而不是 0。卡 · 条件聚合与去重计数- 「全部满足」可以转成不满足条件的记录数为 0,同时明确 NULL、没有记录和缺课各算什么。卡 · 全部课程及格的学生
2. 分组与 NULL
GROUP BY 决定结果的粒度:分组键相同的输入行归为一组,每组输出一行。下面按团队统计销售记录,其中一条金额是 NULL:
CREATE TABLE sales (
id INTEGER PRIMARY KEY, team VARCHAR(20), amount INTEGER
);
INSERT INTO sales VALUES
(1, 'A', 30), (2, 'A', NULL), (3, 'A', 70), (4, 'B', 20);
SELECT team, COUNT(*) AS rows_count,
COUNT(amount) AS known_count, SUM(amount) AS total
FROM sales GROUP BY team ORDER BY team;
A 组得到 (A,3,2,100),B 组得到 (B,1,1,20)。A 组有三行,COUNT(*) 数的是行,所以是 3;COUNT(amount) 和 SUM(amount) 都跳过金额为 NULL 的那一行,分别得到 2 和 100。AVG(amount) 同样忽略 NULL,A 组的平均值是 100 除以 2,等于 50,而不是除以 3。
NULL 出现在分组键里时,所有 NULL 键归入同一组。另外,分组不保证结果的顺序,需要固定顺序就像示例这样写 ORDER BY。
3. 分组列与函数依赖
分组后每组只输出一行,所以每个输出值都要有明确的来源。聚合函数从组内多行算出一个值,没有问题;非聚合列就不同了,它必须由分组键唯一确定,或者被过滤条件限定为单一值,否则组内有好几个值,数据库不知道该输出哪个。
SELECT id, team, SUM(amount) FROM sales GROUP BY id;
-- id 是主键,每组只有一行,因此 team 可由 id 确定。
-- 执行错误:team 无法唯一确定 id。
-- SELECT team, id, SUM(amount) FROM sales GROUP BY team;
第一条按主键 id 分组,每组只有一行,team 由 id 唯一确定,这种关系叫函数依赖,MySQL 8.0 能识别并允许这个查询。第二条按 team 分组,A 组里有三个不同的 id,没有依据决定输出哪一个。
MySQL 8.0 默认开启 ONLY_FULL_GROUP_BY,会拒绝第二条查询。关闭这个模式后查询能执行,但服务器会从组内任选一个 id,结果不稳定,ORDER BY 也没法控制选中的是哪个值。需要组内某个确定的值时,用 MIN(id)、MAX(id) 这样的聚合函数明确写出来。
4. 分组前后过滤
WHERE 和 HAVING 都是过滤,区别在于执行时机:WHERE 在分组之前,决定哪些输入行参加统计;HAVING 在聚合之后,决定哪些组出现在结果里,所以它可以使用 SUM(amount) 这样的聚合结果。
SELECT team, SUM(amount) AS total FROM sales
WHERE amount >= 30
GROUP BY team
HAVING SUM(amount) >= 100
ORDER BY team;
WHERE 先去掉金额为 NULL 和 20 的行,A 组只剩 30 和 70,B 组没有行留下。A 组聚合后总额为 100,满足 HAVING,最终结果只有 (A,100)。
两种条件不能随意互换。如果去掉 WHERE amount >= 30,B 组的 20 也会参与统计,B 组以总额 20 被 HAVING 过滤掉;结果碰巧相同,但参与统计的数据已经变了,换一组数据结论就可能不同。
5. 空输入与空值
没有 GROUP BY 时,整个输入被视为一组,所以即使输入为空,也会返回一行聚合结果。有 GROUP BY 时则不同:
SELECT COUNT(*) AS n, SUM(amount) AS total
FROM sales WHERE id < 0;
SELECT team, COUNT(*) FROM sales WHERE id < 0 GROUP BY team;
第一条返回 (0,NULL):没有行可数,COUNT 是 0;没有值可加,SUM 是 NULL 而不是 0。第二条返回空结果集,因为没有输入行,也就产生不了任何分组键,自然没有组可以输出。
需要把「没有金额」显示为 0 时,可以写 COALESCE(SUM(amount), 0)。但要注意,它会把「没有记录」和「金额全为 NULL」都变成 0,未知金额是否也该算 0,要由业务来定义。
6. 条件聚合与去重计数
条件聚合是在聚合函数里放一个 CASE,只让满足条件的行参与统计,这样一次分组就能算出多个不同条件的统计值:
SELECT team,
SUM(CASE WHEN amount >= 50 THEN 1 ELSE 0 END) AS high_count,
COUNT(DISTINCT amount) AS distinct_amounts
FROM sales GROUP BY team ORDER BY team;
结果为 (A,1,2)、(B,0,1)。CASE 把金额不低于 50 的行记为 1,其余记为 0,SUM 累加起来就是命中的行数:A 组只有 70 命中,B 组没有。金额为 NULL 的行在 amount >= 50 上得到 UNKNOWN,走 ELSE 分支记为 0。COUNT(DISTINCT amount) 对非空金额去重,A 组是 30 和 70 两个值。
同样的写法换成 COUNT 就错了:COUNT(CASE WHEN amount>=50 THEN 1 ELSE 0 END) 会把所有行都算进去,因为 0 也是非空值,COUNT 只跳过 NULL。用 COUNT 做条件计数时,未命中的分支要返回 NULL,也就是去掉 ELSE 0。
6.1 全部课程及格的学生
查询「每门课程都不低于 60 分的学生」,可以转成按学生分组,筛出不满足及格条件的记录数为 0 的组。不能先用 WHERE score >= 60 删除不及格记录,否则一个有不及格课程的学生也可能凭剩下的及格记录入选。
先约定这个示例的含义:「每门课程」指该学生已经记录的课程,学生至少有一条成绩记录,NULL 表示成绩未知,不能证明及格。每个学生每门课只有一行;重修取哪次成绩、是否必须修齐指定课程,需要另外确认。下面的表结构和这些规则都是示例约定:
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL
);
CREATE TABLE course_scores (
student_id INT NOT NULL,
course_id INT NOT NULL,
score INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id)
);
INSERT INTO students VALUES
(1,'小林'), (2,'小周'), (3,'小李'), (4,'小王'),
(5,'小林'), (6,'小赵'), (7,'小陈');
INSERT INTO course_scores VALUES
(1,101,60), (1,102,90),
(2,101,59), (2,102,95),
(3,101,70), (3,102,NULL),
(5,101,80), (6,101,NULL), (7,101,60);
按学生身份分组,保留姓名用于输出:
SELECT s.id, s.name
FROM students AS s
JOIN course_scores AS c ON c.student_id = s.id
GROUP BY s.id, s.name
HAVING SUM(CASE WHEN c.score >= 60 THEN 0 ELSE 1 END) = 0
ORDER BY s.id;
结果是 (1,小林)、(5,小林)、(7,小陈)。CASE 只在 score >= 60 为 TRUE 时返回 0;低于 60 或为 NULL 都走 ELSE,记为一条不满足条件的记录。HAVING 要求这类记录数为 0。查询中的内连接使没有成绩记录的小王不产生分组。
输出 id 是为了区分两个同名学生;只要姓名时,可以从 SELECT 列表中去掉 s.id,分组仍按学生身份进行。不能只按姓名分组,否则不同学生的成绩会混在一起。小赵只有 NULL,不能入选;小李虽然有 70 分,也因另一门成绩未知而不能入选。小陈的 60 分恰好满足边界。
这里也不能直接用 MIN(score) >= 60 代替:MIN 会忽略 NULL,小李的最小已知成绩为 70,反而被选中。把条件写成 CASE WHEN score < 60 THEN 1 ELSE 0 END 也不够,因为 NULL 与 60 比较得到 UNKNOWN,会走 ELSE 0,漏掉未知成绩。
同一个要求还可以表达为「存在成绩记录,并且不存在低于 60 或为 NULL 的成绩」,见 SQL 连接的存在性与反连接。只写「不存在坏成绩」而不检查至少有一条成绩,会让没有成绩的学生也满足条件。
学生 5 只记录了课程 101,因此按本例约定可以入选。如果题目要求「指定的每一门必修课都及格」,就必须有必修课集合或选课关系,并检查每门要求的课都有及格成绩。仅统计已有记录中的坏成绩无法识别缺课;例如要求同时完成 101、102 时,上面的结果应只剩学生 1。
7. 使用边界
聚合和连接一起用时,最容易算错粒度。LEFT JOIN 会给没有匹配的左表行补一行右表列全为 NULL 的记录,这时 COUNT(*) 会把它算成 1。要统计每个左表行匹配到多少右表记录,应该数右表的主键,例如 COUNT(o.id),补出来的空行主键是 NULL,不会被计入,没有匹配的左表行就得到 0。
另一个问题是明细放大:一行左表记录连接到多行右表记录后,左表上的金额会重复出现,直接 SUM 就会重复累计。补空和放大的机制见 SQL 连接。
8. 面试题卡片
9. 参考资料
- MySQL:Aggregate Functions
- MySQL:GROUP BY Handling
- MySQL 8.0:Flow Control Functions
- MySQL 8.0:Subqueries with EXISTS or NOT EXISTS
来源:Coding 101 原文 · 免费阅读,引用请保留出处。内容使用说明