re:re:actable
Урок 093 минуты чтения

Что такое 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).

Документация

PostgreSQL: GROUPING SETS, CUBE, and ROLLUP.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам