使用 CASE 进行 SQL 条件聚合

Q: SQL 中的条件聚合是什么？

它是以 CASE 表达式作为参数的聚合函数，例如 SUM(CASE WHEN amount >= 5 THEN amount ELSE 0 END)。每个指标只基于满足其条件的行计算，因此一个查询可以返回许多按不同条件过滤的指标。

Q: 为什么 COUNT(CASE WHEN ... ELSE 0 END) 会统计所有行？

COUNT 统计非 NULL 的值，而 0 不是 NULL。去掉 ELSE，让不匹配的行返回 NULL，或者改用 SUM(CASE WHEN ... THEN 1 ELSE 0 END)。

Q: 如何在 MySQL 中把行转换为列？

MySQL 没有 PIVOT 运算符。为每个输出列使用一个条件聚合，例如 COUNT(CASE WHEN rating = 'G' THEN 1 END) AS g，并按行维度分组。

第 4.4 课 · 阅读时间：约 8 分钟 · 练习题 ↓

条件聚合通过在聚合函数中放入 CASE 表达式，用一个 SQL 查询计算多个指标：SUM(CASE WHEN ... THEN ... END)。在本课中，你将学习条件求和、条件计数和条件平均值，了解会导致错误数字的 ELSE 用法，以及如何把行转换为列（pivot）。

使用 GROUP BY 时，每组只得到一个值：付款笔数或总金额。报表通常需要更多信息：对于每位员工，小额、中额和大额付款各有多少笔，每个区间带来了多少收入。没有条件聚合，你就得用不同的 WHERE 条件执行三个查询，再手动合并结果。

SQL 条件聚合：SUM、COUNT 和 AVG 中的 CASE 决定哪些行计入每个指标，并把行中的类别转换为列

核心思想

聚合函数会处理组中的每一行。其中的 CASE 决定每一行贡献什么值：

SUM(CASE WHEN condition THEN value ELSE 0 END)
COUNT(CASE WHEN condition THEN 1 END)
AVG(CASE WHEN condition THEN value END)

不满足条件的行对求和贡献 0，或者贡献 NULL，而聚合函数会忽略 NULL。这样，每个指标只基于“自己的”行计算，而所有指标都在对表的一次扫描中完成。

条件求和

SELECT staff_id,
       SUM(CASE WHEN amount < 2 THEN amount ELSE 0 END) AS low_total,
       SUM(CASE WHEN amount >= 2 AND amount < 6 THEN amount ELSE 0 END) AS medium_total,
       SUM(CASE WHEN amount >= 6 THEN amount ELSE 0 END) AS high_total,
       SUM(amount) AS total
FROM payment
GROUP BY staff_id;

结果：每位员工的小额、中额、大额付款收入以及总额。三个区间相加等于总额。

对于 SUM，ELSE 0 是正确的：区间之外的行不会增加任何值。如果没有 ELSE，没有任何匹配行的区间会返回 NULL 而不是 0。

条件计数

SELECT staff_id,
       COUNT(CASE WHEN amount < 2 THEN 1 END) AS low_payments,
       COUNT(CASE WHEN amount >= 2 AND amount < 6 THEN 1 END) AS medium_payments,
       COUNT(CASE WHEN amount >= 6 THEN 1 END) AS high_payments
FROM payment
GROUP BY staff_id;

结果：每位员工在每个区间内的付款笔数。

COUNT(expression) 统计非 NULL 值。当条件为假且没有 ELSE 时，CASE 返回 NULL，该行不会被计数。

常见错误：COUNT(CASE WHEN ... THEN 1 ELSE 0 END)。零不是 NULL，所以 COUNT 会统计每一行，三列显示的数字完全相同。如果你习惯写 ELSE 0，请改用 SUM：SUM(CASE WHEN ... THEN 1 ELSE 0 END)。

也可以直接对某一列计数。COUNT(return_date) 只统计已归还的租赁：

SELECT staff_id,
       COUNT(*) AS all_rentals,
       COUNT(return_date) AS returned,
       COUNT(CASE WHEN return_date IS NULL THEN 1 END) AS not_returned
FROM rental
GROUP BY staff_id;

结果：每位员工的全部租赁、已归还的租赁以及尚未归还的租赁。

条件平均值

SELECT staff_id,
       AVG(amount) AS avg_payment,
       AVG(CASE WHEN amount >= 5 THEN amount END) AS avg_big_payment
FROM payment
GROUP BY staff_id;

结果：每位员工所有付款的平均值，以及金额不低于 5 美元的付款的平均值。

对于 AVG，不要写 ELSE 0。平均值是总和除以值的个数；零会被当作值计入，从而拉低平均值。没有 ELSE 时，不匹配的行返回 NULL，会被跳过。

函数	不匹配的行应返回什么
`SUM(CASE ...)`	`ELSE 0`
`COUNT(CASE ...)`	不写 `ELSE`（`NULL`）
`AVG(CASE ...)`、`MIN`、`MAX`	不写 `ELSE`（`NULL`）

Pivot：行转列

Pivot（透视）把某个类别的值显示为单独的列。普通的 GROUP BY rental_rate, rating 会为每种组合返回一行。使用条件聚合后，每个分级都变成一列：

SELECT rental_rate,
       COUNT(CASE WHEN rating = 'G' THEN 1 END) AS g,
       COUNT(CASE WHEN rating = 'PG' THEN 1 END) AS pg,
       COUNT(CASE WHEN rating = 'PG-13' THEN 1 END) AS pg13,
       COUNT(CASE WHEN rating = 'R' THEN 1 END) AS r,
       COUNT(CASE WHEN rating = 'NC-17' THEN 1 END) AS nc17,
       COUNT(*) AS all_films
FROM film
GROUP BY rental_rate
ORDER BY rental_rate;

结果：三行，每种租赁价格（0.99、2.99、4.99 美元）一行，每个分级的电影数量位于单独的列中。

其他聚合函数也同样适用。按分级计算电影的平均时长，同样不写 ELSE：

SELECT rental_rate,
       ROUND(AVG(CASE WHEN rating = 'G' THEN length END), 1) AS g_avg_length,
       ROUND(AVG(CASE WHEN rating = 'R' THEN length END), 1) AS r_avg_length
FROM film
GROUP BY rental_rate
ORDER BY rental_rate;

由日期构建的按星期透视：

SELECT MONTH(rental_date) AS rental_month,
       SUM(CASE WHEN DAYOFWEEK(rental_date) IN (1, 7) THEN 1 ELSE 0 END) AS weekend_rentals,
       SUM(CASE WHEN DAYOFWEEK(rental_date) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) AS weekday_rentals
FROM rental
GROUP BY MONTH(rental_date)
ORDER BY rental_month;

结果：每个月周末和工作日的租赁数量。在 MySQL 中，DAYOFWEEK() 对星期日返回 1，对星期六返回 7。

有些数据库提供 PIVOT 运算符（SQL Server、Oracle），但条件聚合在所有数据库中都能使用，是可移植的选择。

占比与比率

两个条件聚合可以组合在一个表达式中：

SELECT customer_id,
       COUNT(*) AS payments,
       ROUND(100 * SUM(CASE WHEN amount >= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS big_payment_pct
FROM payment
GROUP BY customer_id
ORDER BY big_payment_pct DESC
LIMIT 10;

结果：金额不低于 5 美元的付款占比最高的 10 位客户。

FILTER 子句

PostgreSQL 和 SQLite（3.30+）支持更简短的标准语法 FILTER：

-- PostgreSQL, SQLite
SELECT customer_id,
       COUNT(*) AS all_payments,
       COUNT(*) FILTER (WHERE amount >= 5) AS big_payments,
       SUM(amount) FILTER (WHERE amount >= 5) AS big_total
FROM payment
GROUP BY customer_id;

它的含义与 COUNT(CASE WHEN amount >= 5 THEN 1 END) 相同。MySQL、MariaDB 和 SQL Server 不支持 FILTER，因此 CASE 仍是可移植的写法。

要点：

条件聚合就是内部带有 CASE 的聚合函数：一个查询、一次扫描得到多个指标。
求和用 SUM(CASE ... ELSE 0 END)；计数用不带 ELSE 的 COUNT(CASE ... END)。
COUNT(CASE ... ELSE 0 END) 会统计所有行：这是常见错误。
AVG(CASE ...) 不要写 ELSE，否则零会拉低平均值。
用 CASE 实现的 pivot 能把类别的值转换为列，适用于所有数据库。

常见问题

SQL 中的条件聚合是什么？

它是以 CASE 表达式作为参数的聚合函数，例如 SUM(CASE WHEN amount >= 5 THEN amount ELSE 0 END)。每个指标只基于满足其条件的行计算，因此一个查询可以返回许多按不同条件过滤的指标。

为什么 COUNT(CASE WHEN ... ELSE 0 END) 会统计所有行？

COUNT 统计非 NULL 的值，而 0 不是 NULL。去掉 ELSE，让不匹配的行返回 NULL，或者改用 SUM(CASE WHEN ... THEN 1 ELSE 0 END)。

如何在 MySQL 中把行转换为列？

MySQL 没有 PIVOT 运算符。为每个输出列使用一个条件聚合，例如 COUNT(CASE WHEN rating = 'G' THEN 1 END) AS g，并按行维度分组。