SQL ROLLUP, CUBE и GROUPING SETS

Урок 4.5 · Время чтения: ~9 мин · Задачи для практики ↓

ROLLUP, CUBE и GROUPING SETS расширяют GROUP BY: помимо обычных групп они возвращают в том же результате строки промежуточных итогов и общий итог. В этом уроке вы узнаете, чем отличаются эти три конструкции, как использовать WITH ROLLUP в MySQL и как функция GROUPING() отделяет строки итогов от настоящих NULL-значений.

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

Как устроены уровни группировки

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

В строке промежуточного итога «свёрнутые» столбцы содержат NULL.

WITH ROLLUP в MySQL и MariaDB

MySQL и MariaDB поддерживают ROLLUP в собственном синтаксисе: WITH ROLLUP после списка GROUP BY.

SELECT YEAR(payment_date) AS pay_year,
       MONTH(payment_date) AS pay_month,
       SUM(amount) AS revenue
FROM payment
GROUP BY YEAR(payment_date), MONTH(payment_date) WITH ROLLUP;

Результат: выручка за каждый месяц (с мая по август 2005 года и февраль 2006 года), строка промежуточного итога за каждый год, где pay_month равен NULL, и строка общего итога, где оба столбца равны NULL.

Порядок столбцов в GROUP BY задаёт иерархию: промежуточные итоги добавляются для каждого значения первого столбца, а первым «сворачивается» последний столбец.

Как отличить строки итогов с помощью GROUPING()

В строке итога свёрнутые столбцы равны NULL. Но NULL может встречаться и в самих данных, и тогда промежуточный итог не отличить от группы строк с пустым значением. Эту проблему решает функция GROUPING(column): она возвращает 1, если в этой строке столбец свёрнут, и 0, если значение взято из данных.

GROUPING() поддерживают MySQL 8.0+, PostgreSQL, SQL Server и Oracle. В MariaDB её нет: там можно только проверять на NULL.

-- MySQL 8.0+
SELECT CASE
           WHEN GROUPING(YEAR(payment_date)) = 1 THEN 'Grand total'
           WHEN GROUPING(MONTH(payment_date)) = 1 THEN 'Year total'
           ELSE 'Month'
       END AS row_type,
       YEAR(payment_date) AS pay_year,
       MONTH(payment_date) AS pay_month,
       SUM(amount) AS revenue
FROM payment
GROUP BY YEAR(payment_date), MONTH(payment_date) WITH ROLLUP;

Результат: те же восемь строк, каждая помечена как Month, Year total или Grand total.

Стандартные ROLLUP, CUBE и GROUPING SETS

PostgreSQL, SQL Server и Oracle поддерживают стандартный синтаксис всех трёх конструкций. Примеры ниже выполняются на базе AdventureWorks в SQL Server. Товары группируются по году начала продаж и по цвету; у некоторых товаров цвета нет, поэтому в Color встречаются настоящие NULL-значения.

ROLLUP

SELECT YEAR(SellStartDate) AS start_year,
       Color,
       COUNT(*) AS products,
       GROUPING(Color) AS is_year_total
FROM Product
GROUP BY ROLLUP (YEAR(SellStartDate), Color)
ORDER BY GROUPING(YEAR(SellStartDate)), start_year, GROUPING(Color), Color;

Результат: количество товаров для каждого года и цвета, промежуточный итог за каждый год (is_year_total = 1) и общий итог последней строкой. За 2006 год есть две строки с Color = NULL: строка с is_year_total = 0 - это группа из 18 товаров без цвета, а строка с is_year_total = 1 - итог года.

CUBE

CUBE добавляет итоги для каждой комбинации столбцов, включая итоги по цвету за все годы:

SELECT YEAR(SellStartDate) AS start_year,
       Color,
       COUNT(*) AS products
FROM Product
GROUP BY CUBE (YEAR(SellStartDate), Color)
ORDER BY GROUPING(YEAR(SellStartDate)), start_year, GROUPING(Color), Color;

Результат: всё, что возвращает ROLLUP, плюс итог по каждому цвету за все годы.

Для n столбцов CUBE даёт 2ⁿ уровней группировки, поэтому результат быстро растёт. Используйте его для небольшого числа измерений.

GROUPING SETS

GROUPING SETS возвращает только перечисленные уровни. Например, итоги по годам и итоги по цветам без детальных строк:

SELECT YEAR(SellStartDate) AS start_year,
       Color,
       COUNT(*) AS products
FROM Product
GROUP BY GROUPING SETS ((YEAR(SellStartDate)), (Color), ())
ORDER BY GROUPING(YEAR(SellStartDate)), start_year, Color;

Результат: по строке на каждый год, по строке на каждый цвет и общий итог.

ROLLUP и CUBE - это сокращения: ROLLUP (a, b) равносилен GROUPING SETS ((a, b), (a), ()).

Поддержка в СУБД

Если конструкция не поддерживается, объедините несколько запросов с GROUP BY через UNION ALL - его вы изучите в уроке 5.10.

Что когда использовать

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

Всегда добавляйте ORDER BY, чтобы строки итогов оказывались в предсказуемом месте, и помечайте их с помощью GROUPING() и CASE.

Ключевые выводы:

• ROLLUP, CUBE и GROUPING SETS добавляют к результату GROUP BY строки промежуточных и общего итогов.
• ROLLUP (a, b) даёт (a, b), (a), (); CUBE (a, b) добавляет ещё (b).
• GROUPING SETS возвращает ровно перечисленные уровни.
• GROUPING(column) = 1 отмечает свёрнутый столбец и отделяет строки итогов от настоящих NULL-значений.
• MySQL и MariaDB поддерживают только WITH ROLLUP; в MariaDB нет GROUPING().

Часто задаваемые вопросы

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

ROLLUP создаёт иерархические промежуточные итоги справа налево: для (year, month) он добавляет итоги по годам и общий итог. CUBE создаёт итоги для всех комбинаций, поэтому добавляет ещё итоги по месяцам за все годы.

Как добавить строку итога к запросу с GROUP BY?

В MySQL и MariaDB добавьте WITH ROLLUP после столбцов GROUP BY. В PostgreSQL, SQL Server и Oracle используйте GROUP BY ROLLUP (columns) или GROUP BY GROUPING SETS ((columns), ()), если нужны только детализация и общий итог.

Откуда в результатах ROLLUP значения NULL?

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