Дашборд продаж Brew должен на одном экране показать три цифры: выручку по каждому магазину, выручку по каждой категории напитков и общий итог по всей сети. Аналитик садится за SQL и пишет три отдельных SELECT: один с GROUP BY shop, другой с GROUP BY category, третий вообще без группировки — а потом склеивает их через UNION ALL. И тут начинается мучение: у запросов разное число колонок, надо подставлять NULL-заглушки, чтобы выровнять SELECT-списки, легко перепутать порядок и получить «выручку категории» в колонке магазина. Три прохода по таблице, три места, где можно ошибиться, и витрина, которую страшно трогать.

А Postgres умеет посчитать все эти срезы — листья, подытоги и общий итог — за один проход одного запроса. Расширения GROUP BY: ROLLUP, CUBE и GROUPING SETS.

Обычный GROUP BY даёт только листья

GROUP BY (shop, category) группирует по каждой паре значений и возвращает по строке на комбинацию: (Central, coffee), (Central, tea), (North, coffee), (North, tea). Это «листья» — самые детальные ячейки. Никаких подытогов «всего по Central» или «всего по сети» обычный GROUP BY не даёт: чтобы получить их, нужен ещё один запрос с более грубой группировкой. Именно отсюда растут те три SELECT с UNION ALL.

ROLLUP добавляет иерархические подытоги

GROUP BY ROLLUP (shop, category) считает листья — и сверх них добавляет подытоги по префиксам списка колонок, идя справа налево. Для (shop, category) это значит: к каждому магазину добавляется строка, где category свёрнута (подытог по магазину), плюс одна строка, где свёрнуты обе колонки (общий итог). Иерархия читается как дерево: магазин → его категории, и в корне — итог по всему. Свёрнутая колонка в строке-подытоге приходит как NULL; в демо мы подписываем такие уровни через coalesce(shop, '— все —').

CUBE добавляет все комбинации свёрнутости

ROLLUP сворачивает только по иерархии «слева направо»: он не даст подытог по категории отдельно (где свёрнут shop, но не category). GROUP BY CUBE (shop, category) закрывает это: он считает все комбинации того, какие колонки свёрнуты, а какие нет. Для двух колонок это четыре варианта: обе развёрнуты (листья), свёрнута category (подытог по магазину), свёрнут shop (подытог по категории — «сколько всего coffee по всей сети»), свёрнуты обе (общий итог). CUBE — это ROLLUP плюс те самые поперечные срезы по категории.

GROUPING SETS — ровно те срезы, что нужны

CUBE щедр: он даёт всё, в том числе листья, которые дашборду не нужны. Когда срезы известны заранее, их перечисляют вручную: GROUP BY GROUPING SETS ((shop), (category), ()). Каждый элемент в скобках — отдельная группировка: (shop) — итоги по магазину, (category) — итоги по категории, () — пустая группировка, то есть общий итог. Никаких листьев, никаких лишних строк — ровно те три среза, что просит витрина. По сути ROLLUP и CUBE — это сахар над GROUPING SETS: они разворачиваются в конкретные наборы группировок, а GROUPING SETS даёт писать этот набор напрямую.

grouping() отличает подытог от настоящего NULL

Свёрнутая колонка в строке-подытоге приходит как NULL. Но NULL бывает и в реальных данных — и тогда строку-итог не отличить от строки с настоящим пропуском. Это решает функция grouping(col): она возвращает 1, если в этой строке колонка свёрнута (это NULL-подытог), и 0, если перед нами реальное значение. По ней же удобно сортировать, чтобы итоги уходили в конец каждой группы: ORDER BY grouping(shop), shop, grouping(category), category — сначала строки с реальным shop, потом подытог по магазину. В демо мы складываем оба флага в колонку level = grouping(shop) + grouping(category): 0 — это данные/лист, 1 — подытог по одной свёрнутой колонке, 2 — общий итог (свёрнуты обе).

Две колонки → четыре варианта свёрнутости, квадрат 2×2. Каждое расширение GROUP BY закрашивает свой набор клеток:

               category развёрнута       category СВЁРНУТА
            ┌──────────────────────────┬──────────────────────────┐
 shop       │ ЛИСТЬЯ           level 0 │ подытог по магазину  l. 1 │
 развёрнут  │ (Central, coffee) 1000   │ (Central, — все —) 1300   │
            ├──────────────────────────┼──────────────────────────┤
 shop       │ подытог по кат.  level 1 │ ОБЩИЙ ИТОГ        level 2 │
 СВЁРНУТ    │ (— все —, coffee) 1700   │ (— все —, — все —) 2200   │
            └──────────────────────────┴──────────────────────────┘
 
  GROUP BY        → только верх-лево (листья)
  ROLLUP          → верх-лево + верх-право + низ-право (нет поперечного низ-лево)
  CUBE            → все четыре клетки
  GROUPING SETS   → ровно те клетки, что перечислишь

Та же развилка таблицей:

Что показывает наш код

В query.sql три запроса на одной маленькой таблице фактов sales_fact_lab (две сети × две категории, фиксированные числа). Сердце урока — RollupByShop: ROLLUP плюс grouping() для подписи и сортировки итогов.

SELECT
    coalesce(shop, '— все —')                  AS shop,
    coalesce(category, '— все —')              AS category,
    (sum(cents))::bigint                       AS cents,
    (grouping(shop) + grouping(category))::int AS level
FROM sales_fact_lab
GROUP BY ROLLUP (shop, category)
ORDER BY grouping(shop), shop, grouping(category), category;

CubeAllAngles повторяет это с GROUP BY CUBE (shop, category), а GroupingSetsExplicit — с GROUP BY GROUPING SETS ((shop), (category), ()); остальное во всех трёх одинаково. В cmd/demo/main.go тонкий слой: pgxpool → db.New → три типизированных вызова (RollupByShop, CubeAllAngles, GroupingSetsExplicit) → tabwriter, печатающий магазин, категорию, выручку и level. coalesce(..., '— все —') в SQL подписывает свёрнутые уровни прямо в выводе.

Запуск

Подними песочницу (из корня репозитория) и накати схему Brew:

docker compose up -d
make lecture L=08-analytical-and-lateral/08-06-grouping-sets-rollup-cube T=db-reset
make lecture L=08-analytical-and-lateral/08-06-grouping-sets-rollup-cube

(T=run — значение по умолчанию. Изнутри каталога юнита это make db-reset, make run.)

Вывод:

1) ROLLUP (shop, category) — листья, подытог по магазину, общий итог:
МАГАЗИН  категория  выручка  уровень
Central  coffee     1000     0
Central  tea        300      0
Central  — все —    1300     1
North    coffee     700      0
North    tea        200      0
North    — все —    900      1
— все —  — все —    2200     2
 
2) CUBE (shop, category) — плюс подытоги по категории по всей сети:
МАГАЗИН  категория  выручка  уровень
Central  coffee     1000     0
Central  tea        300      0
Central  — все —    1300     1
North    coffee     700      0
North    tea        200      0
North    — все —    900      1
— все —  coffee     1700     1
— все —  tea        500      1
— все —  — все —    2200     2
 
3) GROUPING SETS ((shop),(category),()) — только нужные срезы:
МАГАЗИН  категория  выручка  уровень
Central  — все —    1300     1
North    — все —    900      1
— все —  coffee     1700     1
— все —  tea        500      1
— все —  — все —    2200     2

Сравни три блока. ROLLUP дал листья (Central/coffee 1000 и так далее), подытог по каждому магазину (Central/— все — 1300, North/— все — 900) и общий итог (— все —/— все — 2200). CUBE повторил всё это и добавил ровно две строки — — все —/coffee 1700 и — все —/tea 500: это подытоги по категории поперёк магазинов («сколько coffee и сколько tea по всей сети»), которых у ROLLUP нет. А GROUPING SETS выкинул листья и оставил ровно три перечисленных среза — итоги по магазину, итоги по категории и общий итог: пять строк, которые и нужны дашборду, без единой лишней.

Заборчик

• Главная ловушка — NULL в строке-подытоге неотличим от настоящего NULL в данных, если колонка их допускает: без grouping() не скажешь, NULL тут — это «итог по всем категориям» или «у строки реально не было категории». Как только в группируемой колонке возможны реальные NULL, метка grouping() (и/или coalesce для подписи) перестаёт быть украшением и становится обязательной.
• CUBE растёт как 2^N комбинаций по числу колонок: на двух измерениях это четыре группировки, на пяти уже тридцать две. На многих измерениях CUBE дорог — под конкретный дашборд лучше брать GROUPING SETS с ровно нужными срезами.
• Это разовая сводка «на лету» по небольшой таблице фактов, а не замена настоящему OLAP-кубу или материализованным агрегатам на больших данных. Когда срезов много, данные тяжёлые, а витрину дёргают часто, построение и регулярное обновление такой витрины (предрасчёт, материализованные представления, отдельная аналитическая база) — забота аналитической платформы и твоего DBA, а не одного SELECT в продовом OLTP.

Что забрать с собой

• GROUP BY (a, b) даёт только листья — комбинации значений; подытоги и общий итог обычный GROUP BY не считает.
• ROLLUP (a, b) добавляет иерархические подытоги: по каждому a (с свёрнутым b) плюс общий итог (свёрнуты обе колонки).
• CUBE (a, b) добавляет все комбинации свёрнутости — в том числе поперечные подытоги по b через все a, которых у ROLLUP нет.
• GROUPING SETS ((a), (b), ()) перечисляет ровно нужные срезы — без листьев и без лишних строк; ROLLUP и CUBE это сахар над ним.
• grouping(col) = 1, если колонка в строке свёрнута (NULL-подытог), и 0, если это реальное значение — так отличают итог от настоящего NULL и сортируют итоги в конец.

На этом модуль 08 «Аналитика и LATERAL» завершён. Ты прошёл весь аналитический инструментарий Postgres: оконные функции, которые считают сводку, не схлопывая строки (OVER (...)); ранжирование (row_number, rank, dense_rank) для «топ-N в группе»; lag/lead и оконные фреймы для сравнения строки с её соседями и накопительных итогов; рекурсивные CTE (WITH RECURSIVE) для обхода деревьев и графов; LATERAL, где правый источник в JOIN смотрит на текущую строку левого; и теперь GROUPING SETS/ROLLUP/CUBE — субитоги и общий итог за один проход. Всё это — про то, как читать данные под аналитику.

Дальше — модуль 09 «Запись, события и серверная логика», и фокус смещается с чтения на запись. Там продвинутая запись: MERGE для upsert-логики одним оператором и COPY для массовой загрузки; рабочая очередь поверх таблицы через FOR UPDATE SKIP LOCKED, чтобы несколько воркеров разбирали задания, не блокируя друг друга; паттерн transactional outbox, который атомарно фиксирует бизнес-данные и событие для внешнего мира (тот самый мост к Kafka); LISTEN/NOTIFY для лёгких уведомлений между сессиями; и триггеры — серверная логика, которую база выполняет сама на вставку, обновление и удаление.