Классификация некатегориальных данных

Задача
Вам необходимо получить итоговую информацию для набора значений, большая часть которых уникальна и не разбивается естественным образом на категории.

Решение
Для разбиения значений на категории используйте выражения.

Обсуждение
Группирование строк по результатам вычисления выражения часто используется для классификации значений, которые сами по себе не образуют никаких категорий. Такая возможность очень важна, так как инструкция GROUP BY отлично работает для столбцов с повторяющими значениями. Но, например, вам может понадобиться выполнить анализ численности населения, группируя записи таблицы states по значениям столбца pop. Получитсяне очень хорошо, так как в столбце много разных значений. На самом деле они вообще все разные, как видно из запроса:

mysql> SELECT COUNT(pop), COUNT(DISTINCT pop) FROM states;

+---------------+-----------------------------+
| COUNT(pop) | COUNT(DISTINCT pop) |
+---------------+-----------------------------+
| 50 | 50 |
+---------------+-----------------------------+

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


Сначала определим диапазон значений численности населения:

mysql> SELECT MIN(pop), MAX(pop) FROM states;

+-----------+-------------+
| MIN(pop) | MAX(pop) |
+-----------+-------------+
| 453588 | 29760021 |
+-----------+-------------+

Из результата видно, что если разделить значения pop на пять миллионов, можно получить шесть категорий – вполне разумное количество. (Категории будут иметь диапазоны от 1 до 5 000 000; от 5 000 001 до 10 000 000; и т. д.)

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

mysql> SELECT FLOOR(pop/5000000) AS 'population (millions)',

-> COUNT(*) AS 'number of states'
-> FROM states GROUP BY 1;

+------------------------+---------------------+
| population (millions) | number of states |
+------------------------+---------------------+
| 0 | 35 |
| 1 | 8 |
| 2 | 4 |
| 3 | 2 |
| 5 | 1 |
+------------------------+---------------------+

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


Давайте попробуем умножить результаты функции FLOOR() на пять:

mysql> SELECT FLOOR(pop/5000000)*5 AS 'population (millions)',
-> COUNT(*) AS 'number of states'
-> FROM states GROUP BY 1;

+-----------------------+----------------------+
| population (millions) | number of states |
+-----------------------+----------------------+
| 0 | 35 |
| 5 | 8 |
| 10 | 4 |
| 15 | 2 |
| 25 | 1 |
+-----------------------+----------------------+

Все равно неправильно! Максимальное население штата равнялось 29 760 021, такое значение должно было попасть в категорию для 30, а не 25 миллионов. Проблема в том, что выражение, формирующее категории, группирует значения по нижней границе каждого интервала. Чтобы добиться группирования по верхней границе, применим один прием: для сопоставления значению x соответствующей категории длины n воспользуйтесь выражением:

FLOOR((x+(n-1))/n)
В итоге запрос будет таким:
mysql> SELECT FLOOR((pop+4999999)/5000000)*5 AS 'population (millions)',
-> COUNT(*) AS 'number of states'
-> FROM states GROUP BY 1;

+------------------------+---------------------+
| population (millions) | number of states |
+------------------------+---------------------+
| 5 | 35 |
| 10 | 8 |
| 15 | 4 |
| 20 | 2 |
| 30 | 1 |
+-----------------------+----------------------+

Как видите, население большей части штатов США не превышает пяти миллионов.
Данный метод можно применять к любым видам числовых значений.


Например, можно группировать записи таблицы mail в категории по 100 000 каждая:

mysql> SELECT FLOOR((size+99999)/100000) AS 'size (100KB)',
-> COUNT(*) AS 'number of messages'
-> FROM mail GROUP BY 1;

+---------------+-------------------------+
| size (100KB) | number of messages |
+---------------+-------------------------+
| 1 | 13 |
| 2 | 1 |
| 10 | 1 |
| 24 | 1 |
+--------------+-------------------------+

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

mysql> SELECT FLOOR(LOG10(pop)) AS 'log10(population)',
-> COUNT(*) AS 'number of states'-> FROM states GROUP BY 1;

+---------------------+---------------------+
| log10(population) | number of states |
+---------------------+---------------------+
| 5 | 7 |
| 6 | 36 |
| 7 | 7 |
+---------------------+---------------------+.



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

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

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