GROUP BY: группировка строк
GROUP BY: группировка строк
В предыдущем уроке вы узнали, что агрегатные функции возвращают одно число для всей таблицы или для отфильтрованного набора строк. Но большинство реальных аналитических задач требует большего: не «средняя зарплата по всем», а «средняя зарплата по каждому отделу». Не «общая выручка», а «выручка по каждому месяцу». Это задача группировки, и её решает клауза GROUP BY.
Идея GROUP BY
GROUP BY разбивает строки таблицы на группы по значению одной или нескольких колонок, а затем применяет агрегатную функцию к каждой группе отдельно. Вместо одной итоговой строки вы получаете по одной строке на каждую уникальную группу.
Синтаксис:
SELECT колонка_группировки, агрегат(другая_колонка)
FROM таблица
GROUP BY колонка_группировки;
Простой пример — количество сотрудников в каждом отделе:
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;
Таблица employees:
id | name | department | salary
---+--------+-------------+--------
1 | Анна | Разработка | 90000
2 | Борис | Менеджмент | 110000
3 | Вера | Разработка | 85000
4 | Гриша | Дизайн | 75000
5 | Дина | Разработка | 95000
Результат запроса:
department | headcount
------------+----------
Дизайн | 1
Менеджмент | 1
Разработка | 3
СУБД разбила строки на три группы по значению department, подсчитала COUNT(*) для каждой группы и вернула по одной строке на группу.
Несколько агрегатов с GROUP BY
В одном запросе можно вычислять несколько агрегатов для каждой группы:
SELECT department,
COUNT(*) AS headcount,
SUM(salary) AS payroll,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department
ORDER BY payroll DESC;
Результат — полная статистика по каждому отделу в одном запросе.
Проверь себя: сколько строк вернёт SELECT department, COUNT(*) FROM employees GROUP BY department для таблицы выше?
Правило SELECT с GROUP BY
Это важнейшее правило: в SELECT-списке при наличии GROUP BY каждая колонка должна быть либо:
- Колонкой из GROUP BY
- Агрегатной функцией
Нельзя включить в SELECT произвольную колонку, не попавшую в GROUP BY:
-- Ошибка!
SELECT department, name, COUNT(*) FROM employees GROUP BY department;
-- ERROR: column "employees.name" must appear in GROUP BY clause
Почему? Потому что «Разработка» имеет трёх сотрудников — Анну, Веру и Дину. Какое именно name вернуть для этой группы? СУБД не знает. Нужно либо добавить name в GROUP BY (тогда группировка будет по комбинации department + name), либо применить агрегат: MAX(name), STRING_AGG(name, ', ') и т.д.
GROUP BY с WHERE
WHERE фильтрует строки до группировки. Это позволяет агрегировать только нужные строки:
SELECT department,
COUNT(*) AS headcount,
AVG(salary) AS avg_salary
FROM employees
WHERE salary > 80000
GROUP BY department;
Логика выполнения: сначала WHERE убирает сотрудников с зарплатой ≤ 80000, затем GROUP BY группирует оставшихся.
Порядок логического выполнения запроса:
FROM → WHERE → GROUP BY → SELECT → ORDER BY → LIMIT
GROUP BY по нескольким колонкам
Можно группировать по нескольким колонкам одновременно — тогда уникальные комбинации значений образуют группы:
SELECT department, year, COUNT(*) AS hires
FROM employees
GROUP BY department, year;
Если у вас есть 3 отдела и 4 года, результат может содержать до 3×4 = 12 строк (по одной на каждую уникальную пару «отдел + год»).
GROUP BY и ORDER BY
ORDER BY применяется после GROUP BY и может сортировать по колонкам из GROUP BY или по агрегатным значениям:
SELECT department,
COUNT(*) AS headcount,
SUM(salary) AS payroll
FROM employees
GROUP BY department
ORDER BY payroll DESC; -- сортировка по результату агрегата
Или в PostgreSQL — сортировка по номеру позиции в SELECT:
ORDER BY 3 DESC; -- третья колонка = payroll
Хотя такой стиль и работает, лучше писать явное имя колонки для читаемости.
GROUP BY без агрегатов: как DISTINCT
Интересный факт: GROUP BY без агрегатных функций работает как DISTINCT — возвращает уникальные значения:
SELECT department FROM employees GROUP BY department;
-- то же самое, что:
SELECT DISTINCT department FROM employees;
Оба запроса вернут список уникальных отделов. Разница: GROUP BY исторически быстрее на некоторых типах данных в отдельных СУБД, но PostgreSQL оптимизирует оба варианта примерно одинаково. Используйте DISTINCT для явной семантики «убрать дубликаты» и GROUP BY когда нужны агрегаты.
GROUP BY с CASE WHEN
GROUP BY можно применять к вычисляемым выражениям, включая CASE WHEN. Это позволяет создавать кастомные группы:
SELECT CASE
WHEN salary < 70000 THEN 'Низкая'
WHEN salary < 100000 THEN 'Средняя'
ELSE 'Высокая'
END AS salary_level,
COUNT(*) AS employee_count,
AVG(salary) AS avg_in_level
FROM employees
GROUP BY CASE
WHEN salary < 70000 THEN 'Низкая'
WHEN salary < 100000 THEN 'Средняя'
ELSE 'Высокая'
END
ORDER BY avg_in_level;
Это сгруппирует сотрудников по уровню зарплаты — три группы вместо отдельных значений. CASE WHEN в GROUP BY нужно повторить (или использовать номер позиции GROUP BY 1).
GROUP BY по дате: агрегация по периодам
Частый паттерн в аналитике — группировка по временному периоду: день, месяц, год. PostgreSQL предоставляет функцию DATE_TRUNC для «обрезки» временных меток:
SELECT DATE_TRUNC('month', created_at) AS month,
COUNT(*) AS orders_count,
SUM(total_amount) AS monthly_revenue
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;
DATE_TRUNC('month', created_at) обрезает дату до начала месяца: 2024-03-15 14:30 → 2024-03-01 00:00. Все даты одного месяца попадают в одну группу. Результат — ежемесячная статистика по заказам. Детали работы с датами будут в модуле 9.
Практический пример: анализ продаж по категориям
Задача: выручка, количество продаж и средний чек по каждой категории товаров:
SELECT p.category,
COUNT(*) AS orders_count,
SUM(oi.quantity * oi.unit_price) AS revenue,
ROUND(AVG(oi.quantity * oi.unit_price), 2) AS avg_check
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.category
ORDER BY revenue DESC;
Этот пример использует JOIN (модуль 6), но показывает типичное применение GROUP BY в аналитике. SUM(quantity * unit_price) вычисляет выручку как сумму произведений количества на цену за единицу. Сочетание GROUP BY, JOIN и нескольких агрегатов — основа большинства аналитических отчётов в реальных системах.
Типичные ошибки
1. Колонка в SELECT не в GROUP BY и не агрегирована:
SELECT department, name, COUNT(*) -- ошибка: name не в GROUP BY
FROM employees GROUP BY department;
2. Агрегирование уже сгруппированных данных: Если нужен итог по итогам — нужен вложенный запрос или CTE (модуль 7).
3. WHERE после GROUP BY (вместо HAVING):
-- Неправильно: нельзя фильтровать по результатам агрегации через WHERE
SELECT department, COUNT(*) AS cnt
FROM employees
WHERE cnt > 2 -- ERROR: column "cnt" does not exist
GROUP BY department;
-- Правильно: HAVING (следующий урок)
SELECT department, COUNT(*) AS cnt
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;
Краткий итог
GROUP BY колонка— разбивает строки на группы, агрегатные функции применяются к каждой группе отдельно- В
SELECTприGROUP BY— только колонки изGROUP BYи агрегаты WHEREфильтрует до группировки; фильтрация по результатам агрегации — черезHAVINGGROUP BYпо нескольким колонкам: группы образуют уникальные комбинации- Порядок выполнения:
FROM → WHERE → GROUP BY → SELECT → ORDER BY → LIMIT
Что дальше
Вы умеете группировать строки. Но иногда нужно отфильтровать сами группы — например, показать только отделы с более чем пятью сотрудниками. WHERE для этого не подходит (он работает до группировки). Специальная клауза для фильтрации групп — HAVING. Разберём её в следующем уроке.