GROUP BY: статистика по группам

Бран · продюсер «Хроник Пограничья»

Команда спорит, под какую платформу делать летний ивент. Мобильщики кричат, что Android нас кормит. ПК-шники уверяют, что их игроки самые щедрые. Кто прав? Нужны цифры по каждой платформе.

В прошлом уроке мы сворачивали всю таблицу покупок в одну строку итогов. Бран хочет не один итог, а три: по Android, iOS и ПК. Можно написать три запроса с разными WHERE. А если платформ станет десять? А если Бран попросит то же по 40 гильдиям?

Стопки чеков

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

GROUP BY делает ровно это. Он раскладывает строки на группы с одинаковым значением в указанном столбце, а агрегаты считаются внутри каждой группы отдельно. На выходе одна строка на группу.

SELECT platform,
       count(*)        AS purchases,
       sum(amount_rub) AS revenue
FROM purchases
GROUP BY platform;

 platform | purchases | revenue
----------+-----------+-----------
 android  |       586 | 317275.00
 ios      |       388 | 227221.00
 pc       |       276 | 181937.00

1250 покупок легли в три стопки, и по каждой посчитались количество и сумма. Сложи суммы: 317 275 + 227 221 + 181 937 = 726 433, та же сырая выручка, что в прошлом уроке. Хорошая привычка: после группировки проверить, что части сходятся с целым.

Главное правило GROUP BY

После группировки от каждой стопки остаётся одна строка. Поэтому в SELECT можно ставить только две вещи:

1. столбцы, по которым группируем: у всех строк стопки они одинаковые;
2. агрегаты: они сворачивают стопку в одно значение.

Всё остальное база отвергнет. Например, amount_rub без агрегата:

SELECT platform, amount_rub, count(*)
FROM purchases
GROUP BY platform;

ERROR:  column "purchases.amount_rub" must appear in the GROUP BY clause or be used in an aggregate function

В стопке Android 586 разных сумм. Какую из них показать? База не угадывает и просит решить самому: min(amount_rub), max(amount_rub), sum(amount_rub) или группировать ещё и по сумме.

Персонажи по классам

Теперь вопрос Ильвы про баланс: сколько персонажей каждого класса и какой у них средний уровень.

SELECT class,
       count(*)             AS characters,
       round(avg(level), 1) AS avg_level
FROM characters
GROUP BY class
ORDER BY characters DESC;

 class   | characters | avg_level
---------+------------+-----------
 warrior |       1463 |      13.0
 mage    |       1278 |      13.0
 ranger  |       1107 |      13.4
 rogue   |        890 |      13.4
 priest  |        864 |      12.4

ORDER BY работает после группировки, поэтому сортировать можно по псевдониму агрегата, characters. Воинов больше всего, жрецов меньше всего и качаются они медленнее остальных. Вывод «жрецы слабые» пока делать рано, но вопрос для Ильвы уже есть.