SQL CUBE: Every Grouping Combination in One Pass for Cross-Tab Reports

Бизнес просит «таблицу по регионам, по продуктам, по их пересечению и общий итог в придачу» — и неопытный разработчик тут же лепит четыре SELECT, склеенных через UNION ALL. База читает таблицу по разу на каждый блок, запрос разрастается, а малейшая правка фильтра тянет за собой синхронную правку во всех четырёх местах. CUBE закрывает эту боль одной строкой: это расширение GROUP BY, которое за один проход по данным считает агрегаты сразу по всем комбинациям перечисленных столбцов — по каждому измерению отдельно, по их пересечениям и по всему датасету целиком. По сути это готовый движок для cross-tab отчётов.

Что вычисляет CUBE

Канонический пример — продажи в разрезе региона и продукта.

SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product);

GROUP BY CUBE (region, product) разворачивается сразу в четыре группировки:

• (region, product) — детальные ячейки cross-tab;
• (region) — итог по каждому региону (любой продукт);
• (product) — итог по каждому продукту (любой регион);
• () — пустой набор, то есть общий итог по всем строкам.

На n столбцов CUBE порождает 2^n группировок: два столбца — 4 набора, три — 8, четыре — уже 16. Здесь же пролегает граница с ROLLUP: тот сворачивает столбцы строго справа налево, выстраивая иерархию из n + 1 уровня, тогда как CUBE перебирает все подмножества без исключения. Разница видна на пальцах: CUBE (a, b) отдаёт и (a), и (b), а ROLLUP (a, b) — только (a), но никак не (b).

Чтение NULL-подытогов и GROUPING()

А вот и первые грабли. Подытоговые строки помечаются значением NULL в тех столбцах, что были свёрнуты. Беда в том, что если NULL уже встречается в самих данных — скажем, продажа без указанного региона, — вы при всём желании не отличите настоящий NULL от маркера итога: глазами они неразличимы. Развязывает этот узел функция GROUPING(): она возвращает 1, когда столбец в этой строке был свёрнут, и 0, когда перед вами реальное значение.

SELECT
  GROUPING(country) AS g_country,
  GROUPING(status)  AS g_status,
  country,
  status,
  SUM(amount)       AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY CUBE (country, status)
ORDER BY g_country, country, g_status, status;

На практике удобнее не таскать сырые флаги в отчёт, а сразу подменять их читаемыми подписями — тогда таблица говорит сама за себя и не требует сносок:

SELECT
  CASE WHEN GROUPING(country) = 1 THEN 'All countries' ELSE country END AS country,
  CASE WHEN GROUPING(status)  = 1 THEN 'All statuses'  ELSE status  END AS status,
  SUM(amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY CUBE (country, status)
ORDER BY country NULLS LAST, status NULLS LAST;

Та же GROUPING() помогает выдернуть из куба ровно один срез: достаточно отфильтровать строки по её битам в HAVING. Например, оставить только итоги по странам (продукт свёрнут, страна — нет):

SELECT country, SUM(amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY CUBE (country, status)
HAVING GROUPING(country) = 0 AND GROUPING(status) = 1;

CUBE на реальном HR-примере

Матричные отчёты ложатся на CUBE как влитые. Возьмём зарплаты сотрудников в разрезе отдела и того, есть ли над человеком руководитель:

SELECT
  COALESCE(dept, 'ALL DEPTS')                       AS dept,
  CASE WHEN GROUPING(manager_id IS NULL) = 1
       THEN 'ALL'
       WHEN manager_id IS NULL THEN 'top-level'
       ELSE 'has manager' END                       AS reports_to,
  COUNT(*)                                           AS headcount,
  ROUND(AVG(salary), 2)                              AS avg_salary
FROM employees
GROUP BY CUBE (dept, (manager_id IS NULL))
ORDER BY dept NULLS LAST;

Один запрос разом выдаёт среднюю зарплату по каждой паре «отдел + уровень», по каждому отделу целиком, по уровню в масштабе всей компании и общий итог в придачу. Без CUBE тот же результат пришлось бы городить из четырёх SELECT через UNION ALL — со всеми вытекающими повторениями и риском рассинхрона.

CUBE vs ROLLUP vs GROUPING SETS

Под капотом все три — синтаксический сахар над GROUPING SETS. Стоит один раз разглядеть их разложение, и вы получаете полный контроль над тем, какие именно срезы запрашиваете:

• CUBE (a, b) = GROUPING SETS ((a, b), (a), (b), ()) — все комбинации;
• ROLLUP (a, b) = GROUPING SETS ((a, b), (a), ()) — только иерархия справа налево;
• нужен нестандартный набор? Выпишите его руками: GROUPING SETS ((a, b), (b), ()).

Выбор прост. Тянитесь за ROLLUP, когда столбцы образуют естественную иерархию (год → месяц → день) и боковые подытоги не нужны. Берите CUBE, когда измерения независимы и вам нужна полная матрица. И держите в голове главный подводный камень: CUBE по многим столбцам взрывается экспоненциально — пять столбцов это уже 32 группировки, и на больших таблицах такой запрос обходится дорого. Ограничивайте число измерений или выписывайте нужные срезы явными GROUPING SETS.

Пара слов о диалектах. CUBE, ROLLUP и GROUPING SETS живут в PostgreSQL начиная с 9.5. В MySQL доступен только GROUP BY ... WITH ROLLUP — ни CUBE, ни явных GROUPING SETS там нет, и полную матрицу приходится собирать по старинке через UNION ALL. У ClickHouse есть модификатор WITH CUBE, но порядок строк и обработка NULL расходятся со стандартом — так что при переносе отчёта проверяйте в первую очередь именно итоговые строки.