Зачем нужна группировка

В прошлом уроке мы сжали всю таблицу в одну строку: SELECT AVG(price_cents) FROM products. Но почти всегда хочется ответ «в разрезе»: средняя цена по каждой категории, число заказов у каждого клиента, выручка по каждому месяцу.

Это и есть

Модель: каждая группа = одна строка

Формально, GROUP BY col делает три вещи:

1. Берёт исходное отношение.
2. Разбивает кортежи на группы — в одну группу попадают кортежи с одинаковыми значениями group-by-атрибутов.
3. Для каждой группы вычисляет агрегаты и возвращает одну строку — значения group-by + значения агрегатов.

Запустим:

Пять строк на выходе — по одной на каждое уникальное значение country. В SELECT мы можем выводить либо саму group-by-колонку (country), либо агрегат (COUNT(*)). Это важное правило, к которому мы сейчас вернёмся.

Группировка по нескольким колонкам

GROUP BY принимает список колонок. Группой становится уникальное сочетание их значений:

Каждая комбинация (country, birth_year) — отдельная группа. Если у двух клиентов одинаковые страна и год — они в одной группе. Если у одного NULL в birth_year — он в отдельной группе с другими NULL-ами (важно: в GROUP BY все NULL-ы попадают в одну группу, в отличие от обычного сравнения).

Главное правило: всё в SELECT — либо в GROUP BY, либо агрегат

Вот строгое правило, нарушение которого даёт самую частую ошибку SQL-новичка:

Любая колонка в SELECT (или ORDER BY, или HAVING) должна быть либо включена в GROUP BY, либо обёрнута в агрегатную функцию.

Почему так? Подумай о модели. Группа RU содержит 6 клиентов с разными именами, разными датами рождения, разными датами регистрации. Группа должна стать одной строкой. Какое имя СУБД положит в результат? Имя первого клиента? Случайного? Это не определено — поэтому стандарт SQL запрещает такие запросы.

PostgreSQL вернёт явное сообщение: column "customers.full_name" must appear in the GROUP BY clause or be used in an aggregate function. Это защита от бессмыслицы.

Чтобы запрос работал, есть три варианта:

• Добавить full_name в GROUP BY — но тогда группой станет (country, full_name), и это уже не «по странам».
• Обернуть в агрегат: STRING_AGG(full_name, ', ') — соберёт все имена в одну строку.
• Использовать MIN(full_name) или MAX(full_name) — лексикографически первое/последнее имя в группе. Это лайфхак, когда нужно «хоть какое-то» представительное значение.

Functional dependency: PK освобождает от перечисления

PostgreSQL (начиная с версии 9.1) умеет одну удобную поблажку. Если ты группируешь по PRIMARY KEY таблицы, то можно выводить любые другие колонки этой таблицы без перечисления их в GROUP BY. СУБД понимает: PK уникален, значит, в каждой группе ровно одна строка, значит, все остальные колонки функционально зависят от PK и однозначно определены.

Без этой поблажки пришлось бы писать GROUP BY c.id, c.full_name, c.email. Это работает и абсолютно правильно — просто многословно. Сейчас в Postgres стало короче.

GROUP BY без агрегатов = DISTINCT

Если в SELECT вообще нет агрегатов, а просто перечислены те же колонки, что и в GROUP BY, — результат идентичен SELECT DISTINCT.

Оптимизатор Postgres эти два запроса часто превращает в один и тот же план. Стиль выбирается по читаемости: если идея — «уникальные значения», пиши DISTINCT; если «по группам, просто без агрегатов» — GROUP BY. Но если ты добавишь хоть один агрегат, выбора уже нет — нужен GROUP BY.

Группировка с JOIN — типичный сценарий

Чаще всего GROUP BY идёт не по одной таблице, а по результату JOIN. Это позволяет считать агрегаты «в разрезе родительской таблицы»:

LEFT JOIN тут важен: клиент без заказов всё равно появится в результате с orders_count = 0. С обычным JOIN он бы выпал. COUNT(DISTINCT o.id) — потому что один заказ через JOIN order_items может появиться несколько раз (если в нём несколько товаров). К этой ловушке мы вернёмся в уроке 6 модуля.

Тонкость: GROUP BY и NULL

В GROUP BY все NULL-ы считаются равными между собой, и попадают в одну группу. Это исключение из общего правила трёхзначной логики (как и в DISTINCT, UNION, set-операциях).

В результате будет строка birth_year = NULL, cnt = 1. Без этого исключения NULL-клиенты «потерялись» бы — потому что NULL != NULL в обычном сравнении. GROUP BY (как и DISTINCT) специально устроен иначе, чтобы было удобно.

Чек-лист

• GROUP BY разрезает отношение на группы по значениям перечисленных колонок. Каждая группа → одна строка в результате.
• Все колонки в SELECT должны быть либо в GROUP BY, либо в агрегате. Иначе — ошибка парсера.
• Группировка по PRIMARY KEY в Postgres освобождает от перечисления остальных колонок этой таблицы — functional dependency.
• GROUP BY без агрегатов эквивалентен SELECT DISTINCT.
• В GROUP BY все NULL считаются равными и попадают в одну группу — исключение из трёхзначной логики.
• При группировке после LEFT JOIN помни про COUNT(DISTINCT ...), чтобы не задвоить из-за множества дочерних строк.