Оконные функции в SQL: что это и как использовать

Рассказываем о пользе оконных функций в SQL и объясняем, как применять их в работе с данными. Описываем синтаксис и виды, приводим примеры функций.

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

Суть оконных функций в SQL заключается в разделении запроса на части, или так называемые партиции, по определённым условиям. При этом каждая часть обрабатывается отдельно от других. Функция выдаёт отдельный результат для каждой строки, сохраняя его. Этим оконные функции отличаются от GROUP BY, где строки группируются.

Результаты вычислений оконных функций добавляются в качестве дополнительных строк

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

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

• Расчёт усреднённых значений. Допустим, можно определить среднее значение продаж за квартал или вычислить максимальную цену товара за день.
• Анализ временных рядов. К примеру, можно рассчитать скользящее среднее значение цен акций за определённый период.
• Ранжирование. Оконные функции SQL позволяют ранжировать строки в таблице на основе определённого критерия, допустим по имени или по цене.
• Разделение данных на группы. Такие функции помогают сформировать группы данных и определить статистические показатели для каждой группы.
• Фильтрация данных. Оконные функции позволяют выбрать из общего массива данных только строки, соответствующие определённым условиям.

Научиться применять оконные функции поможет курс «SQL для работы с данными и аналитики». На занятиях студенты осваивают PostgreSQL, ClickHouse, DBeaver и DataLens, получают практический опыт и выполняют два проекта на реальных данных.

Написание оконных функций включает указание имени функции, её условий и спецификации. Спецификация в SQL ограничивает набор строк, которые будут участвовать в выполнении вычислений.

Шаблон написания оконной функции

Разбираемся, что означает каждый параметр SQL:

• <function_name> — название функции одного из классов, всегда пишется в угловых скобках.
• (arguments) — аргументы, допустим название столбца для расчёта, заключаются в круглые скобки.
• OVER — параметр, объявляющий, что используется именно оконная функция SQL.
• PARTITION BY (column names) — указывает партиции по колонкам, пишется в квадратных скобках.
• ORDER BY (column names) — формирует порядок вычисления функции, заключается в квадратные скобки.
• [frame clause] — указание фрейма для партиции, заключается в квадратные скобки.

Также оконная функция SQL может включать необязательное условие:

• [FILTER (WHERE filter_clause)] — выражение фильтрации, в котором используются квадратные и круглые скобки.

Оконные функции в SQL делятся на четыре вида:

1. Агрегатные. Проводят вычисления над строками и выдают единственный результат для группы. К ним относятся SUM(), AVG(), MIN(), MAX().
2. Ранжирующие. Определяют ранги строк, основываясь на заданном условии. В этот вид входят RANK(), DENSE_RANK(), ROW_NUMBER().
3. Аналитические. Рассчитывают значения всех строк, основываясь на других строках этой же группы. Сюда относятся CUME_DIST(), PERCENT_RANK(), PERCENTILE_CONT() и PERCENTILE_DISC().
4. Функции смещения. Определяют значения на основе движущегося окна строк. К ним относятся LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE().

Возьмём для примера таблицу employee_sales с информацией о продажах, совершенных сотрудниками за последние несколько месяце. В ней есть следующие поля: employee_id, month, sales_amount. Нужно вычислить скользящее среднее продаж (moving_average_sales) за последние три месяца для каждого сотрудника (employee_id). Здесь поможет агрегатная оконная функция SQL.

SQL-запрос с оконной функцией, который определяет средние показатели продаж сотрудников за три месяца

Разберёмся, что значит каждый параметр в примере:

• PARTITION BY employee_id — разделяет данные по сотрудникам.
• ORDER BY month — упорядочивает данные по месяцам внутри каждого сотрудника.
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — определяет окно в три строки (текущая строка и две предыдущие).
• AVG(sales_amount) OVER (...) — вычисляет скользящее среднее продаж за последние три месяца для каждого сотрудника.

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

SQL-запрос, ранжирующий продажи сотрудников по месяцам

Значения параметров:

• PARTITION BY employee_id — разделяет данные по сотрудникам.
• ORDER BY sales_amount DESC — упорядочивает данные по продажам в порядке убывания внутри каждого сотрудника.
• ROW_NUMBER() OVER (...) — присваивает уникальный номер строкам в пределах каждого сотрудника начиная с 1 для строки с наибольшими продажами.
• Внешний запрос выбирает строки, где sales_rank = 1, что соответствует месяцу с наибольшими продажами для каждого сотрудника.

В этом случае для примера возьмём таблицу student_scores, которая содержит информацию о результатах экзаменов студентов. В ней есть такие поля: student_id, exam_date, score. С помощью аналитической оконной функции SQL можно вычислить кумулятивное распределение (cumulative_distribution) для каждого студента (student_id) по их результатам экзаменов (score).

Запрос, показывающий процент студентов, которые получили оценку ниже текущей или равную ей

Значения параметров:

• PARTITION BY student_id — разделяет данные по студентам.
• ORDER BY score — упорядочивает данные по оценкам внутри каждого студента.
• CUME_DIST() OVER (...) — вычисляет кумулятивное распределение для каждой оценки студента, показывая долю оценок, которые ниже текущей оценки или равны ей.

Для примера используем таблицу stock_prices с данными о ценах акций на разные даты. В ней есть такие поля: stock_id, date, closing_price. Функция смещения поможет определить изменение цены акций (price_change) по сравнению с предыдущим днём для каждой акции (stock_id).

Запрос, показывающий динамику цен акций

Значения параметров:

• PARTITION BY stock_id — разделяет данные по акциям.
• ORDER BY date — упорядочивает данные по датам внутри каждой акции.
• LAG(closing_price, 1) OVER (...) — возвращает цену закрытия предыдущего дня для каждой акции.
• closing_price - LAG(closing_price, 1) OVER (...) — вычисляет изменение цены акций по сравнению с предыдущим днём.