Что такое GROUPING SETS, ROLLUP и CUBE
На этой странице
Эти расширения GROUP BY считают несколько уровней группировки за один запрос. GROUPING SETS ((a, b), (a), ()) — явный список наборов: детализация, подытоги по a и общий итог. ROLLUP (a, b) — иерархические подытоги: (a, b), (a), () — например «год → месяц → итог». CUBE (a, b) — все комбинации: (a, b), (a), (b), (). В строках подытогов «свёрнутые» столбцы равны NULL; чтобы отличить их от настоящих NULL в данных, используют функцию GROUPING(col) — она возвращает 1 для агрегированного уровня. Это замена нескольких запросов, склеенных через UNION ALL.
Вопрос
Что такое GROUPING SETS, ROLLUP и CUBE и когда они нужны?
ROLLUP
SELECT
country,
city,
sum(total) AS revenue
FROM orders
GROUP BY ROLLUP (country, city)
ORDER BY country NULLS LAST, city NULLS LAST;country | city | revenue
RU | Moscow | 1000
RU | Kazan | 400
RU | NULL | 1400 ← итог по стране
DE | Berlin | 700
DE | NULL | 700
NULL | NULL | 2100 ← общий итогCUBE
SELECT channel, device, count(*) AS visits
FROM sessions
GROUP BY CUBE (channel, device);
-- (channel, device), (channel), (device), ()Полезно для OLAP-подобных отчётов: «по каналам», «по устройствам», «в разрезе обоих» и «всего».
GROUPING SETS
SELECT status, date_trunc('month', created_at) AS month, count(*)
FROM orders
GROUP BY GROUPING SETS (
(status), -- по статусам
(month), -- по месяцам
() -- всего
);Эквивалент трёх запросов с UNION ALL, но за один проход по таблице.
GROUPING() для подписей
SELECT
CASE WHEN GROUPING(country) = 1 THEN 'Всего' ELSE COALESCE(country, 'Не указана') END AS country,
CASE WHEN GROUPING(city) = 1 THEN 'Итого' ELSE COALESCE(city, 'Не указан') END AS city,
sum(total) AS revenue
FROM orders
GROUP BY ROLLUP (country, city);Так строка подытога и строка с реально пустым city не путаются.
Сравнение
| Конструкция | Наборы для (a, b) |
|---|---|
ROLLUP (a, b) | (a, b), (a), () |
CUBE (a, b) | (a, b), (a), (b), () |
GROUPING SETS (...) | Ровно перечисленные |
CUBE от n столбцов даёт 2ⁿ наборов — для 5+ столбцов результат быстро растёт.
Частые ошибки
- Путать
NULLподытога сNULLиз данных — нуженGROUPING(). - Порядок столбцов в
ROLLUPважен:ROLLUP (city, country)даст подытоги по городу, а не по стране. CUBEпо многим столбцам — взрыв числа строк.- Считать подытоги в приложении, когда их может дать база.
Что важно помнить
ROLLUP— иерархические подытоги,CUBE— все комбинации,GROUPING SETS— явный список.GROUPING(col) = 1— строка подытога по этому столбцу.- Один запрос вместо нескольких
UNION ALL.
Мини-шпаргалка
GROUP BY ROLLUP (year, month).GROUP BY CUBE (a, b).GROUP BY GROUPING SETS ((a), (b), ()).GROUPING(col).