Странные значения AVG() аномалии MySQL() NULL

Что я делаю:

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

ВЫХОД MYSQL:

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

В идеале я хотел бы, чтобы MYSQL получал сумму из 5 строк и делил ее на 5, но делил ее только на 3 (строки, отличные от NULL)

Почему это происходит и что я могу сделать, чтобы получить правильный AVG, т.е. 60/5? PS: Я не могу сделать поле меток NOT NULL, в моем дизайне db поле меток должно быть NULL.

Спасибо

Ответ 1

Это правильное поведение, потому что NULL не совпадает с числом 0.

Концептуально, NULL означает "пропущенное неизвестное значение", и оно трактуется несколько иначе, чем другие значения. Вот почему агрегатные функции, такие как AVG() игнорируют NULL.

AVG() вычисляет среднее значение только по всем известным значениям. (= это не NULL = не неизвестно)

Из документов MySQL:

Если не указано иное, групповые функции игнорируют значения NULL.

Также ознакомьтесь с концепцией NULL в разделе "3.3.4.6 Работа со значениями NULL" руководства MySQL.

Чтобы получить то, что вы хотите, вы могли бы сделать

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

IFNULL() возвращает второй аргумент для вычислений, если значение равно NULL или проходит через значение в противном случае.

Есть более распространенные недоразумения относительно концепции NULL. Это также объясняется в разделе "5.5.3 Проблемы с NULL" руководства:

В SQL значение "NULL" никогда не является истинным по сравнению с любым другим значением, даже "NULL". Выражение, содержащее "NULL", всегда создает значение "NULL", если иное не указано в документации для операторов и функций, участвующих в выражении.

то есть: "NULL == 0" приводит к NULL вместо "true". Также 'NULL == NULL' приводит к NULL, а не к true.
Для поиска значений столбцов, которые имеют значение "NULL", вы не можете использовать тест "expr = NULL". Чтобы искать значения "NULL", вы должны использовать тест "IS NULL".
При использовании "DISTINCT", "GROUP BY" или "ORDER BY" все значения "NULL" считаются равными.
При использовании 'ORDER BY' значения 'NULL' представляются первыми или последними, если вы указываете 'DESC' для сортировки в порядке убывания.
Для некоторых типов данных MySQL обрабатывает значения NULL специально. Если вы вставите "NULL" в столбец "TIMESTAMP", будут вставлены текущие дата и время.
Если вы вставите 'NULL' в столбец с целыми числами или с плавающей запятой, который имеет атрибут 'AUTO_INCREMENT', вставляется следующий номер в последовательности.
Столбец, для которого определен ключ UNIQUE, может содержать несколько значений NULL.

Ответ 2

Try:

select avg(case marks when null then 0 else marks end) from sample group by name;

Или попробуйте и избегайте нулей в таблице;)

Ответ 3

Вместо этого вы можете сделать это:

SELECT SUM(marks) / COUNT(name)
FROM sample 
GROUP BY name;

Ответ 4

Это нормальное поведение, поскольку NULL не равно нулю. Как вы делаете среднее значение 5 + NULL? Поэтому MySQL берет только строки, которые можно усреднить.

Appart из правильных ответов, которые уже предоставили вам другие пользователи, вы также можете использовать функцию COALESCE, которая возвращает первое значение, отличное от NULL, указанное в списке, поэтому вы можете заменить NULL на тот, который вам нравится:

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