GROUP BY
Как получать сводные итоги по отдельным группам
Теория
После изучения агрегатных функций многие задаются вопросом: функции COUNT(), SUM() и AVG() нам уже знакомы. Они являются полезными инструментами, которые позволяют вычислить результат для набора данных и представить его в виде итоговой строки. Но как посчитать итог не по всей таблице сразу, а отдельно по городам, статусам, категориям или должностям? Иными словами, определить итоговые результаты по категориям, срезам или группам.
Для этого существует оператор группировки строк GROUP BY.
Он делит значения столбца на группы по какому-то признаку, а агрегатная функция считает итог внутри каждой такой группы.
Если написать запрос SELECT COUNT(*) FROM orders, мы получим одно общее количество заказов. Но если нужно понять, сколько заказов со статусом 'создан', сколько 'оплачен', сколько 'доставлен', такое вычисление не подойдет.
Здесь уже нужно дополнительно использовать GROUP BY.
Можно рассматривать это следующим образом: агрегатная функция определяет, какие именно данные нужно подсчитать, а GROUP BY указывает, в какие группы эти данные следует объединить для подсчёта.
Сделать предположение о том, что в SQL запросе требуется группировка можно по таким формулировкам:
• сколько пользователей в каждом городе (слово «в каждом» должно насторожить);
• сколько товаров в каждой категории;
• какая средняя зарплата по каждому отделу;
• сколько заказов у каждого статуса.
Главная мысль темы: без GROUP BY агрегатные функции считают результат в одну итоговую строку, а с GROUP BY — по одной строке на каждую группу.
Для этого существует оператор группировки строк GROUP BY.
Он делит значения столбца на группы по какому-то признаку, а агрегатная функция считает итог внутри каждой такой группы.
Если написать запрос SELECT COUNT(*) FROM orders, мы получим одно общее количество заказов. Но если нужно понять, сколько заказов со статусом 'создан', сколько 'оплачен', сколько 'доставлен', такое вычисление не подойдет.
Здесь уже нужно дополнительно использовать GROUP BY.
Можно рассматривать это следующим образом: агрегатная функция определяет, какие именно данные нужно подсчитать, а GROUP BY указывает, в какие группы эти данные следует объединить для подсчёта.
Сделать предположение о том, что в SQL запросе требуется группировка можно по таким формулировкам:
• сколько пользователей в каждом городе (слово «в каждом» должно насторожить);
• сколько товаров в каждой категории;
• какая средняя зарплата по каждому отделу;
• сколько заказов у каждого статуса.
Главная мысль темы: без GROUP BY агрегатные функции считают результат в одну итоговую строку, а с GROUP BY — по одной строке на каждую группу.
Синтаксис
SELECT column_name, -- выводим сгруппированный столбец
aggregate_function(other_column) -- считаем итоговое значение внутри каждой группы
FROM table_name -- указываем таблицу, из которой берём данные
GROUP BY column_name; -- собираем строки в группы по column_name
После SELECT часто указываются две части: столбец, по которому нужно разбить строки на группы, и агрегатную функцию, которая будет считать итог внутри каждой группы.
Дальше идёт FROM, а после него — GROUP BY с тем же столбцом, по которому нужно собрать одинаковые значения вместе. Да, чуть не упустили важный момент. Как формируются эти группы? Ответ: группы формируются по принципу идентичности значений. То есть все значения в столбце, которые совпадают, объединяются в одну группу, причём не просто объединяются, а именно группируются в одно значение.
Например, если написать GROUP BY city, то все пользователи из одного и того же города попадут в одну группу. После этого COUNT(*) сможет посчитать, сколько строк оказалось в каждой такой группе.
Важное правило: Если в SELECT вы выводите дополнительный столбец, а не агрегатную функцию, этот столбец должен участвовать в GROUP BY. Иначе база данных не поймёт, как именно собрать строки.
В PostgreSQL группировать можно не только по самому столбцу, но и по его псевдониму или по позиционному номеру из SELECT.
Например, если нужный столбец в запросе в SELECT стоит первым, можно написать GROUP BY 1. Но в учебных и рабочих запросах лучше писать явное имя столбца или понятный псевдоним, потому что такой код легче поддерживать.
Ещё один важный момент: NULL тоже может участвовать в группировке. Если в столбце есть несколько строк с NULL, они соберутся в одну отдельную группу.
После группировки результат можно отсортировать через ORDER BY.
Это удобно, когда нужно показать сначала самые крупные группы.
Дальше идёт FROM, а после него — GROUP BY с тем же столбцом, по которому нужно собрать одинаковые значения вместе. Да, чуть не упустили важный момент. Как формируются эти группы? Ответ: группы формируются по принципу идентичности значений. То есть все значения в столбце, которые совпадают, объединяются в одну группу, причём не просто объединяются, а именно группируются в одно значение.
Например, если написать GROUP BY city, то все пользователи из одного и того же города попадут в одну группу. После этого COUNT(*) сможет посчитать, сколько строк оказалось в каждой такой группе.
Важное правило: Если в SELECT вы выводите дополнительный столбец, а не агрегатную функцию, этот столбец должен участвовать в GROUP BY. Иначе база данных не поймёт, как именно собрать строки.
В PostgreSQL группировать можно не только по самому столбцу, но и по его псевдониму или по позиционному номеру из SELECT.
Например, если нужный столбец в запросе в SELECT стоит первым, можно написать GROUP BY 1. Но в учебных и рабочих запросах лучше писать явное имя столбца или понятный псевдоним, потому что такой код легче поддерживать.
Ещё один важный момент: NULL тоже может участвовать в группировке. Если в столбце есть несколько строк с NULL, они соберутся в одну отдельную группу.
После группировки результат можно отсортировать через ORDER BY.
Это удобно, когда нужно показать сначала самые крупные группы.
Примеры
1. Сколько работников на каждой должности
Строки таблицы workers сначала группируются по одинаковым должностям, а потом COUNT(*) считает, сколько работников оказалось в каждой группе. В результате мы видим не одно общее число по всей таблице, а отдельный итог по каждой должности.
🔄 Попробуйте изменить запрос:
- • Уберите ORDER BY и посмотрите, как изменится порядок вывода
- • Попробуйте определить, сколько сотрудников работают в каждом отделе
2. Средняя зарплата по отделам
В данном случае нас интересует не просто средняя зарплата по всей компании, а анализ по отделам. Для этого мы группируем данные по идентификатору отдела department_id и затем вычисляем среднее значение зарплаты для каждой группы.
🔄 Попробуйте изменить запрос:
- • Определите минимальную зарплату по каждому отделу вместо средней
- • Добавьте перед группировкой фильтр WHERE salary >= 70000 и сравните результат
3. Сколько пользователей в каждом городе
Если у пользователя город не указан, вместо NULL мы показываем понятную группу с названием 'город не указан'. Так результат более понятный.
🔄 Попробуйте изменить запрос:
- • Уберите функцию COALESCE и посмотрите, как будет выглядеть группа с NULL
- • В группировке вместо полного выражения укажите псевдоним city_label
4. Сколько заказов в каждом статусе было выполнено в 2024 году
В примере WHERE оставляет только заказы за 2024 год, а уже потом GROUP BY делит их на группы по статусу. Вспомним, что фильтрация идёт раньше группировки.
🔄 Попробуйте изменить запрос:
- • Поменяйте диапазон дат на BETWEEN '2023-01-01' AND '2023-12-31'
- • Уберите COALESCE и посмотрите, как отобразятся заказы без статуса
5. Сколько заказов у каждого работника в каждом статусе
Пример показывает, как выполнять группировку по нескольким столбцам. Сначала заказы делятся по работникам, а внутри каждого работника ещё и по статусам.
🔄 Попробуйте изменить запрос:
- • Добавьте фильтр WHERE order_date >= '2024-01-01' и сравните результат
- • Уберите из группировки столбец worker_id, но оставьте его в SELECT. Постарайтесь объяснить ошибку
Типичные ошибки
Выводят в SELECT столбец, которого нет в GROUP BY
Если столбец не обёрнут в агрегатную функцию, он должен участвовать в группировке. Иначе СУБД не поймёт, как его показать рядом с итогами по группе.
Ждут, что GROUP BY сам что-то считает без агрегатной функции
Сама по себе группировка только собирает одинаковые значения вместе. Чтобы получить количество, сумму, среднее или максимум, нужна агрегатная функция: COUNT(), SUM(), AVG(), MAX() или MIN().
Забывают, что WHERE работает раньше группировки
Сначала SQL отбирает строки по условию, и только потом собирает оставшиеся строки в группы. Поэтому фильтр может сильно менять итоговые цифры.
Пугаются группы с NULL
Если в столбце есть несколько строк с NULL, они не пропадут. Они соберутся в одну отдельную группу. Если хотите показать её понятнее, можно использовать COALESCE().
Группируют пользователей по имени и фамилии вместо идентификатора
На первый взгляд кажется, что GROUP BY first_name, last_name — нормальная идея. Но в реальной базе может оказаться два разных человека с одинаковыми именем и фамилией. Тогда их строки сольются в одну группу, и цифры получатся неверными. Если нужен итог по конкретному пользователю, безопаснее группировать по user_id.
Практика
Проверь себя
Ответьте на вопросы, чтобы закрепить материал:
1
В какой ситуации обычно нужен GROUP BY?
2
Что произойдёт со строками, где значение столбца равно NULL, если этот столбец участвует в GROUP BY?
3
Почему группировать пользователей по first_name и last_name может быть плохой идеей, если нужен итог по конкретным людям?
4
Какой вариант в PostgreSQL тоже может использоваться в GROUP BY, кроме явного имени столбца?
5
Напишите запрос, который выведет department_id, position и количество работников на каждой должности внутри каждого отдела. В результат должны попасть только работники с зарплатой не меньше 70000. Отсортируйте результат по department_id, а внутри отдела — по количеству работников по убыванию.
6
Напишите запрос, который покажет, сколько товаров находится в каждой категории среди товаров дороже 30000. Если категория не указана, вместо неё нужно вывести 'без категории'. Выведите название категории и количество товаров, а результат отсортируйте по названию категории.
7
Что не так в запросе SELECT first_name, COUNT(*) FROM users GROUP BY city;?
8
Напишите запрос, который покажет, сколько заказов с указанным статусом есть у каждого пользователя. Нужно вывести user_id и orders_count. Заказы без статуса в расчёт попадать не должны. Отсортируйте результат по количеству заказов по убыванию, а при равенстве — по user_id.