tgoop.com/big_data_systems_analysis/181
Create:
Last Update:
Last Update:
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 |
Если нужно определить, какие строки являются результатом группировки, используйте функцию
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