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

SQL под капотом: как выполняются запросы

Знаете ли вы, что происходит под капотом СУБД, когда вы выполняете SQL-запрос? База данных запускает целый процесс, шаг за шагом превращая код в набор данных. Каждая команда проходит проверку, оптимизацию, выполнение, обработку и вывод результата. Давайте посмотрим на каждый из этапов:

1. Query Parsing (разбор запроса)
Сначала сервер проверяет, правильно ли написан запрос. Проводит так называемый синтаксический анализ. Ошиблись в запятой или перепутали порядок ключевых слов? Получите ошибку.

После синтаксического анализа начинается семантический разбор: существуют ли таблицы и колонки, есть ли у вас права на запрос? Если все ок, база строит parse tree.

Parse Tree — это иерархическое представление запроса, где каждый узел — отдельная операция (например, фильтр, join, сортировка). Это облегчает работу оптимизатора и позволяет строить разные планы выполнения.

2. Query Optimization (оптимизация запроса)
На этом этапе в работу вступает умный планировщик. Он оценивает различные стратегии выполнения запроса, чтобы определить наиболее эффективную и менее ресурсоёмкую. Оптимизаторы сильно отличаются от СУБД к СУБД, но, к примеру, в Snowflake он, действительно, умный и даже плохо написанный запрос в большинстве случаев "переписывает" оптимально самостоятельно (это, конечно, не значит что стоит писать запросы как попало 👿).

Оптимизатор, в зависимости от СУБД может проверять:
Как соединять таблицы — Nested Loop, Hash Join, Merge Join?
Как фильтровать и сортировать данные?
Использовать индексы или нет?

Оптимизатор анализирует статистику таблиц: сколько строк, какие значения чаще встречаются, какие индексы есть. Он перебирает варианты и выбирает наилучший.

3. Query Execution (выполнение запроса)
После этого база данных начинает пошагово выполнять запрос, согласно выбранному плану.

Запросы могут выполняться через:
🔵 Table Scan — полный перебор строк в таблице (долго).
🔵 Index Seek — точечный поиск через индекс (быстро, но требует индекса).

4. Извлечение или изменение данных
Если наш запрос извлекает данные (SELECT - Data Query Language), база выбирает нужные строки из таблиц и формирует результат. Если же запрос изменяет данные (INSERT, MERGE, UPDATE или DELETE - Data Manipulation Language), информация в таблице обновляется, удаляется или дополняется.

5. Формирование результата
Когда SQL-движок собрал нужные строки, он финально формирует итоговый результат: сортирует, группирует, выполняет агрегатные вычисления. Однако часть агрегаций, особенно в запросах с GROUP BY, может выполняться еще на этапе извлечения данных, если движок решит, что это эффективнее. То есть это зависит от плана выполнения запроса и используемого метода обработки.

6. Результат
Когда всё готово, результат возвращается в клиентское приложение, которое уже отображает его пользователю.

Для SELECT-запросов, если данных много, они передаются частями, чтобы не перегружать память.
Некоторые базы поддерживают Lazy Execution — строки выгружаются только при необходимости.


Как видите, написанный запрос запускает целые механизмы внутри СУБД. Каждый этап играет свою роль: разбор проверяет синтаксис на ошибки, оптимизатор выбирает самый быстрый путь, выполнение шаг за шагом приводит к нужному результату, а передача данных гарантирует, что вы получите ответ в удобной форме, ничего не потеряв.

Не всегда имеет смысл знать, что происходит под капотом, но хотя бы верхнеуровневое понимание помогает нам самим работать эффективнее. Если что-то идет не так, вы будете знать, где искать проблему и как ее решить. Понимание происходящего — ключ к написанию быстрых и оптимизированных запросов.

#sql

Разбираемся с дублями

Если вашу выборку нужно почистить от дублей, вы можете сделать это очень просто:

SELECT *
FROM your_table
QUALIFY ROW_NUMBER() OVER(PARTITION BY column_id ORDER BY column_dt DESC) = 1;

В результате получим в выводе только уникальные строки (вместо *, конечно же, указываем корректный список полей).

QUALIFY + ROW_NUMBER() = никаких лишних подзапросов 🙃

Недостаток: пока что работает не во всех СУБД 🥲

Если СУБД не поддерживает оператор QUALIFY, можем чистить так:

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY column_id ORDER BY column_dt DESC) AS rn
    FROM your_table
)
SELECT *
FROM cte
WHERE rn = 1;

P.S. Про сам QUALIFY я уже писала здесь.

#sql

Занималась тут оптимизацией чужого запроса. И вот вроде бы знаешь базу и хочешь её применить, но оптимизатор всегда оказывается хитрее 🙂

Среди прочего, пыталась применить одно из главных правил оптимизации — predicate pushdown. Это когда мы поднимаем условия фильтрации как можно выше, чтобы заранее уменьшить объем данных. Так вот, вынесла в cte фильтрацию одной таблички (~2GB), а в другом cte уже шла работа с отфильтрованными данными — джойны и тп. Смотрю в план запроса и вижу фигу, что снежок (snowflake) всё равно сначала сканирует таблицу целиком, затем джойнит, и только после этого фильтрует 😵 причём аналогичный сценарий на другой, но бОльшей таблице (~в 8GB) отрабатывает как надо 🥲 Видимо, размер данных или внутренняя статистика влияют на решения cost-based оптимизатора.

Никаких инсайтов в этой заметке вам не дам, но в очередной раз убеждаюсь: важно уметь читать (и понимать) планы запросов и анализировать query profile. Не всегда логичные на первый взгляд шаги оптимизации работают как ожидается. И не только от СУБД к СУБД поведение может разительно отличаться, но и даже в рамках таблиц в одном хранилище. Экспериментируйте и тестируйте на реальных данных 🤖

P.S. Тем, кто хочет использовать для анализа планов гпт, всё же советую сначала самостоятельно научиться их читать, т.к. LLM всё ещё склонны к галлюцинациям. Как говорится: "на ИИ надейся, да сам не плошай".

#sql #snowflake