奇怪的MySQL AVG()异常NULL值 [英] Strange MySQL AVG() anomaly NULL values

问题描述

我在做什么:

create table sample (id INT(10) PRIMARY KEY AUTO_INCREMENT,name varchar(255),marks INT(10));

insert into sample (name,marks) VALUES('sam',10);
insert into sample (name,marks) VALUES('sam',20);
insert into sample (name,marks) VALUES('sam',NULL);
insert into sample (name,marks) VALUES('sam',NULL);
insert into sample (name,marks) VALUES('sam',30);

select AVG(marks) from sample GROUP BY(name);

我期望的输出:

AVG =(10 + 20 + 30)/5 = 12

AVG = (10+20+30)/5 = 12

MYSQL的输出:

AVG =(10 + 20 + 30)/3 = 20

AVG = (10+20+30)/3 = 20

理想情况下，我想要的是MYSQL应该获得5行的总和并除以5，但是它只能除以3(非NULL行)

Ideally what i wanted is that MYSQL should get the sum of 5 rows and divide it by 5 , but it only divides by 3 (the non-NULL rows)

为什么会发生这种情况，我该怎么做才能获得正确的AVG(即60/5)? PS:我不能使标记字段NOT NULL，在我的数据库设计中，标记字段允许为NULL.

Why does this occur and what can i do to get the correct AVG ie 60/5 ? PS: I cannot make the marks field NOT NULL , in my db design the marks field is allowed to be NULL.

谢谢

推荐答案

这是正确的行为，因为NULL与数字0不同.

This is the correct behavior, because NULL is not the same as the number 0.

从概念上讲， NULL 表示遗漏的未知值"，并且与其他值的处理方式有所不同.这就是为什么集合函数像 AVG() 忽略NULL s .

Conceptually, NULL means "a missing unknown value" and it is treated somewhat differently from other values. That is why aggregate functions like AVG() ignore NULLs.

AVG() 仅计算所有已知值的平均值. (=表示 not NULL =未知)

AVG() calculates the average over all known values only. (= that are not NULL = not unknown)

从 MySQL文档:

除非另有说明，否则组函数将忽略NULL值.

Unless otherwise stated, group functions ignore NULL values.

此外，请在.

要获得想要的东西，你可以做

To get what you want, you might do

SELECT AVG(IFNULL(marks, 0)) 
FROM sample 
GROUP BY name;

IFNULL() 如果值为NULL，则返回第二个计算参数，否则返回该值.

IFNULL() returns the second argument for calculations if the value is NULL or passes through the value otherwise.

关于NULL的概念还有更多常见的误解.在第"5.5.3节问题NULL" :

There are more common misunderstandings regarding the concept of NULL. These are also explained in Section "5.5.3 Problems with NULL" of the manual:

在SQL中，与任何其他值(甚至是NULL)相比，"NULL"值从不为真.除非文档中针对表达式所涉及的运算符和函数另有说明，否则包含NULL的表达式始终会产生NULL值.

即:NULL == 0会导致NULL而不是NULL是的.同样，'NULL == NULL'也会导致NULL而不是true.

In SQL, the `NULL` value is never true in comparison to any other value, even `NULL`. An expression that contains `NULL` always produces a `NULL` value unless otherwise indicated in the documentation for the operators and functions involved in the expression.

i.e.: `NULL == 0` results in NULL instead of `true`. Also `NULL == NULL` results in NULL, instead of true.

要搜索为"NULL"的列值，不能使用"expr = NULL"测试.要查找"NULL"值，必须使用"IS NULL"测试.

To search for column values that are `NULL`, you cannot use an `expr = NULL` test. To look for `NULL` values, you must use the `IS NULL` test.

当使用DISTINCT，GROUP BY或ORDER BY时，所有NULL值均视为相等.

When using `DISTINCT`, `GROUP BY`, or `ORDER BY`, all `NULL` values are regarded as equal.

使用ORDER BY时，将首先显示NULL值，或者如果您指定DESC以降序排序，则最后显示NULL值.

When using `ORDER BY`, `NULL` values are presented first, or last if you specify `DESC` to sort in descending order.

对于某些数据类型，MySQL特别处理NULL值.如果在"TIMESTAMP"列中插入"NULL"，则会插入当前日期和时间.

For some data types, MySQL handles NULL values specially. If you insert `NULL` into a `TIMESTAMP` column, the current date and time is inserted.

如果将"NULL"插入具有"AUTO_INCREMENT"属性的整数或浮点列中，则会插入序列中的下一个数字.

If you insert `NULL` into an integer or floating-point column that has the `AUTO_INCREMENT` attribute, the next number in the sequence is inserted.

定义了UNIQUE键的列仍可以包含多个NULL值.

A column that has a `UNIQUE` key defined can still contain multiple `NULL` values.

这篇关于奇怪的MySQL AVG()异常NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！