SQL код скопирован в буфер обмена

SQLtest.online

Практика

Уроки

Тест

EN PT FR

Урок 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.