Промежуточные итоги в SQL без костылей: как модификатор WITH ROLLUP строит древовидные отчёты в одном запросеЗаметка · дайджест за 15 ноября
Трёхмерная изометрическая инфографика иерархической агрегации данных продаж с промежуточными суммами и общим итогом
Заметка2 раздела≈ 4 мин

Промежуточные итоги в SQL без костылей: как модификатор WITH ROLLUP строит древовидные отчёты в одном запросе

Сколько отдельных SQL-запросов ты обычно склеиваешь воедино, когда для дашборда или бухгалтерии нужно выгрузить таблицу продаж сразу с разбивкой по регионам, промежуточными суммами по каждому товару и строчкой «Итого» в самом низу? Кажется, что реляционная база не умеет отдавать разные уровни обобщения в одной плоской выдаче.

На самом деле стандарт SQL решает эту задачу внутри самого движка. Я разобрал пример с таблицей продаж из канала SQL Portal и сверил механику с документацией MySQL 8.0: модификатор WITH ROLLUP вычисляет детальные строки, подытоги по категориям и генеральный итог за один план сканирования, избавляя от склеек через UNION ALL.

Почему обычный GROUP BY слеп к иерархии и чем опасна склейка через UNION ALL

Посмотрим шаг за шагом, как работает группировка на таблице Sales с колонками товара product, региона region и суммы sales_amount. Если написать обычный GROUP BY product, region, то на этапе сворачивания групп (этот конвейер мы разбирали в статье о порядке выполнения SQL и цене группировки) база схлопнет строки строго на нижнем уровне детализации: каждая пара «товар + регион» даст одну сумму SUM(sales_amount).

Если же в одной выборке нужны ещё и подытоги по каждому товару вместе с общей выручкой, то при решении в лоб приходится соединять три запроса через UNION ALL: первый считает GROUP BY product, region, второй вычисляет подытоги GROUP BY product с NULL вместо региона, а третий суммирует всю таблицу без GROUP BY. В итоге планировщик СУБД трижды сканирует таблицу Sales, а забытая правка фильтра WHERE в одной из веток тихо ломает сходимость отчёта.

Как работает WITH ROLLUP на таблице Sales и зачем нужна функция GROUPING()

Модификатор WITH ROLLUP (расширение стандарта ANSI SQL:1999) переносит расчёт иерархии внутрь движка. Это как обход склада с блокнотом: кладовщик идёт вдоль рядов один раз, фиксирует остатки по полкам, у края каждого стеллажа сразу подбивает сумму по товару, а на выходе складывает итоги стеллажей в общий результат.

При вызове GROUP BY product, region WITH ROLLUP база за один проход сворачивает группировку справа налево, формируя N + 1 уровней агрегации (в отличие от CUBE с сеткой из 2^N срезов или ручного GROUPING SETS). В выдачу последовательно попадают детальные строки (product, region), подытоги по товару (product, NULL) и генеральный итог (NULL, NULL).

Здесь многие попадают в ловушку: поскольку свёрнутые уровни помечаются маркером NULL, рука тянется обернуть поле в COALESCE(region, 'Все регионы'). Но если в таблице Sales у части заказов регион не заполнен и там хранится реальный NULL, то COALESCE не отличит пустую ячейку от подытога и подпишет такие продажи как «Все регионы». Выручает функция GROUPING(): она возвращает 1 только для синтетических NULL, созданных оператором ROLLUP, и 0 для обычных строк. Соберём чистый запрос к таблице Sales:

SELECT 
  IF(GROUPING(product) = 1, 'Все товары', product) AS product_name,
  IF(GROUPING(region) = 1, 'Все регионы', region) AS region_name,
  SUM(sales_amount) AS total_sales
FROM Sales
GROUP BY product, region WITH ROLLUP;

В рантайме СУБД для детальных строк GROUPING() возвращает 0, сохраняя названия товаров и регионов (включая реальные пропуски). На строке подытога по товару срабатывает GROUPING(region) = 1, подставляя метку 'Все регионы', а в финальной строке генерального итога единицу возвращают обе функции, выводя 'Все товары' и 'Все регионы'.

Учитывай и диалекты: в MySQL пишется постфикс WITH ROLLUP (в версиях 8.0+ избегай конфликтующих явных ORDER BY), а в PostgreSQL используется форма GROUP BY ROLLUP(product, region). Проверка через EXPLAIN (по правилам из шпаргалки по чтению планов выполнения SQL) покажет однократное чтение таблицы — так замена UNION ALL на WITH ROLLUP разгружает диск и процессор базы.

А если завтра заглянуть в аналитические модули твоего проекта — сколько многоэтажных склеек с UNION ALL или циклов суммирования на бэкенде там можно заменить одной строчкой ROLLUP?

Хочу подробный разборГолоса помогают выбрать, что разобрать подробно. За этот материал 0 голосов.
Источники · 3