查询基础（SELECT / JOIN / 聚合 / 排序） · 聚合与分组

AVG / SUM / MIN / MAX 在面对 NULL 时的行为？

How do AVG / SUM / MIN / MAX handle NULL values?

答案语言

考察要点

这道题面试官想看你是否精确理解 SQL 标准中聚合函数处理 NULL 值的核心规则，尤其是在 AVG 函数上的体现。这不仅考察你的基础知识是否扎实，也考察你对数据计算严谨性的关注，因为错误理解该规则会导致严重的统计偏差。

核心概念

SQL 标准规定，除了 COUNT(*) 之外，所有的聚合函数在计算时都会忽略 NULL 值。

我们通过一个具体的例子来理解：假设有一张 student_scores 表：

id	name	score
1	Alice	100
2	Bob	80
3	Carol	NULL
4	Dave	90
5	Eve	NULL

1. SUM(score) 计算非 NULL 值的总和。

计算过程: 100 + 80 + 90 = 270
结果: 270

2. MIN(score) / MAX(score) 计算非 NULL 值中的最小值/最大值。

MIN 计算过程: MIN(100, 80, 90) -> 80
MAX 计算过程: MAX(100, 80, 90) -> 100

3. AVG(score) 这是最容易出错的地方。AVG(col) 的内部计算公式是 SUM(col) / COUNT(col)。由于 SUM 和 COUNT(col) 都忽略 NULL，所以：

SUM(score): 270
COUNT(score): 3 (只计算了 Alice, Bob, Dave 的分数)
计算过程: 270 / 3 = 90
结果: 90

4. COUNT 的变体

COUNT(score): 计算 score 列中非 NULL 值的行数。结果为 3。
COUNT(*): 计算表中的总行数，无论任何列的值是否为 NULL。结果为 5。
COUNT(1): 效果与 COUNT(*) 相同，在大多数现代数据库（如 MySQL InnoDB）中，性能也几乎没有差异。它计算的是值为 1 这个常量表达式的行数，也就是所有行。

边界条件与差异：

空表或全为 NULL:
- 如果聚合的集合为空（例如，一个空表，或者 WHERE 条件过滤掉了所有行），SUM, AVG, MIN, MAX 均返回 NULL，而 COUNT 返回 0。
- 如果聚合的列值全部为 NULL（如上例中的 Carol 和 Eve 组成一个分组），SUM, AVG, MIN, MAX 同样返回 NULL，COUNT(col) 返回 0。
数据库兼容性:
- 此行为是 SQL-92 标准的一部分，因此在 MySQL, PostgreSQL, Oracle, SQL Server 等主流关系型数据库中表现一致。

常见低分回答

误认为 AVG 会将 NULL 当作 0 处理。这是一个非常普遍的错误。
- 错误计算: (100 + 80 + 0 + 90 + 0) / 5 = 270 / 5 = 54
- 正确计算: (100 + 80 + 90) / 3 = 90
- 面试官点评: 这个错误直接反映了候选人对聚合函数核心机制的理解偏差，是严重的扣分项。
混淆 COUNT(*) 和 COUNT(column)。
- 错误描述: "COUNT 都是用来计行数的，所以 COUNT(*) 和 COUNT(score) 结果应该差不多。"
- 正确区分: COUNT(*) 统计物理行数，COUNT(column) 统计指定列上非 NULL 的值的个数。在我们的例子中，前者是 5，后者是 3。
认为对全为 NULL 的列求 SUM 返回 0。
- 错误 SQL 及预期: SELECT SUM(score) FROM student_scores WHERE name IN ('Carol', 'Eve'); 错误地认为结果是 0。
- 正确结果: NULL。因为 SUM 忽略了所有 NULL 值后，发现没有可计算的数值，所以返回 NULL。
在 GROUP BY 场景下描述不清。当被问及分组聚合时，无法清晰说明如果一个分组内全是 NULL 会发生什么。正确的回答是，该分组依然会输出一行，但其聚合结果（SUM/AVG/MIN/MAX）为 NULL。

工程实践与优化

数据建模阶段: 在设计表结构时，应明确列是否允许为 NULL。如果一个字段（如：score）在业务逻辑上不应该为空，就应该定义为 NOT NULL DEFAULT 0。这可以从根本上避免聚合函数对 NULL 处理的歧义，保证数据一致性。
显式处理 NULL: 如果业务需要将 NULL 视为 0 来计算平均值，必须显式地使用函数进行转换。
sql
```
 1-- 将 NULL 替换为 0 后再计算平均值
 2-- COALESCE 是标准 SQL 函数，推荐使用
 3SELECT AVG(COALESCE(score, 0)) FROM student_scores;
 4-- 在 MySQL 中也可以用 IFNULL
 5SELECT AVG(IFNULL(score, 0)) FROM student_scores;
```
注意: 在 WHERE 子句中对列使用函数（如 WHERE COALESCE(score, 0) > 60）通常会导致索引失效，但在 SELECT 部分的聚合计算中，由于通常需要全表或索引扫描，性能影响相对较小。
执行计划分析:
- 对于 SELECT AVG(score) FROM student_scores;，EXPLAIN 通常会显示 type: ALL (全表扫描) 或 type: index (全索引扫描)。
- 如果 score 列上有索引，EXPLAIN 的 Extra 字段可能会显示 Using index，表示数据库只需扫描索引文件，无需回表查询数据行，这是最高效的聚合方式（覆盖索引）。
- 对于 MIN(indexed_col) 或 MAX(indexed_col)，如果该列是索引的最左前缀，优化器可以直接从索引的头部或尾部获取值，无需扫描，复杂度为 $O(1)$ 。

常见追问与延伸题目

追问：如果我想把 NULL 分数当成 0 分计入总平均分，SQL 该怎么写？
- 回答要点: 需要使用 COALESCE 或 IFNULL 函数。正确的写法是 SELECT AVG(COALESCE(score, 0)) FROM student_scores;。这会计算 (100 + 80 + 0 + 90 + 0) / 5 = 54。进一步解释，COALESCE(expression, ...) 返回参数列表中的第一个非 NULL 值，是跨数据库的标准用法，而 IFNULL(expr1, expr2) 是 MySQL 特有的，功能类似但只接受两个参数。
追问：COUNT(*)、COUNT(1) 和 COUNT(column) 的区别和性能差异？
- 回答要点:
  - COUNT(*): SQL 标准推荐的行数统计方式，统计所有行。
  - COUNT(1): 效果同 COUNT(*)，因为 1 是一个非空常量。在 MySQL 的 InnoDB 引擎中，优化器会将 COUNT(1) 自动优化为 COUNT(*)，所以没有性能差异。
  - COUNT(column): 只统计指定 column 中非 NULL 的行数。
  - 性能: COUNT(*) 和 COUNT(1) 性能基本一致。COUNT(column) 如果 column 是非索引列，性能会低于 COUNT(*)；如果 column 是索引，性能可能与 COUNT(*) 相当或更快（取决于索引类型和存储引擎）。在 MyISAM 引擎中，COUNT(*) 对整表计数非常快，因为它直接读取存储的行数，但带 WHERE 条件后就没这个优势了。InnoDB 则需要扫描。
追问：如果我用 SUM(score) / COUNT(*) 来计算平均分，和 AVG(score) 有什么区别？
- 回答要点: 这是考察你是否真正理解了 AVG 的核心。
  - AVG(score) 的分母是非 NULL 分数的数量 (COUNT(score))。
  - SUM(score) / COUNT(*) 的分母是总行数。
  - 在存在 NULL 值的情况下，COUNT(score) < COUNT(*)，因此 AVG(score) 的结果会大于 SUM(score) / COUNT(*)。这两种计算方式代表了不同的业务口径：前者是“有效分数的平均分”，后者是“将缺考（NULL）视为 0 分后，全体学生的平均分”（假设 SUM 对 NULL 的处理符合预期，但实际上 SUM 忽略 NULL，所以这种写法本身就是一种逻辑陷阱，正确的“视为0分”写法是 SUM(COALESCE(score,0)) / COUNT(*)）。