MySQL / 13. Статистические методы

Подсчет отсутствующих значений

Задача
Набор наблюдений не полон. Вы хотите понять – насколько.

Решение
Сосчитайте количество значений NULL в множестве.

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

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

mysql> SELECT subject, score FROM t ORDER BY subject;

+--------+-------+
| subject | score |
+--------+-------+
| 1          | 38      |
| 2          | NULL |
| 3          | 47      |
| 4          | NULL |
| 5          | 37      |
| 6          | 45      |
| 7          | 54      |
| 8          | NULL |
| 9          | 40      |
| 10        | 49      |
+--------+-------+

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

mysql> SELECT COUNT(*) AS 'n (total)',
-> COUNT(score) AS 'n (non-missing)',
-> COUNT(*) - COUNT(score) AS 'n (missing)',
-> ((COUNT(*) - COUNT(score)) * 100) / COUNT(*) AS '% missing'
-> FROM t;

+---------+-------------------+-------------+-----------+
| n (total) | n (non-missing) | n (missing) | % missing |
+---------+-------------------+-------------+-----------+
| 10         | 7                       | 3                 | 30.00        |
+---------+-------------------+-------------+-----------+

В качестве альтернативы вычислению количества значений NULL как разности двух счетчиков можно получить его напрямую, используя SUM(ISNULL(score)). Функция ISNULL() возвращает 1, если ее аргумент – NULL, и ноль в противном случае:

mysql> SELECT COUNT(*) AS 'n (total)',
-> COUNT(score) AS 'n (non-missing)',
-> SUM(ISNULL(score)) AS 'n (missing)',
-> (SUM(ISNULL(score)) * 100) / COUNT(*) AS '% missing'
-> FROM t;

+----------+------------------+-------------+-----------+
| n (total) | n (non-missing) | n (missing) | % missing |
+----------+------------------+-------------+-----------+
| 10          | 7                       | 3                | 30.00        |
+----------+------------------+-------------+-----------+

Если значения разбиты на группы, то вхождения значений NULL могут оцениваться на уровне группы. Предположим, что t содержит баллы по предметам, распределенным по двум показателям, каждый из которых имеет два уровня:

mysql> SELECT subject, A, B, score FROM t ORDER BY subject;

+--------+---+--+-------+
| subject | A | B | score |
+--------+---+--+-------+
| 1          | 1 | 1 | 18      |
| 2          | 1 | 1 | NULL |
| 3          | 1 | 1 | 23      |
| 4          | 1 | 1 | 24      |
| 5          | 1 | 2 | 17      |
| 6          | 1 | 2 | 23      |
| 7          | 1 | 2 | 29      |
| 8          | 1 | 2 | 32      |
| 9          | 2 | 1 | 17      |
| 10        | 2 | 1 | NULL |
| 11        | 2 | 1 | NULL |
| 12        | 2 | 1 | 25      |
| 13        | 2 | 2 | NULL |
| 14        | 2 | 2 | 33      |
| 15        | 2 | 2 | 34      |
| 16        | 2 | 2 | 37      |
+--------+--+--+-------+

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

mysql> SELECT A, B, COUNT(*) AS 'n (total)',
-> COUNT(score) AS 'n (non-missing)',
-> COUNT(*) - COUNT(score) AS 'n (missing)',
-> ((COUNT(*) - COUNT(score)) * 100) / COUNT(*) AS '% missing'
-> FROM t
-> GROUP BY A, B;

+--+--+----------+-------------------+------------+------------+
| A | B | n (total) | n (non-missing) | n (missing) | % missing |
+--+--+----------+------------------+-------------+------------+
| 1 | 1 | 4            | 3                       | 1                | 25.00        |
| 1 | 2 | 4            | 4                       | 0                | 0.00          |
| 2 | 1 | 4            | 2                       | 2                | 50.00        |
| 2 | 2 | 4            | 3                       | 1                | 25.00        |
+--+--+----------+------------------+-------------+------------+

Статьи по MySQL на эту тему:

Вычисление линейной регрессии и коэффициентов корреляции
Генерация случайных чисел
Присваивание рангов
Рандомизация набора строк
Случайный выбор из набора строк

Вернуться в раздел: MySQL / 13. Статистические методы