GROUPING SETS, ROLLUP и CUBE в SQL: подытоги и общий итог одним запрос

Q: Как ROLLUP считает иерархию год → месяц → день?

ROLLUP рассчитан на иерархию, где столбцы вложены друг в друга: год содержит месяцы, месяц содержит дни. Он последовательно «схлопывает» столбцы справа налево и на каждом уровне добавляет подытог.

Q: Чем CUBE отличается от ROLLUP?

CUBE строит все возможные комбинации перечисленных столбцов, а не только вложенные. Это то, что нужно, когда измерения независимы и вы хотите резать данные по каждому из них отдельно и вместе.

Q: Когда использовать GROUPING SETS вместо ROLLUP и CUBE?

GROUPING SETS — самый гибкий вариант: вы вручную перечисляете, какие именно наборы группировок вам нужны, и ничего лишнего. Это спасает, когда CUBE дал бы половину ненужных комбинаций, а ROLLUP — не ту иерархию.

Q: Какие ошибки чаще всего ломают такие запросы?

Первая и самая частая — забыть ORDER BY. Без него строки-итоги раскиданы по выдаче в непредсказуемом порядке, и отчёт выглядит хаосом. Всегда сортируйте, и удобно делать это через GROUPING(), чтобы итоги вставали под своими группами. Вторая — путать NULL из данных и NULL от подытога. Если в таблице есть строки с незаполненным region, а вы не используете GROUPING(), то строка «заказы без региона» и строка «итог по всем регионам» станут неотличимы, и суммы поедут. Это классическая скрытая ошибка,

Если нужно в одном отчёте показать выручку по дням, подытоги по месяцам, по годам и общий итог — не пишите пять запросов через UNION ALL. Для этого в SQL есть три расширения GROUP BY: ROLLUP считает иерархические подытоги (год → месяц → день), CUBE — все возможные комбинации измерений, а GROUPING SETS — ровно те наборы группировок, которые вы перечислите руками. Движок делает один проход по таблице и сам дорисовывает строки-итогов, а функция GROUPING() помогает отличить строку-итог от настоящего NULL. Дальше — на живых примерах с таблицей orders.

Для примеров держите в голове простую табличку заказов:

-- orders(order_id, created_at, region, category, amount)
-- одна строка = один оплаченный заказ

Зачем вообще нужны ROLLUP, CUBE и GROUPING SETS?

Классический сценарий: аналитику приносят выгрузку и просят «выручку по регионам, по категориям и заодно общий итог». Первое, что делает большинство — пишет три отдельных GROUP BY и склеивает их через UNION ALL:

SELECT region, NULL AS category, SUM(amount) FROM orders GROUP BY region
UNION ALL
SELECT NULL, category, SUM(amount) FROM orders GROUP BY category
UNION ALL
SELECT NULL, NULL, SUM(amount) FROM orders;

Работает, но каждый SELECT — это отдельное сканирование таблицы. На трёх строчках терпимо, а когда измерений пять и таблица на сотни миллионов строк, вы гоняете один и тот же скан по пять раз. GROUPING SETS и его сокращения ROLLUP/CUBE делают то же самое за один проход: планировщик считает базовую агрегацию один раз и наращивает на неё подытоги. Меньше кода, меньше шансов опечататься в NULL-заглушках, и запрос читается как одно понятное намерение, а не как три склеенных куска.

Все три конструкции ставятся внутрь GROUP BY и отличаются только тем, какой набор комбинаций генерируют.

Как ROLLUP считает иерархию год → месяц → день?

ROLLUP рассчитан на иерархию, где столбцы вложены друг в друга: год содержит месяцы, месяц содержит дни. Он последовательно «схлопывает» столбцы справа налево и на каждом уровне добавляет подытог.

SELECT
    EXTRACT(YEAR  FROM created_at) AS y,
    EXTRACT(MONTH FROM created_at) AS m,
    EXTRACT(DAY   FROM created_at) AS d,
    SUM(amount) AS revenue
FROM orders
GROUP BY ROLLUP (
    EXTRACT(YEAR  FROM created_at),
    EXTRACT(MONTH FROM created_at),
    EXTRACT(DAY   FROM created_at)
)
ORDER BY y, m, d;

ROLLUP (y, m, d) разворачивается в четыре набора группировок: (y, m, d), (y, m), (y) и () — пустой набор, то есть общий итог по всей таблице. В результате вы получаете строки по каждому дню, под ними строку-подытог за месяц (в ней d будет NULL), под месяцами — подытог за год (m и d равны NULL), а в самом низу — одну строку общего итога, где NULL во всех трёх столбцах.

Ключевое свойство: ROLLUP не даёт все комбинации, только вложенные. Он не посчитает «сумму по всем дням 15-го числа независимо от месяца» — для такого нужен CUBE или явный GROUPING SETS. Если у вас честная иерархия (страна → регион → город, год → квартал → месяц), ROLLUP — самый компактный и правильный инструмент.

Чем CUBE отличается от ROLLUP?

CUBE строит все возможные комбинации перечисленных столбцов, а не только вложенные. Это то, что нужно, когда измерения независимы и вы хотите резать данные по каждому из них отдельно и вместе.

SELECT
    region,
    category,
    SUM(amount) AS revenue
FROM orders
GROUP BY CUBE (region, category)
ORDER BY region, category;

CUBE (region, category) даёт четыре набора: (region, category), (region), (category) и (). В отчёте появятся: выручка по каждой паре регион-категория, подытог по каждому региону (по всем категориям), подытог по каждой категории (по всем регионам) и общий итог.

Разница с ROLLUP — именно в наборе (category). ROLLUP (region, category) его не создаёт, потому что считает категорию вложенной в регион; CUBE создаёт, потому что считает измерения равноправными. Отсюда практическое правило: сколько строк-итогов сгенерируется. ROLLUP от n столбцов даёт n + 1 набор группировок, а CUBE — 2 в степени n. Для двух столбцов это 3 против 4, а для пяти — уже 6 против 32. На широком CUBE число подытогов растёт лавинообразно, и это стоит держать в уме на больших таблицах: движок хоть и сканирует данные один раз, но комбинаций материализует много.

Обе конструкции — просто сокращения. ROLLUP (a, b) эквивалентен GROUPING SETS ((a, b), (a), ()), а CUBE (a, b) — GROUPING SETS ((a, b), (a), (b), ()). Понимая это, вы всегда можете разложить «магию» в явный список.

Когда использовать GROUPING SETS вместо ROLLUP и CUBE?

GROUPING SETS — самый гибкий вариант: вы вручную перечисляете, какие именно наборы группировок вам нужны, и ничего лишнего. Это спасает, когда CUBE дал бы половину ненужных комбинаций, а ROLLUP — не ту иерархию.

SELECT
    region,
    category,
    SUM(amount)  AS revenue,
    COUNT(*)     AS orders_cnt
FROM orders
GROUP BY GROUPING SETS (
    (region),      -- срез по регионам
    (category),    -- срез по категориям
    ()             -- общий итог
)
ORDER BY region, category;

Здесь я сознательно не запрашиваю пару (region, category) — она мне в этом отчёте не нужна, а на большой таблице это заметная экономия. CUBE посчитал бы её принудительно. То есть GROUPING SETS — это про «отдай мне ровно эти три среза и не трать ресурсы на остальное».

Ещё один рабочий приём — комбинировать конструкции внутри GROUPING SETS. Можно вкладывать ROLLUP в набор, чтобы получить иерархию по времени в разрезе региона:

GROUP BY GROUPING SETS (
    (region, ROLLUP(EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)))
)

Практически GROUPING SETS я выбираю всегда, когда точно знаю нужные срезы и хочу контролировать нагрузку. ROLLUP/CUBE беру как удобные шорткаты для типовых форм отчёта.

Как отличить строку-итог от настоящего NULL через GROUPING()?

Проблема всех трёх конструкций: подытоги помечаются значением NULL в «схлопнутых» столбцах. Но NULL может быть и в самих данных — например, у части заказов region не заполнен. Как отличить «это итог по всем регионам» от «это заказы с неизвестным регионом»? Именно для этого есть функция GROUPING().

GROUPING(col) возвращает 1, если столбец был свёрнут в этой строке (то есть NULL здесь — искусственный, от подытога), и 0, если строка — обычная группа и NULL в ней настоящий, из данных.

SELECT
    CASE WHEN GROUPING(region)   = 1 THEN 'Все регионы'   ELSE region   END AS region,
    CASE WHEN GROUPING(category) = 1 THEN 'Все категории' ELSE category END AS category,
    SUM(amount) AS revenue
FROM orders
GROUP BY CUBE (region, category)
ORDER BY GROUPING(region), region, GROUPING(category), category;

Теперь строки-итогов подписаны человеческими метками, а NULL из данных остаётся NULL. Обратите внимание и на ORDER BY через GROUPING() — это удобный способ прижать итоги к нужному месту, а не разбрасывать их по отчёту.

GROUPING() умеет принимать несколько столбцов сразу и возвращать битовую маску: GROUPING(region, category) даёт число от 0 до 3, где старший бит отвечает за region, младший — за category. Значение 0 — детальная строка, 3 — общий итог, 1 и 2 — частичные подытоги. Это пригодится, когда нужно в одном условии отфильтровать конкретный уровень агрегации, например оставить только строки общего итога через HAVING GROUPING(region, category) = 3.

Как это работает в PostgreSQL и ClickHouse?

Синтаксис похож, но диалекты расходятся в деталях, и на собеседовании про это любят спросить.

PostgreSQL поддерживает все три конструкции с версии 9.5 в стандартном виде — ровно так, как в примерах выше: GROUP BY ROLLUP(...), CUBE(...), GROUPING SETS(...) и функция GROUPING(). Свёрнутые столбцы получают честный NULL, поэтому GROUPING() действительно нужен, чтобы их отличить.

ClickHouse идёт другим путём. Здесь ROLLUP и CUBE — это модификаторы после GROUP BY, а не функции:

-- ClickHouse
SELECT region, category, sum(amount) AS revenue
FROM orders
GROUP BY region, category WITH CUBE
ORDER BY region, category;

GROUPING SETS в ClickHouse тоже есть и пишется как в стандарте — GROUP BY GROUPING SETS ((region), (category), ()). Но есть важный подвох: в подытогах ClickHouse по умолчанию подставляет не NULL, а значение по умолчанию для типа столбца — пустую строку для строк, 0 для чисел. Поэтому визуально итог и «настоящий ноль» сливаются ещё сильнее, и функция GROUPING() (она в ClickHouse есть) становится не роскошью, а необходимостью. Если хотите именно NULL в подытогах, столбцы должны быть Nullable. Про особенности движка я подробнее разбирал в отдельном материале — загляните в блог про ClickHouse для аналитика.

Для полноты: MySQL умеет только WITH ROLLUP (без CUBE и GROUPING SETS), а функция GROUPING() там появилась в 8.0. SQLite этих расширений не знает вовсе — там придётся вернуться к UNION ALL. Так что если вы пишете переносимый SQL, GROUPING SETS — не самый безопасный выбор, и это стоит проверять под конкретный движок. Погонять запросы на живом PostgreSQL прямо в браузере можно в SQL-тренажёре — там как раз PostgreSQL 16.

Какие ошибки чаще всего ломают такие запросы?

Первая и самая частая — забыть ORDER BY. Без него строки-итоги раскиданы по выдаче в непредсказуемом порядке, и отчёт выглядит хаосом. Всегда сортируйте, и удобно делать это через GROUPING(), чтобы итоги вставали под своими группами.

Вторая — путать NULL из данных и NULL от подытога. Если в таблице есть строки с незаполненным region, а вы не используете GROUPING(), то строка «заказы без региона» и строка «итог по всем регионам» станут неотличимы, и суммы поедут. Это классическая скрытая ошибка, которую замечают, только когда итог не сходится.

Третья — выражение в GROUP BY не совпадает с выражением в SELECT. Если группируете по date_trunc('month', created_at), то и в SELECT должно быть ровно оно же, иначе получите либо ошибку, либо неверную разбивку.

Четвёртая — недооценить взрыв комбинаций у CUBE. Пять столбцов — это 32 набора группировок. На большой таблице это ощутимая нагрузка, и часто вместо CUBE достаточно GROUPING SETS с тремя нужными срезами.

И пятая — фильтрация не на том уровне. WHERE отрабатывает до группировки и режет исходные строки, а отфильтровать конкретный уровень итогов можно только через HAVING с условием на GROUPING(). Логику WHERE против HAVING я разбирал в посте про GROUP BY и HAVING — если она пока не в пальцах, начните с неё.

Что стоит запомнить

ROLLUP — для иерархий и «нарастающих» итогов, CUBE — когда нужны все комбинации независимых измерений, GROUPING SETS — когда вы точно знаете нужные срезы и не хотите платить за лишние. Все три экономят один и тот же скан таблицы вместо нескольких UNION ALL, а GROUPING() спасает от путаницы между итогом и NULL из данных. Держите в голове диалектные различия: PostgreSQL следует стандарту, ClickHouse кладёт значения по умолчанию вместо NULL, MySQL умеет только ROLLUP, SQLite — ничего.

Дальше — руками. Соберите отчёт по выручке с подытогами на задачах SQL-тренажёра, прогоните типовые формулировки из подборки вопросов с собеседований, а если хотите системно, а не наскоками, — пройдите базовый курс SQL с нуля и разберите разбор на реальных бизнес-кейсах. Синтаксис под рукой всегда есть в SQL-справочнике. Полный доступ ко всем задачам, кейсам и AI-разбору решений открывается в Pro — это заметно ускоряет подготовку к собеседованию, если готовитесь всерьёз.