Краткий ответ

Используйте GROUPING SETS: он позволяет явно задать несколько уровней группировки в одном агрегатном запросе. Для иерархических итогов также подходят ROLLUP, а для всех комбинаций измерений — CUBE.

GROUPING SETS удобен, когда нужны конкретные срезы, например итоги по регионам, по годам и общий итог, но не нужны все возможные комбинации этих признаков.

Исторический контекст

Обычный GROUP BY формирует результат только на одном уровне детализации. Для отчёта с несколькими уровнями итогов раньше приходилось выполнять несколько агрегатных запросов и объединять их через UNION ALL, что усложняло поддержку и повышало риск различий в фильтрах и формулах.

Расширения группировки GROUPING SETS, ROLLUP и CUBE предназначены для аналитических отчётов, где требуется получить несколько гранулярностей данных за один проход логики агрегации.

Постановка проблемы

Предположим, отчёту нужны одновременно суммы по регионам, суммы по годам и общий итог. Несколько отдельных запросов могут случайно использовать разные условия, соединения или выражения, из-за чего части отчёта перестанут быть сопоставимыми.

Простой GROUP BY не решает задачу: он выдаёт либо разбивку по регионам, либо по годам, но не оба уровня сразу. Неправильный выбор между GROUPING SETS, ROLLUP и CUBE также может создать лишние строки и увеличить стоимость вычислений.

Подробное решение

GROUPING SETS принимает перечень независимых наборов группировочных столбцов. Пустой набор () означает агрегацию по всем строкам, то есть общий итог.

SELECT
    region,
    year,
    SUM(amount) AS total_amount
FROM sales
GROUP BY GROUPING SETS (
    (region),
    (year),
    ()
);

В результате появятся строки с итогами по каждому региону, строки с итогами по каждому году и одна строка общего итога. Значения отсутствующих в конкретной группировке столбцов обычно представлены как NULL; это не обязательно означает, что исходный столбец содержал NULL.

Для различения настоящего NULL и значения, добавленного механизмом итогов, применяют функцию GROUPING или аналогичную ей функцию конкретной СУБД. Это особенно важно при отображении отчёта и последующей обработке результатов.

ROLLUP задаёт иерархию: например, регион → год → общий итог. Он автоматически формирует промежуточные итоги по префиксам указанного порядка. CUBE строит все комбинации измерений, поэтому при большом числе столбцов может породить много групп и оказаться существенно дороже.

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

Ситуация из практики

В отчёте о продажах требовались итоги по регионам, по годам и общий итог. Команда рассматривала три варианта: три отдельных запроса с UNION ALL, ROLLUP и GROUPING SETS.

UNION ALL был понятен, но требовал дублирования фильтров и агрегатных выражений. ROLLUP был короче, однако задавал иерархию, которая не отражала независимые требования отчёта и могла добавить ненужные промежуточные уровни.

Выбрали GROUPING SETS, поскольку он точно перечислял нужные уровни. В результате логика фильтрации и вычисления суммы находилась в одном месте, а отчёт получал только необходимые строки без лишних комбинаций.

Что кандидаты часто упускают

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

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

Почему порядок столбцов важен для ROLLUP?

ROLLUP строит итоги по префиксам списка группировки. Иерархии регион, год и год, регион дают разные промежуточные итоги: в первом случае естественным уровнем детализации будет год внутри региона, во втором — регион внутри года. Поэтому порядок должен отражать бизнес-иерархию, а не быть произвольным.

Как отличить общий итог от строки с реальным NULL в измерении?

Проверять только IS NULL недостаточно: NULL может присутствовать в исходных данных, а может быть создан как признак уровня итога. Нужно использовать GROUPING для соответствующего столбца и проверять, был ли он исключён из конкретного набора группировки. Это позволяет корректно подписывать строки отчёта и не смешивать реальные данные с синтетическими итогами.