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

Привет 👋 Живя за тысячи километров от «цивилизации», не хватает нетворкинга и свежего взгляда на дата-мир. Посты, подкасты, видео — всё это хорошо, но хочется живого общения.

Хочется узнать больше о вас, мои читатели. Расскажите о себе и вашем опыте. Как у вас строятся дата-пайплайны? С какими сложностями сталкиваетесь? Или, может, вы только начинаете свой путь в мире данных?

Моя текущая боль — интеграция с 1С, данные из которой падают в кафку в куче всяческих вариаций структур. И надо всё разложить по красоте, ничего не потеряв по пути и обложив проверками. Бесконечная вложенность объектов, непредсказуемые структуры, русские наименования сущностей... в общем, то ещё извращение 😏 А в жире уже ждет следующая пачка 1С на интеграцию. Вы как, дружите с 1С?) Делитесь опытом!

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

Помню, что задолжала пост про инструменты в руках системного аналитика.

Но много думала об этом и выходит слишком скользкая тема) ведь главное — это не инструменты, а результат. А любой инструмент — это всего лишь путь к нему. Поэтому от компании к компании, да даже в рамках одной компании инструменты будут постоянно меняться.

Можно, конечно, поковырять всего понемногу из какой-нибудь таблички как на примере выше 😬, но сначала спросите себя "зачем". Быть в курсе трендов и быстро меняющегося мира данных — ок, знать всё обо всём без практической применимости — достижение уже спорное. Так или иначе, в работе вы будете погружаться в новые для вас инструменты и технологии.

Когда я приходила на свою первую работу без DWH бекграунда, то знала что такое airflow чисто теоретически из пары обзорных видео. Но жизнь заставила быстро разобраться что к чему. На текущей работе также пришлось моментально погружаться в Snowflake, про который каждый из коллег знал примерно столько же (так как зарождение DWH в компании началось с нас).

Выходит, что лучший наш инструмент — это мозг и умение (и что важно — желание) учиться и адаптироваться. Смотреть на мир широко и системно. Не фукать на что-то новое (частенько встречаю такое в IT-мире). Развивать софт-скиллс. Не зря всё чаще в статьях и конференциях упор делается именно на развитие мягких навыков, которые дополняют техническую экспертизу (или у меня это иллюзия частотности? 🤨).

Возвращаясь к инструментам. Чем пользуюсь в работе ежедневно сейчас: snowflake, airflow, kafka, tfs, базы различных наших источников (MS SQL, PostgreSQL и прочее), а также множеством самописных фреймворков (в том числе etl), разработанных нашими инженерами, тестирую новые загрузки через питон+тетрадку, исследую сторонние апи через постман (или ту же тетрадку). Заглядываю в датахаб и табло. Периодически сталкиваюсь с чем-то новым. В прошлом месте инструментарий был совсем другой) например, был гораздо более плотный опыт взаимодействия с Oracle, Greenplum и ClickHouse. Какие-то популярные решения, типа dbt ещё не было возможности потрогать в бою) только в качестве личного "факультатива".

Так что инструменты приходят и уходят, а мозг остаётся с нами всегда 😀 наше лучшее вложение 🤑🤫

#системный_анализ

Иметь перед глазами шпаргалки-напоминалки и короткие конспекты порой очень удобно, особенно, если чем-то пользуетесь не на регулярной основе. Пользуйтесь 😎

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

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

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

Как-то в одной из моих команд появился коллега, который крайне негативно относился к GIT в DE-практиках, считая это никому не нужным усложнением 👀 и "вообще я привык один работать".

Git — это распределённая система контроля версий (VCS), которая позволяет отслеживать изменения в файлах и совместно работать над проектами.

Помню, тогда меня это сильно удивило, так как всегда считала использование гита стандартом де-факто в любой разработке. Да что уж там говорить – даже при написании документации он пригождается. Когда только начинала работать SA, я даже онлайн-митап проводила для наших аналитиков — объясняла, зачем им гит в контексте написания sql-скриптов и как им пользоваться.

С тех пор, конечно, много воды утекло. Но хочется напомнить и здесь, что владение гитом – это, действительно, важный навык, который вам пригодится в реальной работе, а не просто собесы проходить. Даже если вдруг вы работаете в одиночку, git всё равно будет вашим другом. Откатиться к рабочей версии после неудачного эксперимента? Легко. Посмотреть, что вы меняли в SQL-скрипте три месяца назад? (ну это на тот случай, если вы не любитель писать доки). Без проблем. А уж когда дело доходит до командной работы — тут вообще без вариантов. В общем, если вы ещё не пользовались гитом, пора восполнить пробелы в знаниях.

Пока я очень не спешно пишу заметки в блоге 🐈, более продуктивные ребята пилят полезный контент. Например, инженерообязанный Владимир записал отличное видео, где рассказывает про гит на пальцах. Для работы этой базы более чем достаточно, а чего будет не хватать — уж нагуглите спросите у гопоты в процессе.

Не реклама, рекомендация от души)

Меня дико бесят недокументированные данные! 🤬 А это, увы, встречается слишком часто. Злит, что пункт "документация" не входит в неотъемлемую часть разработки какого-либо продукта. И вот ты подцепляешься к базе какого-то нового используемого сервиса и видишь просто кучу табличек с 100+ столбцами без какого-либо намёка какие данные там лежат. Особенно "приятно", когда нет возможности пощупать сам сервис и поэкспериментировать с данными в UI. Садишься и пытаешься описать (читай — сделать чужую работу) всё это богатство, опираясь только на свою логику и здравый смысл.

Часто бывает, что задачи на интеграцию прилетают asap, и кажется, что тебе нужно просто загрузить данные, а там уж аналитики разберутся. Но терпеть не могу, когда в моём хранилище что-то лежит без описания 👿 поэтому в любом случае стараюсь заполнить метаданные по максимуму. Да, это занимает дополнительное время, но зато потом не приходится каждый раз заново разбираться в структуре и объяснять коллегам, что означает загадочное поле "fl_xyz_2".

Понимаю, что разработчики работают в условиях жёстких дедлайнов, но отсутствие доков — это техдолг, который рано или поздно аукнется. А сейчас, когда все активно используют ИИ, хорошие=правильные описания становятся ещё важнее, тем более когда мы говорим о сложных продуктах и запутанных взаимосвязях. Без доков даже самый умный ИИ будет строить уверенные предположения, которые чаще всего окажутся ошибочными. Опять же, разработчикам в эпоху ИИ накидать описания к полям и таблицам при наличии ТЗ к проекту и доступом к коду — вполне реально и быстро. Но пока это мне приходится использовать ИИ в паре с анализом, логикой и гаданием на кофейной гуще.

Когда-то я хотела стать техписом именно из-за того, что мне нравится всё описывать и структурировать, но решила, что совсем без технарской работы будет скучно 💻 а теперь совмещаю и то, и другое.

А вас какие боли при работе с данными? 🙃

Work-life balance или как проходит это лето

В последние пару недель встречаю очень много постов про то, как лето проходит мимо, и вспоминаю свои такие же периоды в жизни. Но в этом году моё лето настолько в стиле life, что balance совсем не соблюдается 🤫 Весна выдалась горячей и я решила выдохнуть и никуда не спешить, наслаждаясь моментами жизни (ведь мы за этим с вами растём и работаем, правда?).

Моё замедление пришло через совершенно неожиданные вещи. Зимой мы переехали в квартиру побольше и к лету я решила чуть оживить балкон зеленью, а в итоге всерьез увлеклась комнатными растениями. Теперь каждое утро начинается с чашечки кофе и обхода владений 🪴 поливы, пересадки, изучение грунтов и горшков – миллион информации из совершенно другого мира. Вместе с этим и вся жизнь как будто замедлилась. Бэклог личных дел растёт, но крокодил не ловится, не растёт кокос.

Много лет я жила в страхе "не успеть" и с желанием "бежать-развиваться", чтобы догнать ребят из инфо-поля. Казалось, что если остановишься хоть на день — и всё, отстанешь навсегда. Но чем больше ты развиваешься, тем больше растёт и развивается твоё инфо-поле, и этой гонке нет конца. Мне кажется, что это путь к выгоранию, насколько бы интересно тебе не было.

Хотя, конечно, и у замедлания есть обратная сторона. Во-первых, к размеренности слишком легко привыкнуть и вот уже мозг начинает лениться и тяжелее въезжает даже в обычные рутинные процессы. Появляются ошибки, сама грешна. А во-вторых, порой из замедления сложно выйти без встряски. Но лично у меня чаще всего бывает наоборот. Наступает момент, когда устаёшь от размеренности и с новыми силами "врываешься" в любимые активные дела. И, как будто бы, жизнь показывает: позволяя себе перерывы, в итоге мы становимся только продуктивнее. В конце концов, что такое пара месяцев в рамках множества рабочих лет?

А как проходит ваше лето? Боитесь отстать от IT-гонки или позволяете себе отдых?

#life