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

Получение частотного распределения

Задача
Вы хотите узнать частоту вхождения каждого значения в таблицу.

Решение
Сгенерируйте частотное распределение, которое является сводной характеристикой содержимого вашего множества данных.

Обсуждение
Методы вычисления сводных характеристик группы часто используются для разбиения значений на категории согласно количеству их вхождений во множество, то есть для получения частотного распределения. Для таблицы testscore частотное распределение выглядит так:

mysql> SELECT score, COUNT(score) AS occurrence
-> FROM testscore GROUP BY score;

+-------+------------+
| score | occurrence |
+-------+------------+
| 4       | 2                |
| 5       | 1                |
| 6       | 4                |
| 7       | 4                |
| 8       | 2                |
| 9       | 5                |
| 10     | 2                |
+-------+------------+

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

mysql> SELECT @n := COUNT(score) FROM testscore;
mysql> SELECT score, (COUNT(score)*100)/@n AS percent
-> FROM testscore GROUP BY score;

+------+---------+
| score | percent |
+------+---------+
| 4       | 10         |
| 5       | 5           |
| 6       | 20         |
| 7       | 20         |
| 8       | 10         |
| 9       | 25         |
| 10     | 10         |
+------+---------+

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

Частотное распределение часто применяется при экспорте результатов в графическую программу. В отсутствие такой программы для визуального представления распределения вы можете сформировать простую ASCII-диаграмму и в MySQL. Например, чтобы вывести ASCII-гистограмму для счетчиков результатов тестов, преобразуйте счетчики в строки символов *:

mysql> SELECT score, REPEAT('*',COUNT(score)) AS occurrences
-> FROM testscore GROUP BY score;

+-------+-------------+
| score | occurrences |
+-------+-------------+
| 4       | **                |
| 5       | *                  |
| 6       | ****            |
| 7       | ****            |
| 8       | **                |
| 9       | *****          |
| 10     | **                |
+-------+-------------+

Для вывода диаграммы относительного частотного распределения используйте процентное соотношение:

mysql> SELECT @n := COUNT(score) FROM testscore;
mysql> SELECT score, REPEAT('*',(COUNT(score)*100)/@n) AS percent
-> FROM testscore GROUP BY score;

+------+-------------------------------------+
| score | percent                                      |
+------+-------------------------------------+
| 4       | **********                              |
| 5       | *****                                         |
| 6       | ********************           |
| 7       | ********************           |
| 8       | **********                               |
| 9       | ************************* |
| 10     | **********                               |
+------+--------------------------------------+

Конечно, ASCII-диаграммы далеки от совершенства, но они обеспечивают быстрый способ получения общей картины распределения наблюдений без дополнительного инструментария.

Если вы формируете частотное распределение для набора категорий, часть которых не присутствует в ваших наблюдениях, то такие категории не будут отражены в выводе. Для того чтобы принудительно отобразить каждую категорию, используйте справочную таблицу и левое объединение (метод обсуждался в рецепте 12.9). Для таблицы testscore можно было бы ввести диапазон баллов от 0 до 10, тогда справочная таблица должна была бы содержать все значения диапазона:

mysql> CREATE TABLE ref (score INT);
mysql> INSERT INTO ref (score)
-> VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

Теперь объединяем справочную таблицу с результатами тестов и формируем частотное распределение:

mysql> SELECT ref.score, COUNT(testscore.score) AS occurrences
-> FROM ref LEFT JOIN testscore ON ref.score = testscore.score
-> GROUP BY ref.score;

+-------+--------------+
| score | occurrences |
+-------+--------------+
| 0       | 0                  |
| 1       | 0                  |
| 2       | 0                  |
| 3       | 0                  |
| 4       | 2                  |
| 5       | 1                  |
| 6       | 4                  |
| 7       | 4                  |
| 8       | 2                  |
| 9       | 5                  |
| 10     | 2                  |
+-------+--------------+

аспределение содержит строки для результатов от 0 до 3, при том что ни один из них ранее не отображался в предыдущих частотных распределениях.

Тот же принцип используется и при получении относительных распределений частот:

mysql> SELECT @n := COUNT(score) FROM testscore;
mysql> SELECT ref.score, (COUNT(testscore.score)*100)/@n AS percent
-> FROM ref LEFT JOIN testscore ON ref.score = testscore.score
-> GROUP BY ref.score;

+------+---------+
| score | percent |
+------+---------+
| 0       | 0           |
| 1       | 0           |
| 2       | 0           |
| 3       | 0           |
| 4       | 10         |
| 5       | 5          |
| 6       | 20        |
| 7       | 20        |
| 8       | 10        |
| 9       | 25        |
| 10     | 10         |
+------+---------+

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

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

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