Итоги и значения NULL

Задача
Суммируя набор значений, среди которых могут быть и значения NULL, вы хотите понять, как интерпретировать полученные результаты.

Решение
Осознайте, каким образом агрегирующие функции обрабатывают значения NULL.

Обсуждение
Большинство агрегирующих функций игнорируют значения NULL. Предположим, у вас есть таблица expt, в которую записываются результаты тестов для испытуемых (subject), каждому из которых нужно пройти четыре теста (test); при этом указывается значение NULL, если результат (score) еще не получен:

mysql> SELECT subject, test, score FROM expt ORDER BY subject, test;

+---------+------+-------+
| subject | test | score |
+---------+-----+-------+
| Jane | A | 47 |
| Jane | B | 50 |
| Jane | C | NULL |
| Jane | D | NULL |
| Marvin | A | 52 |
| Marvin | B | 45 |
| Marvin | C | 53 |
| Marvin | D | NULL |
+--------+------+-------+

Если использовать инструкцию GROUP BY для группировки строк по имени испытуемых, то можно вычислить количество тестов, пройденных каждым из них, а также общий, средний, максимальный и минимальный результаты следующим образом:

mysql> SELECT subject,
-> COUNT(score) AS n,
-> SUM(score) AS total,
-> AVG(score) AS average,
-> MIN(score) AS lowest,
-> MAX(score) AS highest
-> FROM expt GROUP BY subject;

+---------+--+------+----------+--------+----------+
| subject | n | total | average | lowest | highest |
+---------+--+------+----------+--------+----------+
| Jane | 2 | 97 | 48.5000 | 47 | 50 |
| Marvin | 3 | 150 | 50.0000 | 45 | 53 |
+---------+--+------+----------+--------+----------+

Из результатов в столбце n (количество тестов) видно, что запрос обработал только пять значений.


Почему? Потому что в этом столбце выводятся только значения, соответствующие не-NULL-результатам тестов каждого испытуемого. Значения, представленные в других итоговых столбцах, также вычислены на основе только не-NULL-результатов тестов.

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

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

mysql> SELECT subject,
-> COUNT(score) AS n,
-> SUM(score) AS total,
-> AVG(score) AS average,
-> MIN(score) AS lowest,
-> MAX(score) AS highest
-> FROM expt WHERE score IS NULL GROUP BY subject;

+---------+--+------+----------+--------+----------+
| subject | n | total | average | lowest | highest |
+---------+--+------+---------+---------+----------+
| Jane | 0 | 0 | NULL | NULL | NULL |
| Marvin | 0 | 0 | NULL | NULL | NULL |
+--------+---+------+---------+---------+----------+

Даже в таких условиях агрегирующие функции возвращают наиболее разумное значение.


Количество тестов, пройденных каждым испытуемым, и его общий результат равны нулю, нули и выводятся. А вот AVG() возвращает NULL. Среднее значение – это отношение суммы значений к их количеству.

Если складывать нечего, вы имеете дело с отношением 0/0, которое не определено. Поэтому для AVG() разумнее всего вернуть NULL. Аналогично, функциям MIN() и MAX() не с чем работать, поэтому они возвращают NULL. Если вы не хотите, чтобы эти функции выводили NULL, используйте IFNULL() для сопоставления им соответствующих значений:

mysql> SELECT subject,
-> COUNT(score) AS n,
-> SUM(score) AS total,
-> IFNULL(AVG(score),0) AS average,
-> IFNULL(MIN(score),'Unknown') AS lowest,-> IFNULL(MAX(score),'Unknown') AS highest
-> FROM expt WHERE score IS NULL GROUP BY subject;

+---------+--+------+----------+-----------+------------+
| subject | n | total | average | lowest | highest |
+---------+--+------+----------+-----------+------------+
| Jane | 0 | 0 | 0 | Unknown | Unknown |
| Marvin | 0 | 0 | 0 | Unknown | Unknown |
+---------+--+------+----------+-----------+------------+

Функция COUNT() несколько отличается в своей трактовке значений NULL от остальных агрегирующих функций.


Как и другие агрегирующие функции, COUNT(выражение) считает только значения не-NULL, а COUNT(*) считает все строки независимо от их содержимого. Продемонстрируем разницу между двумя формами COUNT():

mysql> SELECT COUNT(*), COUNT(score) FROM expt;

+------------+------------------+
| COUNT(*) | COUNT(score) |
+------------+------------------+
| 8 | 5 |
+------------+------------------+

Теперь вы знаете, что в таблице expt восемь строк, но лишь в пяти из них заполнены значения score. Две формы COUNT() очень удобно использовать для подсчета недостающих значений – просто найдите разность значений:

mysql> SELECT COUNT(*) - COUNT(score) AS missing FROM expt;

+---------+
| missing |
+---------+
| 3 |
+---------+

Можно проводить подсчет недостающих и имеющихся значений и для подгрупп. Следующий запрос выполняет операцию для каждого испытуемого. Так вы можете оценить, как далеко продвинулся эксперимент:

mysql> SELECT subject,
-> COUNT(*) AS total,
-> COUNT(score) AS 'non-missing',
-> COUNT(*) - COUNT(score) AS missing
-> FROM expt GROUP BY subject;

+---------+------+--------------+---------+
| subject | total | non-missing | missing |
+---------+------+--------------+---------+
| Jane | 4 | 2 | 2 |
| Marvin | 4 | 3 | 1 |
+---------+------+--------------+---------+.



Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Выбор групп только с определенными характеристиками
Группирование по результатам выражения
Использование ключевого слова DISTINCT для удаления дубликатов
Итоги по датам
Классификация некатегориальных данных

Вернуться в раздел: MySQL / 7. Формирование итогов