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;
После SELECT часто указываются две части: столбец, по которому хотим разбить строки на группы, и агрегатную функцию, которая будет считать итог внутри каждой группы.
Дальше идёт FROM, а после него — GROUP BY с тем же столбцом, по которому нужно собрать одинаковые значения вместе.
Например, если написать GROUP BY city, то все пользователи из одного и того же города попадут в одну группу. После этого COUNT(*) сможет посчитать, сколько строк оказалось в каждой такой группе.
Но есть важное правило. Если в SELECT вы выводите дополнительный столбец, а не агрегатную функцию, этот столбец должен участвовать в GROUP BY. Иначе база данных не поймёт, как именно собрать строки.
В PostgreSQL группировать можно не только по самому столбцу, но и по его псевдониму или по позиционному номеру из SELECT.
Например, если нужный столбец в запросе стоит первым, можно написать GROUP BY 1. Но в учебных и рабочих запросах чаще всё же лучше писать явное имя столбца или понятный псевдоним, потому что такой код легче читать.
Ещё один важный момент: NULL тоже может участвовать в группировке. Если в столбце есть несколько строк с NULL, они соберутся в одну отдельную группу.
После группировки результат можно отсортировать через ORDER BY.
Это очень удобно, когда нужно, например, показать сначала самые большие группы.
Дальше идёт FROM, а после него — GROUP BY с тем же столбцом, по которому нужно собрать одинаковые значения вместе.
Например, если написать GROUP BY city, то все пользователи из одного и того же города попадут в одну группу. После этого COUNT(*) сможет посчитать, сколько строк оказалось в каждой такой группе.
Но есть важное правило. Если в SELECT вы выводите дополнительный столбец, а не агрегатную функцию, этот столбец должен участвовать в GROUP BY. Иначе база данных не поймёт, как именно собрать строки.
В PostgreSQL группировать можно не только по самому столбцу, но и по его псевдониму или по позиционному номеру из SELECT.
Например, если нужный столбец в запросе стоит первым, можно написать GROUP BY 1. Но в учебных и рабочих запросах чаще всё же лучше писать явное имя столбца или понятный псевдоним, потому что такой код легче читать.
Ещё один важный момент: NULL тоже может участвовать в группировке. Если в столбце есть несколько строк с NULL, они соберутся в одну отдельную группу.
После группировки результат можно отсортировать через ORDER BY.
Это очень удобно, когда нужно, например, показать сначала самые большие группы.
Примеры
1. Сколько работников на каждой должности
Здесь строки таблицы workers сначала группируются по одинаковой должности, а потом COUNT(*) считает, сколько работников оказалось в каждой группе. В результате мы видим не одно общее число по всей таблице, а отдельный итог по каждой должности.
🔄 Попробуйте изменить запрос:
- • Уберите ORDER BY и посмотрите, как изменится порядок вывода
- • Замените position на department_id и получите количество работников по отделам
2. Средняя зарплата по отделам
В этом примере мы не хотим просто одну среднюю зарплату по всей компании, нам нужно понять картину по отделам. Для этого группируем строки по department_id, а потом считаем AVG(salary) отдельно внутри каждой группы.
🔄 Попробуйте изменить запрос:
- • Поменяйте AVG(salary) на MAX(salary) и посмотрите максимальную зарплату по отделам
- • Добавьте перед группировкой фильтр WHERE salary >= 70000 и сравните результат
3. Сколько пользователей в каждом городе
Здесь, если у пользователя город не указан, вместо NULL мы показываем понятную группу с названием 'город не указан'. Так результат выглядит аккуратнее и легче читается.
🔄 Попробуйте изменить запрос:
- • Уберите COALESCE и посмотрите, как будет выглядеть группа с NULL
- • Замените текст 'город не указан' на 'неизвестный город'
4. Сколько заказов по каждому статусу в 2024 году
Сначала WHERE оставляет только заказы за 2024 год, а уже потом GROUP BY делит их на группы по статусу. То есть фильтрация идёт раньше группировки.
🔄 Попробуйте изменить запрос:
- • Поменяйте диапазон дат на BETWEEN '2023-01-01' AND '2023-12-31'
- • Уберите COALESCE и посмотрите, как отобразятся заказы без статуса
5. Сколько заказов у каждого работника в каждом статусе
Это пример группировки по нескольким столбцам. Сначала заказы делятся по работникам, а внутри каждого работника ещё и по статусам. Заодно здесь видно, что в PostgreSQL можно группировать и по псевдониму status_label, а не повторять всё выражение целиком.
🔄 Попробуйте изменить запрос:
- • Добавьте фильтр WHERE order_date >= '2024-01-01' и сравните результат
- • Попробуйте заменить worker_id на user_id и получить группировку по пользователям и статусам
Типичные ошибки
Выводят в 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.