В мире больших данных: “GROUPING SETS для упрощения агрегации Мы группируем данные десятки раз…”

GROUPING SETS для упрощения агрегации

Мы группируем данные десятки раз в день: по датам, категориям, клиентам или нескольким полям сразу. Но что, если нужно получить несколько уровней агрегации в одном результате? Объединять три разных запроса через UNION ALL? Писать вложенные подзапросы? Такой сценарий превращает простую задачу в головоломку с кучей повторяющегося кода 🔄

Теперь представьте: один запрос возвращает и детализацию, и промежуточные итоги, и общую сумму. И всё это без дублирования логики и потери производительности. Это не магия — это GROUP BY GROUPING SETS. Спойлер: после него вы вряд ли захотите возвращаться к старому подходу.

Синтаксис:


SELECT column1, column2, AGG_FUNC(column3) AS aggregate_result
FROM table_name
GROUP BY GROUPING SETS
(
    (column1),
    (column2),
    (column1, column2),
    () -- итоговая строка для всех данных
);

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


| order_id | order_dt   | category    | price |
|----------|------------|-------------|-------|
| 1        | 2025-02-01 | Книги       | 100   |
| 2        | 2025-02-01 | Книги       | 200   |
| 3        | 2025-02-01 | Электроника | 700   |
| 4        | 2025-02-02 | Книги       | 150   |
| 5        | 2025-02-02 | Электроника | 250   |
| 6        | 2025-02-02 | Электроника | 550   |

Запрос:


SELECT 
    order_dt, 
    category, 
    SUM(price) AS total_sum
FROM orders
GROUP BY GROUPING SETS
(
    (order_dt, category), -- Группировка по дням и категориям
    (order_dt),           -- по дням
    (category),           -- по категориям
    ()                    -- Итоговая строка
);

Результат:


| order_dt   | category    | total_sum |
|------------|-------------|-----------|
| 2024-01-01 | Книги       | 300       |
| 2024-01-01 | Электроника | 700       |
| 2024-01-02 | Книги       | 150       |
| 2024-01-02 | Электроника | 800       |
| 2024-01-01 | NULL        | 1000      |
| 2024-01-02 | NULL        | 950       |
| NULL       | NULL        | 1950      |
| NULL       | Книги       | 450       |
| NULL       | Электроника | 1500      |

🔵Полные строки (order_dt и category заполнены) — детализированные данные.
🔵Строки с order_dt и NULL показывают суммы по каждому дню.
🔵Строки с category и NULL показывают суммы по каждой категории.
🔵Строка с двумя NULL — общая сумма.

Если нужно определить, какие строки являются результатом группировки, используйте функцию GROUPING(). Она возвращает 1 там, где значение агрегировано.

Пример:


SELECT
    order_dt,
    category,
    SUM(price) AS total_sales,
    GROUPING(order_dt) AS is_dt_agg,
    GROUPING(category) AS is_cat_agg
FROM orders
GROUP BY GROUPING SETS
(
    (order_dt),           -- Группировка по дням
    (category),           -- Группировка по категориям
    ()                    -- Итоговая строка
);

| order_dt   | category   | total_sales | is_dt_agg | is_cat_agg |
|------------|------------|-------------|-----------|------------|
| 2024-01-01 | NULL       | 1000        | 0         | 1          |
| 2024-01-02 | NULL       | 950         | 0         | 1          |
| NULL       | NULL       | 1950        | 1         | 1          |
| NULL       | Книги      | 450         | 1         | 0          |
| NULL       | Электроника| 1500        | 1         | 0          |

Почему GROUPING SETS лучше UNION ALL?
➕ один запрос вместо нескольких
➕ оптимизация выполнения — СУБД сканирует таблицу один раз и для каждой строки вычисляет все группировки параллельно
➕ читабельность кода

➖ поддерживаются не все диалекты SQL (но основные — PostgreSQL, Oracle, SQL Server, Snowflake, BigQuery — да)

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

#sql