Теория: Введение

Представим прикладную задачу: у нас есть таблица продаж, и для каждой строки нужно показать цену товара и среднюю цену этого же товара по всем чекам.

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

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

Сами данные в таблице при этом не перестраиваются.

Например, результат может выглядеть так:

Столбец avg_price_by_product как раз и добавляется оконной функцией: для каждой строки считаем среднюю цену в рамках своего товара.

Что такое партиция

Чтобы не путаться в терминах:

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

Дальше в уроке, когда мы говорим «партиция», мы имеем в виду именно логическую партицию окна.

Синтаксис оконных функций

Оконные функции вычисляются для каждой строки результата. Синтаксис выглядит так:

SELECT product_id, product_name, category_id, price,
    SUM(price) OVER (PARTITION BY category_id) AS sum_price_by_category,
    AVG(price) OVER (PARTITION BY category_id) AS avg_price_by_category,
    COUNT(price) OVER (PARTITION BY category_id) AS count_price_by_category,
    MIN(price) OVER (PARTITION BY category_id) AS min_price_by_category,
    MAX(price) OVER (PARTITION BY category_id) AS max_price_by_category
FROM test_table;

В запросе выше PARTITION BY category_id задает логические партиции по категориям. Для каждой строки в своей партиции считаются сумма, среднее, количество, минимальное и максимальное значение цены.

В оконных функциях есть три параметра. Ключевое слово OVER() включает оконный режим. Если не указать OVER(), останется обычная агрегирующая функция SUM(price), AVG(price) и так далее.

После OVER() указываются параметры окна: PARTITION BY задает логические партиции, а ORDER BY задает порядок строк внутри партиции.

Оконная функция вычисляет значение в разрезе категории для каждого продукта.

ORDER BY в окне работает так же, как обычная сортировка: по возрастанию или убыванию. Также доступны расширения вроде IGNORE NULLS, NULLS FIRST, NULLS LAST.

PARTITION BY имеет просто значение тех полей, которые нам нужны, но они также могут быть и вычисляемыми.

Расширения оконных функций

У оконных функций, как у остальных, есть свои расширения:

• Шаги окна (для поиска на несколько шагов назад и вперед)
• Дефолтные значения (заглушки для NULL)
• Игнорирование NULL по аналогии с ORDER BY
• Фрейм окна («форточка»)

В курсе мы будем часто использовать шаги окна для поиска на несколько шагов вперед или назад - LEAD и LAG. То есть мы можем сказать, на какое число шагов это смещение произвести.

Оконные функции хорошо работают с дефолтными значениями. В них автоматически встроена обработка значений NULL. Это могут быть как дефолтные значения и это может быть игнорирование значений NULL.

Также существует фрейм окна (иногда его называют «форточкой»). Он задает, какие строки брать относительно текущей: например, от начала партиции до текущей строки. Эту тему подробно разберем дальше в курсе.

Применение оконных функций

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

Вычисления над набором строк с единым признаком

Допустим, нам нужно посчитать среднюю цену продажи конкретного товара. Она может различаться от чека к чеку (например, у покупателя есть скидка на этот продукт). Мы можем применить оконную функцию, где в качестве окна мы будем использовать product_id и product_name товара.

Сравнение строки с групповым показателем

Частая задача: найти чеки, где цена товара выше средней цены по этому же товару.

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

Построение аналитических отчетов

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

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

Расчеты скользящих средних

С помощью оконных функций можно считать скользящие средние. Это средняя по N соседним или предыдущим значениям в окне. Обычно это делается через AVG(...) OVER (...) с рамкой окна, а LEAD и LAG используются для смещения к соседним строкам. Про этоммы оговорим в следующих уроках.

Расчеты нарастающих итогов и процентилей

Расчеты нарастающих применяются для визуализации, когда нам нужно построить динамический график изменения или pie chart. Для круговых диаграмм очень важны процентили — то есть какую долю какой продукт занимает.

Ранжирование данных

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

Итоги

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

В этом курсе научимся:

• Задавать окно через OVER(), PARTITION BY и ORDER BY.
• Сравнивать значения строки с групповыми метриками.
• Считать скользящие и нарастающие показатели.
• Использовать LEAD и LAG для смещения по строкам.
• Строить аналитические выборки и ранжировать данные.

Это позволит быстрее собирать аналитические запросы и о��вечать на прикладные вопросы бизнеса: находить отклонения, сравнивать строки с группой, считать динамику и строить ранжированные отчеты без сложных подзапросов.