MySQL AVG()异常现象：NULL值的坑

在使用MySQL中，我们经常会使用到聚合函数进行数据的统计和计算，其中AVG()就是计算平均值的函数。然而，经常会遇到一些奇怪的现象，如计算结果偏小或偏大等，这些现象往往与NULL值有关。

阅读更多：MySQL 教程

NULL值的特殊处理

NULL值在MySQL中被视为一个特殊的值，它表示“无值”，不同于0或空字符串等具体的值。因此，在对包含NULL值的数据进行计算时，需要进行特殊的处理。对于聚合函数而言，需要注意以下两个问题：

1. AVG()函数如何处理NULL值

AVG()函数会忽略掉NULL值，并计算非NULL值的平均值，例如：

SELECT AVG(score) FROM student;

+------------+
| AVG(score) |
+------------+
|     85.50  |
+------------+

如果存在NULL值，则结果与上述查询结果相同。

2. AVG()函数的计算规则

AVG()函数计算平均值的规则是，将所有非NULL值的总和除以非NULL值的个数。例如：

SELECT AVG(score) FROM student WHERE id IN (1,2,3);

+------------+
| AVG(score) |
+------------+
|     90.00  |
+------------+

在上述查询中，求的是id为1、2、3的学生的平均成绩，即(90+80+100)/3 = 90。

计算结果偏小或偏大的问题

AVG()函数计算平均值的规则相当简单，然而在实际使用中，往往会遇到一些奇怪的现象，例如计算结果偏小或偏大等问题。

偏小问题

AVG()函数的计算规则会将所有非NULL值的总和除以非NULL值的个数，如果存在NULL值，则会忽略掉，从而影响计算结果。例如：

SELECT AVG(score) FROM student WHERE id IN (1,2,3,4,5,6,7,8,9,10);

+------------+
| AVG(score) |
+------------+
|     87.50  |
+------------+

在上述查询中，求的是id为1-10的学生的平均成绩，假设id为5的学生的成绩为NULL，则(90+80+100+75+NULL+85+90+70+80+85)/9 ≈ 79.44。然而，查询结果却为87.50。

这是因为MySQL在计算AVG()函数时，会优先计算非NULL值的总和和个数，然后再计算平均值。在上述查询中，非NULL值的总和为600，个数为7，因此平均值为87.50。如果想得到正确的平均值，需要先将NULL值替换为0再进行计算，例如：

SELECT AVG(IFNULL(score,0)) FROM student WHERE id IN (1,2,3,4,5,6,7,8,9,10);

+-------------------+
| AVG(IFNULL(score,0)) |
+-------------------+
|        79.44       |
+-------------------+

这里使用了IFNULL()函数将NULL值替换为0，然后再使用AVG()函数计算平均值，得到正确结果为79.44。

偏大问题

在某些情况下，AVG()函数的计算结果会偏大。例如：

SELECT AVG(score) FROM student WHERE score < 60;

+------------+
| AVG(score) |
+------------+
|     61.50  |
+------------+

在上述查询中，求的是成绩小于60分的学生的平均成绩，然而查询结果却为61.50。

这是因为MySQL在计算AVG()函数时，会将NULL值视为0。因此，在上述查询中，如果存在NULL值，则会被计算为0，从而导致计算结果偏大。为了得到正确的结果，需要将NULL值忽略掉，例如：

SELECT AVG(score) FROM student WHERE score < 60 AND score IS NOT NULL;

+------------+
| AVG(score) |
+------------+
|     55.00  |
+------------+

在上述查询中，使用了IS NOT NULL将NULL值排除在外，得到正确结果为55.00。

另外，AVG()函数的计算结果还会受到数据类型的影响。例如，在使用DECIMAL类型存储小数时，需要注意小数点后的位数，否则计算结果可能会偏大或偏小。

总结

在使用AVG()函数进行平均值计算时，需要注意以下几点：

1. AVG()函数会忽略掉NULL值，可以使用IFNULL()函数将NULL值替换为0再进行计算；
2. AVG()函数的计算结果会收到非NULL值和NULL值的影响，需要根据实际情况进行处理；
3. AVG()函数的计算结果可能会受到数据类型的影响，需要注意数据类型的选择和小数位数的设置。