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 полезен для отчетности и аналитических анализов, где нужны сводные данные разной детализации. Это инструмент работает: 🟢 удобно: меньше кода, меньше ошибок 🟢 быстро: один проход по данным 🟢 гибко: возможны любые комбинации группировок