MySQL / 7. Формирование итогов

Группирование по результатам выражения

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

</>Решение
Поместите выражение в инструкцию GROUP BY. В ранних версиях MySQL, не поддерживающих выражения в GROUP BY, используйте обходной маневр.

Обсуждение
Как и ORDER BY, инструкция GROUP BY может ссылаться на выражения начиная с версии MySQL 3.23.2. То есть можно использовать вычисления как основу для группирования. Например, чтобы вывести распределение длин названий штатов, выполните группирование по LENGTH(name):

mysql> SELECT LENGTH(name), COUNT(*)
-> FROM states GROUP BY LENGTH(name);

+------------------+-------------+
| LENGTH(name) | COUNT(*) |
+------------------+-------------+
| 4                       | 3               |
| 5                       | 3               |
| 6                       | 5               |
| 7                       | 8               |
| 8                       | 12             |
| 9                       | 4               |
| 10                     | 4               |
| 11                     | 2               |
| 12                     | 4               |
| 13                     | 3               |
| 14                     | 2               |
+------------------+-------------+

В версиях до MySQL 3.23.2 выражения в инструкции GROUP BY не поддерживались, так что такой запрос не выполнился бы. Было показано, как обойти это ограничение для ORDER BY; то же самое можно сделать и для GROUP BY. Можно указать псевдоним выражения в списке столбцов вывода и сослаться в инструкции GROUP BY на этот псевдоним:

mysql> SELECT LENGTH(name) AS len, COUNT(*)
-> FROM states GROUP BY len;

+------+----------+
| len | COUNT(*) |
+----+-------------+
| 4    | 3               |
| 5    | 3               |
| 6    | 5               |
| 7    | 8               |
| 8    | 12             |
| 9    | 4               |
| 10  | 4               |
| 11  | 2               |
| 12  | 4               |
| 13  | 3               |
| 14  | 2               |
+----+------------+

Можно переписать инструкцию GROUP BY так, чтобы она ссылалась на столбец по его позиции в списке вывода:

mysql> SELECT LENGTH(name), COUNT(*)
-> FROM states GROUP BY 1;

+------------------+-------------+
| LENGTH(name) | COUNT(*) |
+------------------+-------------+
| 4                       | 3               |
| 5                       | 3               |
| 6                       | 5               |
| 7                       | 8               |
| 8                       | 12             |
| 9                       | 4               |
| 10                     | 4               |
| 11                     | 2               |
| 12                     | 4               |
| 13                     | 3               |
| 14                     | 2               |
+------------------+-------------+

Естественно, способы, предложенные в качестве альтернативы использованию выражений, будут работать и в MySQL версии 3.23.2 и выше, и некоторые пользователи считают их наиболее удачными.

При желании вы можете выполнять группирование по нескольким выражениям. Чтобы найти те дни года, в которые в Союз вступило более одного штата, группируйте строки по месяцу и дню статуса штата, а затем примените функции HAVING и COUNT() для поиска неуникальных комбинаций:

mysql> SELECT MONTHNAME(statehood), DAYOFMONTH(statehood), COUNT(*)
-> FROM states GROUP BY 1, 2 HAVING COUNT(*) > 1;

+------------------------------+--------------------------------+-------------+
| MONTHNAME(statehood) | DAYOFMONTH(statehood) | COUNT(*) |
+------------------------------+--------------------------------+-------------+
| February                           | 14                                       | 2                |
| June                                  | 1                                         | 2                |
| March                                | 1                                         | 2                |
| May                                   | 29                                       | 2                |
| November                         | 2                                         | 2                 |
+------------------------------+--------------------------------+--------------+

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

Итоги по датам
Классификация некатегориальных данных
Нахождение наибольшего и наименьшего из итоговых значений
Одновременная работа с итогами по группам и общим итогом
Управление порядком вывода итоговой информации

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