Модель GROUP BY: строки в корзины

Группировка — это раскладка по корзинам

GROUP BY берёт строки, пережившие WHERE, и раскладывает их по корзинам по значению ключа группировки. Все строки с одинаковым ключом попадают в одну корзину. Затем — в этом вся суть — каждая корзина сворачивается ровно в одну выходную строку.

Представь пять заказов. Группируем по status:

SELECT status, COUNT(*) AS n, SUM(total) AS revenue
FROM orders
GROUP BY status;

Postgres делит строки на одну корзину на каждое уникальное status (paid, pending, cancelled, …) и выдаёт по одной строке на корзину. Результат — это уже не «заказы», а «статусы», по строке на каждый.

Железное правило, и почему оно не произвольно

Когда корзина держит три заказа, спроси: какой тот самый id у этой корзины? Его нет — их три. А тот самый created_at? Снова три. У корзины есть одно определённое status (это ключ), но нет единого значения ни для какой другой сырой колонки. Postgres упирается в противоречие: ты просишь одну выходную строку на корзину, но SELECT id требует значения, которого у корзины нет.

Вот правило, и оно прямо вытекает из свёртки:

Каждое выражение в списке SELECT должно быть либо перечислено в GROUP BY (тогда у него одно значение на корзину), либо обёрнуто в агрегат (который вычисляет одно значение из корзины).

COUNT(*), SUM(total), MAX(created_at), array_agg(id) — каждый агрегат берёт всю корзину и возвращает один скаляр. Всё, что не агрегировано, должно быть константой внутри корзины — а это ровно то, что гарантирует попадание в GROUP BY. Ошибка «must appear in GROUP BY or be used in an aggregate» — это отказ Postgres молча взять значение одной произвольной строки. Именно так делал старый дефолт MySQL — и десятилетие молча выдавал неверные отчёты.

-- НЕВЕРНО: status не сгруппирован и не агрегирован
SELECT user_id, status, SUM(total)
FROM orders
GROUP BY user_id;            -- ERROR: status должен быть в GROUP BY или агрегате

-- ВЕРНО, вариант A: сгруппировать и по нему (одна строка на пару user+status)
SELECT user_id, status, SUM(total)
FROM orders
GROUP BY user_id, status;

-- ВЕРНО, вариант B: убрать его агрегатом (одна строка на пользователя)
SELECT user_id, MAX(status) AS some_status, SUM(total)
FROM orders
GROUP BY user_id;

Исключение по функциональной зависимости

Есть одно место, где Postgres ослабляет правило, и это не лазейка — это корректность. Если ты делаешь GROUP BY по первичному ключу таблицы, каждая другая колонка этой таблицы функционально зависит от ключа: ключ однозначно определяет строку, поэтому каждая корзина держит ровно одну строку этой таблицы, и у каждой колонки есть единственное чётко определённое значение. Postgres знает это из каталога и разрешает выбирать такие зависимые колонки без группировки:

-- u.id — это PK таблицы users, поэтому u.name и u.email функционально
-- зависят от него — Postgres разрешает их без перечисления в GROUP BY:
SELECT u.id, u.name, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;               -- не u.id, u.name, u.email — достаточно PK

Это часть стандарта SQL (правило функциональной зависимости из SQL:1999), и Postgres реализует его для первичных ключей и других not-null unique-ограничений. Оно избавляет от набивания GROUP BY дюжиной колонок только ради их вывода.

Как на самом деле выполняется свёртка

Бакетизация не бесплатна — и у планировщика два способа её сделать. На таблице orders в 12М строк GROUP BY user_id в ~2М корзин в EXPLAIN ANALYZE показывается как один из двух узлов. HashAggregate строит in-memory хеш-таблицу по ключу user_id, одна запись на корзину с накопленным состоянием агрегата — сортировка входа не нужна, но все различные ключи должны разом поместиться в память. GroupAggregate требует, чтобы вход был уже отсортирован по user_id (обычно через предшествующий Sort или index scan), затем стримит по одной корзине за раз, выдавая и выбрасывая каждую до следующей — почти постоянная память, но платит за сортировку. Планировщик выбирает HashAggregate, когда оценивает, что хеш-таблица влезет в work_mem, и переключается на sort-based GroupAggregate, когда нет.