Почему оконные функции - это суперсила аналитика?

Знакомы с проблемой? Вам нужно посчитать долю каждой продажи в общей выручке за месяц, сравнить сегодняшний показатель со вчерашним или составить рейтинг товаров внутри категории. Обычные агрегатные функции (с GROUP BY или без него) «схлопывают» строки и детализация теряется.

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

Проще на примере:

-- Обычная сумма возвращает одну строку
SELECT SUM(revenue) FROM orders;

-- Оконная сумма добавляет общий итог к КАЖДОЙ строке
SELECT 
    *,
    SUM(revenue) OVER() AS total_revenue 
FROM orders;

Аналогия:
Представьте, что вы учитель.

▪Обычный запрос - это выставить одну общую среднюю оценку всему классу.
▪Оконная функция - это рядом с каждой личной оценкой ученика написать средний балл по классу для сравнения.

Где использовать оконки?
При подсчете скользящего среднего, накопительных итогов, ранжирования, расчете долей, сравнения строк.

В следующем посте подробнее погрузимся в синтаксис и научимся создавать окна.

А пока поделитесь насколько сложно вам было осваивать оконные функции?

🤯 - сложно
🥱 - изи

#харды #sql

Учимся создавать «окна» в SQL

В прошлом посте мы узнали, зачем нужны оконные функции. Теперь научимся их объявлять. Все начинается с инструкции OVER() - она и определяет наше «окно».

Ключевые команды внутри OVER():

▪️PARTITION BY - разделяет данные на группы (партиции). Как GROUP BY, но без «схлопывания» строк. Считает функцию внутри каждой группы отдельно.
▪️ORDER BY - сортирует строки внутри окна. Критично для функций нарастающего итога, ранжирования и смещения (LAG/LEAD).

Разберем на примере простой таблички, содержащей дату, канал с которого пришел пользователь и количество конверсий:

SELECT 
  date AS dt
, medium AS med
, conversions AS conv
, SUM(conversions) OVER(PARTITION BY date ORDER BY medium) AS sum 
FROM orders

Что произойдет?

▪PARTITION BY Date создаст отдельное «окно» для каждой даты. Сумма будет считаться только в рамках одного дня.
▪ORDER BY medium отсортирует каналы внутри каждой даты.
▪SUM(conversions) в паре с ORDER BY рассчитает нарастающий итог конверсий внутри каждого дня. Для первой строки в окне (дне) sum будет равен ее conversions, для второй - сумме первой и второй, и так далее.

⚠️ Важно: ROWS / RANGE управляют диапазоном строк, по которым считается оконная функция. И даже если ничего не указывать, то по умолчанию используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

В результате выполнения запроса мы получим примерно такую табличку:

dt        med      conv  sum
10.05.20  cpa         1    1
10.05.20  cpc         2    3
10.05.20  organic     1    4
11.05.20  cpa         1    1
11.05.20  cpc         3    4
11.05.20  direct      1    5
11.05.20  organic     2    7
12.05.20  cpc         1    1
12.05.20  organic     2    3

Основы разобрали! Далее я расскажу как сужать фокус окна до «скользящего» диапазона с помощью ROWS BETWEEN.

#харды #sql

Как в SQL настроить фокус окна?

В прошлый раз мы научились делить данные на окна. Но что, если нам нужно посчитать результат не по всей группе, а сумму с соседней строкой?

Когда я осваивал оконные функции, для меня самым сложным было разобраться не с OVER, партициями или самими функциями, а именно с настройками окна.

Инструкция ROWS позволяет ограничить строки в окне, указывая фиксированное количество строк, предшествующих или следующих за текущей.

RANGE, в отличие от ROWS, работает не с физическими строками, а с диапазоном значений ORDER BY. Поэтому несколько строк с одинаковым значением могут попадать в одно окно.

Обе инструкции ROWS и RANGE всегда используются вместе с ORDER BY.

Возьмем наш пример и посчитаем сумму по текущей и следующей строке внутри каждого дня:

SELECT 
  date
, medium
, conversions
, SUM(conversions) OVER(PARTITION BY date ORDER BY medium ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS sum 
FROM orders

Результат выполнения и логику запроса смотрите на картинке.

В выражении для ограничения окна также можно использовать следующие ключевые слова:

▪️UNBOUNDED PRECEDING - указывает, что окно начинается с первой строки группы;
▪️UNBOUNDED FOLLOWING - с помощью данной инструкции можно указать, что окно заканчивается на последней строке группы;
▪️CURRENT ROW - инструкция указывает, что окно начинается или заканчивается на текущей строке;
▪️BETWEEN «граница окна» AND «граница окна» - указывает нижнюю и верхнюю границу;
▪️«Значение» PRECEDING - определяет число строк перед текущей строкой (не допускается в RANGE);
▪️«Значение» FOLLOWING - определяет число строк после текущей строки (не допускается в RANGE).

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

Далее разберем самые полезные оконные функции.

#харды #sql

Почему оконные функции могут тормозить запрос?

Я написал уже целую серию постов про оконки (раз, два, три) и там все выглядело красиво - добавил OVER() и получил профит. Но в комментах справедливо подметили, что оконные функции - это часто один из самых тяжелых видов запросов, который сильно жрет ресурсы.

Главный виновник тут обычно не сама функция, а сортировка. Сортировать много строк - дорогая операция на больших объемах данных, и ее лучше избегать везде, где она не нужна. Ниже 4 прикладных совета по оптимизации.

1⃣ Убери ORDER BY из окна, если порядок не влияет на смысл

Частая ошибка - писать ORDER BY внутри OVER() «на всякий случай». Как только он появляется, базе нужно обеспечить порядок строк, а это почти всегда дорого.

Если тебе нужен просто итог по группе (например, общий доход за день для каждой строки), а не накопительная сумма или скользящее окно - ORDER BY внутри окна не нужен.

-- Накопительная сумма (дороже)
SUM(revenue) OVER (PARTITION BY date ORDER BY created_at)

-- Просто итог по дню (дешевле)
SUM(revenue) OVER (PARTITION BY date)

2⃣ Чем меньше строк, тем быстрее

Оконки «проходят» по всем строкам, часто упорядочивают их, а иногда еще и держат большие куски данных в памяти. Поэтому самый простой ускоритель - сократить объем данных ДО оконки:

✔ Отфильтруй период (например, последние 30/90 дней);
✔ Не тащи лишние колонки («SELECT *» - плохо);
✔ Если можно, то сначала агрегируй данные до нужной гранулярности, а окно считай уже на агрегате.

3⃣ Несколько разных окон = несколько сортировок

Если в одном запросе несколько окон с разным ORDER BY, база может быть вынуждена упорядочивать данные несколько раз. И как итог: больше времени и памяти.

SUM(x) OVER (PARTITION BY a ORDER BY b) AS s1,
AVG(x) OVER (PARTITION BY a ORDER BY c) AS s2

Если можешь, унифицируй порядок в окнах. Если не можешь, то иногда лучше разнести расчеты на 2 шага, чем собирать все в одном SELECT.

4⃣ Используй план выполнения запроса

EXPLAIN - это команда, которая показывает план выполнения запроса: какие шаги база собирается сделать и где она потратит ресурсы. А EXPLAIN ANALYZE еще и выполняет запрос добавляя фактические цифры: сколько строк прошло через каждый шаг и сколько времени заняло.

Далее я разберу, как именно читать EXPLAIN и использовать эту команду для оптимизации.

#харды #sql

Как аналитику оптимизировать свои запросы?

Ранее я рассказывал, почему оконные функции могут тормозить, и дал 4 совета по оптимизации. Но если ты действительно хочешь ускорить свои запросы, то начинать нужно с анализа плана выполнения.

Знакомьтесь - EXPLAIN

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

Как SQL работает под капотом?

Прежде чем говорить о плане, важно понимать этапы, которые проходит SQL-запрос внутри базы данных (на примере PostgreSQL):

1⃣ Parser - разбирает текст SQL, проверяет синтаксис и строит абстрактное синтаксическое дерево.

2⃣ Analyzer - проверяет существуют ли таблицы, колонки, функции, есть ли права доступа. Если ты опечатался в названии колонки, то ошибка упадет именно здесь.

3⃣ Rewriter - делает логическое преобразование запроса.

4⃣ Planner / Optimizer - самый важный этап для нас. Он перебирает возможные планы выполнения и выбирает тот, у которого наименьшая стоимость.

5⃣ Executor - выполняет план и возвращает результат.

EXPLAIN показывает результат работы planner и это тот самый план, который база данных посчитает оптимальным на основе статистики.

Как читать EXPLAIN?

План выполнения - это дерево. Самый глубоко вложенный оператор выполняется первым. Всегда читай план снизу вверх - так ты увидишь реальную последовательность действий.

Команда EXPLAIN показывает, как именно база данных собирается выполнять запрос: какие индексы использовать, как соединять таблицы, будет ли сортировка.

А EXPLAIN ANALYZE еще и выполняет запрос, добавляя фактические цифры: сколько строк прошло через каждый шаг, сколько времени заняло, сколько памяти использовано.

Основные операторы в плане:

◾ Seq Scan - последовательное чтение всей таблицы (для маленьких таблиц - это окей).
✔ Оптимизация: добавить индекс на поля, которые используются в WHERE, JOIN, ORDER BY.

◾ Index Scan - чтение по индексу.
✔ Оптимизация: следи, чтобы индекс реально использовался: без функций, кастов и с правильным порядком колонок.

◾ Sort - дорогая операция, особенно на больших объемах и без индексов.
✔ Оптимизация: если сортировка нужна, то убедись, что есть индекс на поля сортировки. Если не нужна - убери.

◾ Hash Join / Merge Join / Nested Loop - это способы соединить таблицы. Hash Join и Merge Join обычно быстрее на больших данных, Nested Loop - на маленьких.
✔ Оптимизация: главный прием - уменьшить данные до JOIN, а не после.

◾ Cost=1..123 - оценка оптимизатора, где первое число - стоимость получить первую строку, второе - все строки. Чем меньше - тем лучше.
✔ Оптимизация: это лишь оценка оптимизатора, ориентируйся на реальные метрики из EXPLAIN ANALYZE.

Я не буду переписывать сюда всю документацию. Вот ссылки на официальные руководства, где все подробно расписано:
- PostgreSQL
- MySQL
- SQL Server
- Greenplum
- ClickHouse

Твое задание на сегодня

Возьми любой запрос, который работал долго, и выполни перед ним EXPLAIN, найди самый дорогой узел по стоимости и попробуй его оптимизировать.

Интересными кейсами делись в комментариях!

#харды #sql