WITH ROLLUP, CUBE и TOTALS

Стандартный GROUP BY выдаёт строки только по конкретным комбинациям ключей. Но аналитические отчёты часто требуют подитоги: “продажи по странам и городам, плюс итог по стране, плюс общий итог”. Без модификаторов — это 3 отдельных запроса с UNION ALL.

ClickHouse поддерживает 3 модификатора GROUP BY:

• WITH ROLLUP — иерархические подитоги (справа налево)
• WITH CUBE — все возможные комбинации (2^n)
• WITH TOTALS — одна строка общего итога

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

ROLLUP удаляет столбцы группировки справа налево, создавая подитоги на каждом уровне:

SELECT country, city, sum(revenue) AS total
FROM sales
GROUP BY country, city WITH ROLLUP
ORDER BY country, city;

Для GROUP BY a, b, c WITH ROLLUP создаются уровни:

• (a, b, c) — детальные строки
• (a, b) — подитог без c
• (a) — подитог без b, c
• () — общий итог

Итого: n + 1 уровень для n столбцов.

WITH CUBE: все комбинации

CUBE создаёт подитоги для всех возможных комбинаций столбцов GROUP BY:

SELECT year, quarter, sum(revenue) AS total
FROM sales
GROUP BY year, quarter WITH CUBE;

Для GROUP BY a, b WITH CUBE создаются комбинации:

• (a, b) — детальные строки
• (a) — подитог без b
• (b) — подитог без a
• () — общий итог

Для n столбцов: 2^n комбинаций.

WITH TOTALS: одна строка итога

WITH TOTALS добавляет одну дополнительную строку с общим итогом. В отличие от ROLLUP и CUBE, не создаёт промежуточных подитогов:

SELECT country, sum(revenue) AS total
FROM sales
GROUP BY country WITH TOTALS;

WITH TOTALS особенно полезен с HAVING: итог вычисляется до фильтрации HAVING, показывая полную картину:

SELECT country, sum(revenue) AS total
FROM sales
GROUP BY country WITH TOTALS
HAVING total > 10000;

GROUPING(): дизамбигуация подитогов

Главная проблема ROLLUP и CUBE: подитоги имеют default-значения (пустая строка, 0) в агрегированных столбцах. Как отличить подитог от реальной строки с пустым значением?

-- Проблема: country='' -- это подитог или реальная страна с пустым названием?
SELECT country, city, sum(revenue) AS total
FROM sales
GROUP BY country, city WITH ROLLUP;

Функция GROUPING() решает эту проблему. Она возвращает битовую маску, показывающую какие столбцы были агрегированы:

SELECT
    country,
    city,
    sum(revenue) AS total,
    GROUPING(country) AS g_country,
    GROUPING(city) AS g_city
FROM sales
GROUP BY country, city WITH ROLLUP
ORDER BY g_country, g_city, country, city;

• GROUPING(col) = 0 — столбец участвует в группировке (реальное значение)
• GROUPING(col) = 1 — столбец агрегирован (значение — подитог)

-- Множественный GROUPING() с битовой маской:
SELECT
    country, city,
    sum(revenue) AS total,
    GROUPING(country, city) AS grp
FROM sales
GROUP BY country, city WITH ROLLUP;
-- grp=0: обычная строка (country + city)
-- grp=1: подитог по country (city агрегирован)
-- grp=3: общий итог (оба агрегированы)

Практический пример: отчёт о продажах

SELECT
    CASE WHEN GROUPING(region) = 1 THEN 'ВСЕ РЕГИОНЫ' ELSE region END AS region,
    CASE WHEN GROUPING(category) = 1 THEN 'ВСЕ КАТЕГОРИИ' ELSE category END AS category,
    sum(revenue) AS revenue,
    count() AS orders,
    avg(revenue) AS avg_order
FROM sales
WHERE sale_date >= '2025-01-01'
GROUP BY region, category WITH CUBE
ORDER BY GROUPING(region, category), region, category;

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

Сравнение модификаторов

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

1. WITH ROLLUP — иерархические подитоги, столбцы удаляются справа налево. Идеален для drill-down отчётов.
2. WITH CUBE — все 2^n комбинаций. Для перекрёстных отчётов с независимыми измерениями.
3. WITH TOTALS — одна строка общего итога. Показывает total до HAVING-фильтрации.
4. GROUPING() — обязательна для дизамбигуации: отличает подитоги от реальных пустых значений.
5. Без GROUPING() нельзя надёжно отличить подитог от строки с country = ''.