Разбиение итогов на подгруппы
Задача
Вы хотите получить итоговую информацию для каждой подгруппы множества строк, а не одно общее итоговое значение.
Решение
Используйте инструкцию GROUP BY для распределения строк по группам.
Обсуждение
Пока что рассматривались запросы, формирующие суммарные значения для всех строк результирующего множества. Например, следующий запрос вычисляет количество ежедневных записей таблицы driver_log, то есть общее количество дней, когда водители были в дороге:
mysql> SELECT COUNT(*) FROM driver_log;
+------------+
| COUNT(*) |
+------------+
| 10 |
+------------+
Но иногда необходимо разбить множество строк на подгруппы и проводить суммирование в каждой группе. Для этого используются агрегирующие функции в сочетании с инструкцией GROUP BY. Чтобы вычислить количестводней, проведенных за рулем каждым водителем, сгруппируйте строки по имени водителя, сосчитайте количество строк в каждой группе и выведите имена вместе со счетчиками:
mysql> SELECT name, COUNT(name) FROM driver_log GROUP BY name;
+-------+-----------------+
| name | COUNT(name) |
+-------+-----------------+
| Ben | 3 |
| Henry | 5 |
| Suz i | 2 |
+-------+------------------+
Данный запрос суммирует тот же столбец, который группируется (name), но это совсем необязательно. Предположим, вам нужно быстро извлечь из таблицы driver_log следующую информацию: общее количество пройденных миль и средний дневной пробег для каждого водителя. Для группировки строк снова будем использовать столбец name, но суммирующие функции теперь будут работать со значениями miles:
mysql> SELECT name,
-> SUM(miles) AS 'total miles',
-> AVG(miles) AS 'miles per day'
-> FROM driver_log GROUP BY name;
+-------+-------------+----------------+
| name | total miles | miles per day |
+-------+-------------+----------------+
| Ben | 362 | 120.6667 |
| Henry | 911 | 182.2000 |
| Suzi | 893 | 446.5000 |
+-------+-------------+----------------+
Используйте столько столбцов группировки, сколько требуется для достижения необходимой детальности итоговой информации. Следующий запрос выводит грубую оценку, вычисляя, сколько сообщений было отправлено каждым пользователем из таблицы mail:
mysql> SELECT srcuser, COUNT(*) FROM mail
-> GROUP BY srcuser;
+---------+-------------+
| srcuser | COUNT(*) |
+---------+-------------+
| barb | 3 |
| gene | 6 |
| phil | 5 |
| tricia | 2 |
+---------+-------------+
Чтобы получить более конкретные данные и узнать, сколько сообщений каждый пользователь отправил с каждого хоста, используйте группировку по двум столбцам. В результате получатся вложенные группы (группы внутри групп):
mysql> SELECT srcuser, srchost, COUNT(*) FROM mail
-> GROUP BY srcuser, srchost;
+---------+---------+-------------+
| srcuser | srchost | COUNT(*) |
+---------+---------+-------------+
| barb | saturn | 2 |
| barb | venus | 1 |
| gene | mars | 2 |
| gene | saturn | 2 |
| gene | venus | 2 |
| phil | mars | 3 |
| phil | venus | 2 |
| tricia | mars | 1 |
| tricia | saturn | 1 |
+--------+-----------+-------------+
В примерах данного раздела для получения групповых итогов использовались функции COUNT(), SUM() и AVG(). Можно применять и функции MIN() и MAX(). Будучи использованными в инструкции GROUP BY, они выводят наименьшее и наибольшее значения в группе. Сгруппируем строки таблицы mail по отправителям сообщений и будем выводить для каждой из них размер максимального сообщения и дату последнего сообщения:
mysql> SELECT srcuser, MAX(size), MAX(t) FROM mail GROUP BY srcuser;
+---------+------------+--------------------------+
| srcuser | MAX(size) | MAX(t) |
+---------+------------+--------------------------+
| barb | 98151 | 2001-05-14 14:42:21 |
| gene | 998532 | 2001-05-19 22:21:51 |
| phil | 10294 | 2001-05-17 12:49:23 |
| tricia | 2394482 | 2001-05-14 17:03:01 |
+---------+------------+--------------------------+
Можно выполнить группировку по нескольким столбцам и вывести наибольшее значение для каждой комбинации значений этих столбцов. Следующий запрос находит размер самого длинного сообщения среди сообщений каждой пары отправитель-получатель (srcuser-dstuser) из таблицы mail:
mysql> SELECT srcuser, dstuser, MAX(size) FROM mail GROUP BY srcuser, dstuser;
+---------+---------+------------+
| srcuser | dstuser | MAX(size) |
+---------+---------+------------+
| barb | barb | 98151 |
| barb | tricia | 58274 |
| gene | barb | 2291 |
| gene | gene | 23992 |
| gene | tricia | 998532 |
| phil | barb | 10294 |
| phil | phil | 1048 |
| phil | tricia | 5781 |
| tricia | gene | 194925 |
| tricia | phil | 2394482 |
+--------+-----------+------------+
Используя агрегирующие функции при получении итоговых значений для групп, можно попасть в ловушку. Предположим, вы хотите найти самую длинную поездку каждого водителя в таблице driver_log. Выполняем такой запрос:
mysql> SELECT name, MAX(miles) AS 'longest trip'
-> FROM driver_log GROUP BY name;
+-------+---------------+
| name | longest trip |
+-------+---------------+
| Ben | 152 |
| Henry | 300 |
| Suzi | 502 |
+-------+---------------+
Но что делать, если вы хотите вывести и дату совершения этой поездки? Можно ли просто добавить столбец trav_date в список вывода? К сожалению, нет:
mysql> SELECT name, trav_date, MAX(miles) AS 'longest trip'
-> FROM driver_log GROUP BY name;
+-------+---------------+--------------+
| name | trav_date | longest trip |
+-------+---------------+--------------+
| Ben | 2001-11-30 | 152 |
| Henry | 2001-11-29 | 300 |
| Suzi | 2001-11-29 | 502 |
+--------+--------------+---------------+
Запрос возвращает результат, но если вы сравните его с данными таблицы (приведенными ниже), то обнаружите, что даты для имен Ben и Henry корректны, а для Suzi – нет:
+--------+-------+--------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+--------------+-------+
| 1 | Ben | 2001-11-30 | 152 | ← самая длинная поездка Ben'а
| 2 | Suzi | 2001-11-29 | 391 |
| 3 | Henry | 2001-11-29 | 300 | ← самая длинная поездка Henry
| 4 | Henry | 2001-11-27 | 96 |
| 5 | Ben | 2001-11-29 | 131 |
| 6 | Henry | 2001-11-26 | 115 |
| 7 | Suzi | 2001-12-02 | 502 | ← самая длинная поездка Suzi
| 8 | Henry | 2001-12-01 | 197 |
| 9 | Ben | 2001-12-02 | 79 |
| 10 | Henry | 2001-11-30 | 203 |
+-------+---------+--------------+-------+
В чем же причина? Почему запрос выводит неправильный результат? Дело в том, что когда вы включаете в запрос инструкцию GROUP BY, выбирать можно только значения группируемых столбцов или вычисленные для них итоговые значения. Если вы выводите какие-то дополнительные столбцы, они никак не привязаны к группируемым столбцам, и выводимые для них значения никак не задаются. (Похоже, что в только что приведенном запросе СУБД MySQL просто взяла первую дату для каждого водителя, не заботясь о том, является ли она датой самой длинной поездки.)
Общим решением проблем вывода содержимого строк, связанных с минимальным или максимальным значением, является использование соединения (см. главу 12). Если вы не хотите забегать вперед или не хотите использовать другую таблицу, то можете обратиться к описанному ранее приему MAX-CONCAT. Запрос получается не очень красивым, но выводит корректный результат:
mysql> SELECT name,
-> SUBSTRING(MAX(CONCAT(LPAD(miles,3,' '), trav_date)),4) AS date,
-> LEFT(MAX(CONCAT(LPAD(miles,3,' '), trav_date)),3) AS 'longest trip'
-> FROM driver_log GROUP BY name;
+-------+--------------+---------------+
| name | date | longest trip |
+-------+--------------+---------------+
| Ben | 2001-11-30 | 152 |
| Henry | 2001-11-29 | 300 |
| Suzi | 2001-12-02 | 502 |
+-------+--------------+----------------+