GROUPING SETS, ROLLUP, CUBE: много группировок за один проход

Один запрос, несколько группировок

Спроси себя: сколько раз ты видел дашборд, собранный из трёх почти одинаковых запросов на UNION ALL, каждый из которых сканирует одну таблицу и рассинхронизируется, как только кто-то правит баг только в одной ветке? GROUPING SETS — ответ на этот вопрос.

Обычно GROUP BY выдаёт ровно одну группировку. GROUPING SETS позволяет запросить несколько группировок одним запросом, складывая их результаты в один вывод:

SELECT status, user_id, SUM(total) AS revenue
FROM orders
GROUP BY GROUPING SETS (
  (status),        -- выручка по статусу
  (user_id),       -- выручка по пользователю
  ()               -- общий итог (пустое множество = без группировки)
);

Каждый список в скобках — одна группировка. Пустое множество () значит «не группировать вовсе» — общий итог по всем строкам. Postgres сканирует orders один раз и выдаёт объединение всех трёх группировок. Колонки, не входящие в данную группировку, возвращаются как NULL в тех строках (user_id равен NULL в строках по статусу, ведь пользователя нет в этом множестве).

ROLLUP и CUBE: сокращения для частых форм

Два сокращения покрывают шаблоны, которые тебе реально нужны:

• ROLLUP (a, b) разворачивается в иерархические множества группировок (a, b), (a), (). Используй, когда колонки образуют иерархию — (year, month) → итоги по месяцу, подытог по году, общий итог. Это «drill-down с подытогами».
• CUBE (a, b) разворачивается во все комбинации: (a, b), (a), (b), (). Используй для полной кросс-таблицы, где важны все измерения и итоги.

-- Подытоги по статусу в иерархии rollup, плюс общий итог:
SELECT status, SUM(total) AS revenue
FROM orders
GROUP BY ROLLUP (status);   -- = GROUPING SETS ((status), ())

-- Каждая комбинация status × user_id, плюс все подытоги и общий итог:
SELECT status, user_id, SUM(total)
FROM orders
GROUP BY CUBE (status, user_id);

ROLLUP (a, b) даёт n+1 множеств группировок; CUBE из n колонок даёт 2^n. Тянись за ROLLUP для иерархий (частые 90%) и за CUBE лишь когда тебе действительно нужна каждая кросс-комбинация — его вывод растёт экспоненциально с числом измерений.

Причина, по которой это бьёт старый паттерн UNION ALL, — число сканов. На таблице orders в 12М строк подсчёт по статусу, по пользователю и общего итога тремя SELECT, склеенными UNION ALL, — это три независимых Seq Scan: ~3 × 800мс ≈ 2.4с плюс стоимость перечитывания суммарно 36М строк. Форма GROUPING SETS в EXPLAIN ANALYZE выглядит как один Seq Scan (rows=12000000), питающий MixedAggregate (или GroupAggregate над отсортированным входом), который считает все три группировки за один проход — ~950мс, одно чтение 12М строк. Подвох CUBE — в выводе: CUBE по 4 измерениям — это 2⁴ = 16 множеств группировок, и на колонках высокой кардинальности каждое множество может быть миллионами строк — результат может затмить базовую таблицу. Postgres всё равно делает это за один скан входа, но состояние агрегации для 16 одновременных группировок может пробить work_mem и спиллить (HashAggregate ... Disk Usage), так что добавляй измерения в CUBE осознанно.

GROUPING(): этот NULL — подытог или настоящие данные?

Вот ловушка. У строки подытога NULL в колонках, по которым она не сгруппирована. Но твои данные могут также содержать настоящий NULL в status. Как отличить «все статусы» (подытог) от «заказа, чей status действительно NULL»? Функция GROUPING():

SELECT
  status,
  SUM(total) AS revenue,
  GROUPING(status) AS is_subtotal   -- 1 = строка подытога, 0 = настоящее значение status
FROM orders
GROUP BY ROLLUP (status);

GROUPING(col) возвращает 1, если col был свёрнут (агрегирован прочь) для этой строки — то есть это строка подытога/итога, — и 0, если col — настоящее сгруппированное значение (включая настоящий NULL). Используй её для разметки строк или чистого coalesce: CASE WHEN GROUPING(status) = 1 THEN 'All statuses' ELSE status END. Без GROUPING() настоящий NULL status и подытог неразличимы, и твой отчёт молча подписывает их одинаково.