Зачем сразу несколько разрезов

Допустим, нужен отчёт: «выручка по странам, выручка по годам, и общая выручка». Это три отдельных GROUP BY на одних и тех же данных. Можно написать три запроса и склеить через UNION ALL. Можно один — через GROUPING SETS. Второй вариант короче, обычно быстрее (один проход по данным вместо трёх) и сразу даёт отчёт в нужной форме.

В стандарте SQL для multi-level агрегации есть три конструкции: GROUPING SETS, ROLLUP, CUBE. Все три — это расширения обычного GROUP BY, и все три эквивалентны друг другу через UNION ALL — просто синтаксис компактнее.

GROUPING SETS: явный список разрезов

Запустим:

Что мы видим в результате:

• Строки, где country заполнен, а birth_year = NULL — это разрез «по странам». NULL в birth_year означает «не группировали по году».
• Строки, где birth_year заполнен, а country = NULL — разрез «по году».
• Строка, где оба NULL — общий итог (одна группа на всю таблицу, GROUP BY ()).

Проблема: «настоящий NULL» vs «NULL-маркер группировки»

В наших данных у Ирины birth_year = IS NULL. В результате GROUPING SETS мы тоже видим NULL в birth_year. Как отличить «не знаем год рождения» от «не группировали по году»?

Для этого есть специальная функция

Теперь легко прочитать:

• g_country=0, g_year=1 — это разрез «по странам». В birth_year стоит маркер NULL, означающий «эта колонка не активна».
• g_country=1, g_year=0 — разрез «по годам». В country маркер NULL.
• g_country=1, g_year=1 — общий итог. Обе колонки не активны.
• Если бы внутри разреза по году нашёлся настоящий NULL (наша Ирина), у неё было бы g_year=0 и birth_year=NULL — это строка с реальными NULL-данными в составе группы.

Часто полезно завернуть это в CASE:

ROLLUP: иерархия с подытогами

ROLLUP — это GROUPING SETS с иерархическими подытогами. ROLLUP (a, b, c) означает: «дай мне разрезы (a, b, c), (a, b), (a) и ()». То есть «отбрасывай по одной колонке справа налево».

Это идеальная форма для отчёта вида «по стране, с подытогами и общим итогом» — типичный финансовый или маркетинговый отчёт.

CUBE: все возможные комбинации

CUBE (a, b, c) — это GROUPING SETS со всеми возможными подмножествами аргументов. То есть для CUBE (a, b) это (a, b), (a), (b), (). Для трёх колонок — 2³ = 8 подмножеств.

Сравни с ROLLUP: у ROLLUP нет строк типа (NULL, 1990) — потому что в иерархии «country → birth_year» нельзя «спрыгнуть» через country. CUBE даёт все комбинации, включая такие.

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

Производительность: один проход, не три

Если бы ты написал три отдельных запроса с UNION ALL, PostgreSQL прошёл бы по таблице три раза. С GROUPING SETS он делает это за один проход с использованием специального оператора плана MixedAggregate или HashAggregate с несколькими наборами ключей. На большой таблице разница может быть 3–5×.

Проверь сам через EXPLAIN:

Ты увидишь что-то вроде MixedAggregate или HashAggregate Group Key: с тремя ключами. Это значит — один Seq Scan + одна агрегация в памяти.

Тонкость: ORDER BY и сортировка подытогов

Подытоги (строки с NULL-маркерами) часто хочется ставить последними в группе или первыми. По умолчанию ORDER BY ставит NULL в конец для ASC. Часто пишут ORDER BY GROUPING(col), col, чтобы сначала шли детальные строки группы, а в конце — её подытог.

Чек-лист

• GROUPING SETS ((a), (b), ()) — явный список разрезов. Эквивалентно UNION ALL трёх GROUP BY, но один проход по данным.
• ROLLUP (a, b, c) = GROUPING SETS ((a,b,c), (a,b), (a), ()). Иерархические подытоги.
• CUBE (a, b) = все 2² подмножества. Все комбинации измерений.
• Функция GROUPING(col) возвращает 1, если колонка в этой строке — маркер группировки (не активна), и 0, если активна. Используй для отличения «настоящий NULL» от «NULL-маркер».
• Один запрос с GROUPING SETS обычно эффективнее, чем UNION ALL нескольких группировок — оптимизатор делает один проход.
• При сортировке подытогов используй ORDER BY GROUPING(col) для контроля их позиции.