跳到主要内容
约 10 分钟5 张卡

SQL 聚合

聚合是把多行输入算成一个汇总值,比如行数、总额或平均值;配合 GROUP BY,就是每组算出一个汇总值。

聚合查询的错误往往不报错,只是数字不对:少算了 NULL,多算了连接后重复的明细,或者把「没有数据」显示成了空白。写聚合查询时,先确定每个输出行代表什么粒度,再区分过滤发生在分组之前还是之后,并检查 NULL 和空输入会得到什么。下文以 MySQL 8.0 为准。

1. 面试核心​

先记住这几条,再读正文

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. 参考资料​

来源:Coding 101 原文 · 免费阅读,引用请保留出处。内容使用说明