Урок 4.5: Продвинутая агрегация с ROLLUP, CUBE и GROUPING SETS в SQL

Когда отчетов становится больше, обычного GROUP BY часто уже недостаточно. Например, нужно одновременно получить:

• детализацию по статусам заказов и клиентам;
• промежуточные итоги по статусам;
• итог по клиентам;
• общий итог по всему набору данных.

Можно написать несколько отдельных запросов и объединить их через UNION ALL, но это громоздко и сложнее поддерживать. Для таких задач в SQL используют модификаторы выражения группировки: ROLLUP, CUBE и GROUPING SETS.

Корректно говорить так: эти модификаторы дополняют результат агрегированной выборки строками с более высокой степенью агрегации (подитоги и общий итог).

Важно: в этом уроке все практические примеры приведены для SQL Server (AdventureWorks).

Замечание по синтаксису: ROLLUP, CUBE, GROUPING SETS и GROUPING() в примерах ниже показаны в синтаксисе SQL Server. В MySQL функциональность заметно ограниченнее, а синтаксис частично отличается (например, обычно используют WITH ROLLUP, а CUBE и GROUPING SETS в классическом виде могут быть недоступны).

В этом уроке разберем:

• чем отличаются ROLLUP, CUBE и GROUPING SETS;
• как строятся промежуточные и итоговые строки;
• как отличать итоговые строки от обычных с помощью GROUPING().

Почему это важно

Продвинутая агрегация помогает:

• строить многоуровневые отчеты одним запросом;
• уменьшать дублирование SQL-кода;
• получать согласованные итоги (детали, подитоги, grand total).
• дополнять результат детализации строками более высокого уровня агрегации.

Базовая идея

Пусть у нас есть продажи из таблицы SalesOrderHeader с измерениями Status, CustomerID и метрикой TotalDue.

Обычный GROUP BY возвращает только один уровень группировки. Расширенные конструкции возвращают сразу несколько уровней.

ROLLUP: иерархические итоги

ROLLUP строит иерархию справа налево по списку колонок.

Синтаксис

GROUP BY ROLLUP (col1, col2, col3)

Будут сформированы уровни:

• (col1, col2, col3) - детализация;
• (col1, col2) - подитог по col3;
• (col1) - подитог по col2 и col3;
• () - общий итог.

Пример: сумма заказов по статусам и клиентам

SELECT
    Status,
    CustomerID,
    SUM(TotalDue) AS total_amount
FROM SalesOrderHeader
GROUP BY ROLLUP (Status, CustomerID)
ORDER BY Status, CustomerID;

Результат:

• строки по каждой паре Status + CustomerID;
• подитог по каждому Status;
• общий итог по всей таблице.

CUBE: все комбинации измерений

CUBE строит агрегаты по всем возможным комбинациям колонок.

Синтаксис

GROUP BY CUBE (col1, col2)

Для двух колонок будут уровни:

• (col1, col2);
• (col1);
• (col2);
• ().

Для трех колонок комбинаций уже $2^3 = 8$, поэтому результат может сильно вырасти.

Пример: сумма заказов по статусу и клиенту во всех срезах

SELECT
    Status,
    CustomerID,
    SUM(TotalDue) AS total_amount
FROM SalesOrderHeader
GROUP BY CUBE (Status, CustomerID)
ORDER BY Status, CustomerID;

Результат: помимо детализации и общего итога, вы получите отдельно:

• итоги по каждому Status;
• итоги по каждому CustomerID.

GROUPING SETS: точный контроль уровней

GROUPING SETS позволяет явно задать только те группировки, которые нужны.

Синтаксис

GROUP BY GROUPING SETS (
    (col1, col2),
    (col1),
    ()
)

Пример: только нужные уровни без лишних комбинаций

SELECT
    Status,
    CustomerID,
    SUM(TotalDue) AS total_amount
FROM SalesOrderHeader
GROUP BY GROUPING SETS (
    (Status, CustomerID),
    (Status),
    ()
)
ORDER BY Status, CustomerID;

Этот запрос эквивалентен набору из нескольких GROUP BY ... UNION ALL ..., но записан компактнее и обычно оптимизируется лучше.

Как отличать итоговые строки: GROUPING()

В итоговых строках агрегирования значения измерений часто становятся NULL. Проблема в том, что в данных тоже могут быть реальные NULL.

Функция GROUPING(column) помогает различить эти случаи:

• 0 - это обычное значение из данных;
• 1 - значение сгенерировано агрегированием (уровень итога).

Пример с метками уровня

SELECT
    Status,
    CustomerID,
    SUM(TotalDue) AS total_amount,
    GROUPING(Status) AS g_status,
    GROUPING(CustomerID) AS g_customer
FROM SalesOrderHeader
GROUP BY ROLLUP (Status, CustomerID)
ORDER BY Status, CustomerID;

Практический прием для отчетов:

CASE
    WHEN GROUPING(Status) = 1 AND GROUPING(CustomerID) = 1 THEN 'GRAND TOTAL'
    WHEN GROUPING(CustomerID) = 1 THEN 'STATUS SUBTOTAL'
    ELSE 'DETAIL'
END AS row_type

Когда что использовать

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

Практические рекомендации

• Всегда проверяйте размер результата: CUBE может резко увеличить число строк.
• Для читаемости маркируйте строки типа DETAIL, SUBTOTAL, GRAND TOTAL.
• Добавляйте явный ORDER BY, чтобы итоги были расположены предсказуемо.
• Если нужно фильтровать агрегаты, комбинируйте с HAVING.

Пример для MySQL

Ниже пример для MySQL на таблице payment с подитогами через WITH ROLLUP:

SELECT
    staff_id,
    customer_id,
    SUM(amount) AS total_amount
FROM
    payment
GROUP BY
    staff_id, customer_id WITH ROLLUP
ORDER BY
    GROUPING(staff_id),
    staff_id,
    GROUPING(customer_id),
    customer_id;

В этом запросе:

• детализация идет по парам staff_id + customer_id;
• WITH ROLLUP добавляет подитог по каждому staff_id и общий итог;
• ORDER BY GROUPING(...) выводит строки в удобном порядке: детали, подитоги, затем grand total.

Что важно учитывать в MySQL:

• WITH ROLLUP дает иерархические итоги, но не полный эквивалент CUBE/GROUPING SETS.
• Для сложных наборов группировок часто приходится использовать несколько запросов и UNION ALL.
• Если в вашей версии MySQL нет GROUPING(), сортировку и маркировку итогов обычно делают через проверку NULL в сгруппированных колонках.

Практическое применение

1. Отчет по сумме заказов в разрезе статусов и клиентов: ROLLUP (Status, CustomerID) дает детализацию по статусу и клиенту, подитоги по статусам и общий итог.

2. Многомерная аналитика продаж: CUBE (Status, CustomerID) дает все комбинации срезов по статусам и клиентам.

3. Кастомный отчет по суммам заказов: GROUPING SETS позволяет оставить только уровни «детали + подитог отдела + общий итог».

Основные выводы из этого урока

• ROLLUP, CUBE и GROUPING SETS расширяют возможности GROUP BY.
• ROLLUP создает иерархические итоги, CUBE - все комбинации, GROUPING SETS - только заданные уровни.
• GROUPING() нужен для корректной интерпретации итоговых строк.
• Эти инструменты позволяют строить гибкие аналитические отчеты по суммам заказов в одном запросе.

Освоив эти конструкции, вы сможете проектировать более мощные SQL-отчеты без сложных цепочек UNION ALL.