ROLLUP, CUBE и GROUPING SETS в SQL: многоуровневые итоги

Стандартный GROUP BY группирует по одному набору столбцов. Но в отчётах часто нужны промежуточные итоги — по категории, по месяцу, и общий итог. ROLLUP и CUBE делают это в одном запросе.

Проблема: итоги в отчёте

-- Обычный GROUP BY
SELECT category, month, SUM(revenue) AS revenue
FROM sales GROUP BY category, month;

-- Но нам нужно ещё: итог по категории, итог по месяцу, общий итог
-- Раньше: 4 запроса через UNION ALL
-- Теперь: ROLLUP / CUBE

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

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

SELECT
    category,
    month,
    SUM(revenue) AS revenue
FROM sales
GROUP BY ROLLUP(category, month);

Это эквивалентно GROUP BY для каждой комбинации:

• (category, month) — детальные данные
• (category) — итог по категории
• () — общий итог (grand total)

-- Результат:
-- Электроника | Январь | 500000   ← детально
-- Электроника | Февраль| 450000   ← детально
-- Электроника | NULL   | 950000   ← итог по категории
-- Мебель      | Январь | 200000   ← детально
-- Мебель      | NULL   | 200000   ← итог по категории
-- NULL        | NULL   | 1150000  ← общий итог

GROUPING(): определить что является итогом

NULL в итоговых строках неотличим от «реального» NULL. Используйте GROUPING():

SELECT
    CASE WHEN GROUPING(category) = 1 THEN 'ИТОГО' ELSE category END AS category,
    CASE WHEN GROUPING(month) = 1    THEN 'Все месяцы' ELSE month::TEXT END AS month,
    SUM(revenue) AS revenue,
    GROUPING(category) AS is_category_subtotal,
    GROUPING(month)    AS is_month_subtotal
FROM sales
GROUP BY ROLLUP(category, month)
ORDER BY GROUPING(category), category, GROUPING(month), month;

GROUPING(col) = 1 если строка является итогом по этому столбцу (NULL добавлен ROLLUP), 0 если реальное значение.

CUBE: все возможные комбинации

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

SELECT category, region, month, SUM(revenue)
FROM sales
GROUP BY CUBE(category, region, month);

Для 3 столбцов: 2³ = 8 комбинаций:

• (category, region, month)
• (category, region)
• (category, month)
• (region, month)
• (category)
• (region)
• (month)
• () — grand total

-- Практичный пример: кросс-таблица по категории и региону
SELECT
    COALESCE(category, 'ВСЕГО') AS category,
    COALESCE(region, 'ВСЕГО') AS region,
    SUM(revenue) AS revenue
FROM sales
GROUP BY CUBE(category, region)
ORDER BY GROUPING(category), category, GROUPING(region), region;

GROUPING SETS: контроль над группировками

GROUPING SETS позволяет явно указать нужные комбинации:

-- Только конкретные группировки (без лишних)
SELECT category, month, SUM(revenue)
FROM sales
GROUP BY GROUPING SETS (
    (category, month),  -- детально
    (category),         -- итог по категории
    ()                  -- grand total
);
-- Эквивалентно ROLLUP(category, month)

-- Нестандартная комбинация: только две группировки
GROUP BY GROUPING SETS (
    (category, region),  -- по категории и региону
    (month)              -- и отдельно по месяцу
);
-- Не ROLLUP, не CUBE — только то что нужно

Практический отчёт с итогами

-- Финансовый отчёт по подразделениям и кварталам
SELECT
    COALESCE(department, '=== КОМПАНИЯ ===') AS department,
    COALESCE(TO_CHAR(quarter_start, 'Q"кв." YYYY'), 'Весь год') AS quarter,
    SUM(revenue) AS revenue,
    SUM(expenses) AS expenses,
    SUM(revenue) - SUM(expenses) AS profit,
    ROUND((SUM(revenue) - SUM(expenses)) / NULLIF(SUM(revenue), 0) * 100, 1) AS margin_pct
FROM (
    SELECT
        department,
        DATE_TRUNC('quarter', sale_date) AS quarter_start,
        SUM(amount) AS revenue,
        SUM(cost) AS expenses
    FROM sales
    WHERE EXTRACT(YEAR FROM sale_date) = 2026
    GROUP BY department, DATE_TRUNC('quarter', sale_date)
) base
GROUP BY ROLLUP(department, quarter_start)
ORDER BY GROUPING(department), department, GROUPING(quarter_start), quarter_start;

FILTER в агрегатах с ROLLUP

-- Разные метрики в одном ROLLUP
SELECT
    category,
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed,
    SUM(amount) FILTER (WHERE status = 'completed') AS revenue,
    ROUND(COUNT(*) FILTER (WHERE status = 'completed')::numeric / COUNT(*) * 100, 1) AS conv_rate
FROM orders
GROUP BY ROLLUP(category, DATE_TRUNC('month', created_at))
ORDER BY GROUPING(category), category, GROUPING(month), month;

Сравнение трёх операторов

Правило выбора:

• Нужны итоги по убыванию детализации → ROLLUP
• Нужны все возможные срезы → CUBE
• Нужны только конкретные комбинации → GROUPING SETS

Альтернатива: UNION ALL (старый способ)

-- Старый способ — громоздко и медленно (4 прохода по данным)
SELECT category, month, SUM(revenue) FROM sales GROUP BY category, month
UNION ALL
SELECT category, NULL, SUM(revenue) FROM sales GROUP BY category
UNION ALL
SELECT NULL, month, SUM(revenue) FROM sales GROUP BY month
UNION ALL
SELECT NULL, NULL, SUM(revenue) FROM sales;

-- Современный способ — один проход
SELECT category, month, SUM(revenue) FROM sales GROUP BY CUBE(category, month);

ROLLUP и CUBE работают за один проход — значительно быстрее UNION ALL.

Итог

ROLLUP, CUBE и GROUPING SETS — стандарт SQL:1999, поддерживаемый в PostgreSQL, MySQL 8+, BigQuery, Snowflake, Redshift. Используйте их для создания многоуровневых отчётов без дублирования запросов.