GROUP BY по нескольким колонкам
GROUP BY по нескольким колонкам
В предыдущих уроках вы группировали строки по одной колонке — например, по department. Это даёт агрегат для каждого уникального отдела. Но реальные аналитические задачи часто требуют нескольких измерений: не просто «сколько сотрудников в каждом отделе», а «сколько сотрудников в каждом отделе по каждому уровню должности». Это многоуровневая группировка, и GROUP BY поддерживает её естественным образом.
Синтаксис многоуровневой группировки
Просто перечислите несколько колонок в GROUP BY через запятую:
SELECT col1, col2, агрегат(col3)
FROM таблица
GROUP BY col1, col2;
Каждая уникальная комбинация значений col1 и col2 образует отдельную группу.
Пример: количество сотрудников по отделу и уровню должности.
Таблица employees:
id | name | department | level | salary
---+--------+-------------+---------+--------
1 | Анна | IT | Senior | 120000
2 | Борис | IT | Junior | 70000
3 | Вера | IT | Senior | 115000
4 | Гриша | HR | Middle | 80000
5 | Дина | IT | Middle | 90000
6 | Елена | HR | Junior | 55000
Запрос:
SELECT department, level, COUNT(*) AS headcount
FROM employees
GROUP BY department, level
ORDER BY department, level;
Результат:
department | level | headcount
-----------+--------+----------
HR | Junior | 1
HR | Middle | 1
IT | Junior | 1
IT | Middle | 1
IT | Senior | 2
Каждая пара (department, level) — отдельная группа. Всего 5 уникальных комбинаций из 6 строк (IT/Senior встречается дважды).
Порядок колонок в GROUP BY
Порядок колонок в GROUP BY не влияет на результат — только на то, как имена колонок расположены в выводе. GROUP BY department, level и GROUP BY level, department дают одинаковые данные, только строки могут идти в другом порядке без явного ORDER BY.
Для читаемости пишите колонки в том же порядке, что и в SELECT.
Сколько строк возвращает многоуровневый GROUP BY
Количество строк в результате равно числу уникальных комбинаций значений колонок группировки. Для двух колонок с M и N уникальными значениями — максимум M × N строк, но реальных комбинаций может быть меньше (не все комбинации обязательно существуют в данных).
Проверь себя: если в таблице 5 отделов и 4 уровня, сколько строк максимум может вернуть GROUP BY department, level?
GROUP BY с агрегатами по двум измерениям
Мощь многоуровневой группировки — в аналитике по нескольким измерениям одновременно:
SELECT department,
level,
COUNT(*) AS headcount,
SUM(salary) AS payroll,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department, level
ORDER BY department, avg_salary DESC;
Результат — полная картина: для каждой комбинации «отдел + уровень» получаем количество, фонд оплаты труда и среднюю зарплату.
GROUP BY с WHERE и HAVING
Все три фильтра работают вместе:
SELECT department,
level,
COUNT(*) AS headcount,
AVG(salary) AS avg_salary
FROM employees
WHERE salary > 60000 -- исключить строки с маленькой зарплатой
GROUP BY department, level
HAVING COUNT(*) >= 2 -- только комбинации с 2+ сотрудниками
ORDER BY department, level;
Порядок выполнения:
WHERE salary > 60000— убираем «дешёвых»GROUP BY department, level— группируем оставшихсяHAVING COUNT(*) >= 2— убираем маленькие группыORDER BY— сортируем
Группировка по трём и более колонкам
Никаких ограничений на количество колонок в GROUP BY нет. Три и более — пожалуйста:
SELECT year, quarter, category, SUM(revenue) AS quarterly_revenue
FROM sales
GROUP BY year, quarter, category
ORDER BY year, quarter, quarterly_revenue DESC;
С каждой новой колонкой количество групп растёт — потенциально до произведения уникальных значений каждой колонки. На больших данных это может быть много строк.
Практический пример: матрица продаж
Задача: для каждой комбинации «регион + категория» посчитать количество заказов и выручку. Это типичная кросс-таблица (pivot table) в виде SQL-запроса:
SELECT region,
category,
COUNT(*) AS orders_count,
SUM(total_amount) AS revenue,
AVG(total_amount) AS avg_order
FROM orders
WHERE status = 'completed'
AND created_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY region, category
ORDER BY region, revenue DESC;
Результат — таблица «регион × категория» с агрегатами. Аналитик или менеджер сразу видит, какие комбинации работают лучше всего.
Типичные ошибки
1. Не все не-агрегированные колонки в GROUP BY:
SELECT department, level, name, COUNT(*) -- ошибка: name не в GROUP BY
FROM employees GROUP BY department, level;
2. Неправильная интерпретация результата:
SELECT department, level, SUM(salary)
FROM employees
GROUP BY department; -- ошибка: level не в GROUP BY
Если level в SELECT но не в GROUP BY, PostgreSQL выдаст ошибку. Некоторые СУБД (MySQL в старых версиях) молча выбирают «случайное» значение level — это ошибка данных без явного сообщения.
3. Ожидание строки для каждой комбинации, которой нет в данных:
Если в данных нет сотрудника с (HR, Senior), эта комбинация не появится в результате. GROUP BY возвращает только существующие комбинации.
Группировка по вычисляемым выражениям
В GROUP BY можно использовать любое выражение, не только имена колонок. Это позволяет группировать по производным значениям:
-- Группировка по году найма
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year,
COUNT(*) AS hires
FROM employees
GROUP BY EXTRACT(YEAR FROM hire_date)
ORDER BY hire_year;
-- Группировка по первой букве фамилии
SELECT SUBSTR(last_name, 1, 1) AS initial,
COUNT(*) AS count
FROM employees
GROUP BY SUBSTR(last_name, 1, 1)
ORDER BY initial;
EXTRACT извлекает часть даты (год, месяц и т.д.). SUBSTR(str, start, length) возвращает подстроку. В PostgreSQL также работает LEFT(str, n) для первых N символов.
Выражение в GROUP BY должно полностью совпадать с выражением в SELECT — или можно использовать номер позиции:
GROUP BY 1 -- группировать по первой колонке в SELECT
Как думать о GROUP BY + GROUP BY
Многоуровневую группировку удобно представлять как «матрицу» или «куб данных». Каждая колонка в GROUP BY добавляет ещё одно измерение:
GROUP BY department— одномерная таблица: строки = отделыGROUP BY department, level— двумерная матрица: строки = отделы × уровниGROUP BY year, quarter, department— трёхмерный куб: год × квартал × отдел
В SQL-результате трёхмерный куб «разворачивается» в плоскую таблицу, где каждая комбинация трёх значений — одна строка.
Визуализация: GROUP BY department, level
Наша таблица employees при GROUP BY department, level разбивается так:
Шаг 1: исходные строки
IT/Senior → [Анна 120000, Вера 115000]
IT/Middle → [Дина 90000]
IT/Junior → [Борис 70000]
HR/Middle → [Гриша 80000]
HR/Junior → [Елена 55000]
Шаг 2: применяем COUNT(*) к каждой группе
IT/Senior → 2
IT/Middle → 1
IT/Junior → 1
HR/Middle → 1
HR/Junior → 1
Именно так работает GROUP BY внутри: разбивает данные на группы, потом сворачивает каждую группу в одно значение.
Проверка запроса перед GROUP BY
Полезная практика: прежде чем писать GROUP BY, напишите запрос без него с ORDER BY по тем же колонкам. Так вы увидите исходные строки и поймёте, правильно ли вы представляете структуру данных:
-- Сначала посмотрите на сырые данные
SELECT department, level, salary
FROM employees
ORDER BY department, level;
-- Потом агрегируйте
SELECT department, level, COUNT(*), AVG(salary)
FROM employees
GROUP BY department, level
ORDER BY department, level;
Это помогает поймать ошибки до того, как агрегат «скроет» неожиданные данные.
Краткий итог
GROUP BY col1, col2— группировка по уникальным комбинациям значений- Количество строк = количество уникальных комбинаций в данных
- В
SELECT— только колонки изGROUP BYи агрегатные функции - Порядок колонок в
GROUP BYне влияет на результат WHERE,GROUP BY,HAVINGработают вместе: разные этапы фильтрацииGROUP BYвозвращает только существующие комбинации, не все теоретически возможные- Перед написанием
GROUP BYполезно изучить сырые данные сORDER BY
Что дальше
Вы освоили многоуровневую группировку. Финальный урок этого модуля — типичные ошибки при работе с WHERE и HAVING. Разберём пограничные случаи, которые часто путают разработчиков.