Оконные функции в SQL — что это и зачем они нужны

, отредактировано
Обложка: Оконные функции в SQL — что это и зачем они нужны

Многие разработчики, даже давно знакомые с SQL, не понимают оконные функции, считая их какой-то особой магией для избранных. И, хотя реализация оконных функций поддерживается с SQL Server 2005, кто-то до сих пор «копипастит» их со StackOverflow, не вдаваясь в детали. Этой статьёй мы попытаемся развенчать миф о неприступности этой функциональности SQL и покажем несколько примеров работы оконных функций на реальном датасете.

Если вы только начинаете изучать SQL, рекомендуем сначала ознакомиться с основными командами SQL — SELECT, FROM, WHERE, GROUP BY, JOIN. Понимая эти основы, разобраться с оконными функциями будет значительно проще. Эта статья — часть нашего полного путеводителя по SQL.

Ключевые выводы

- Оконные функции не сокращают количество строк — в отличие от GROUP BY, они возвращают столько же строк, сколько получили
- Синтаксис начинается с OVER(), внутри которого задаются PARTITION BY, ORDER BY и оконные рамки (ROWS/RANGE)
- ROW_NUMBER() нумерует строки, RANK() и DENSE_RANK() ранжируют с учётом одинаковых значений
- LAG() возвращает значение из предыдущей строки, LEAD() — из следующей
- PARTITION BY делит данные на группы, внутри которых функция вычисляется отдельно
- Агрегатные функции (SUM, AVG, COUNT) тоже работают как оконные — для кумулятивных сумм и скользящих средних
- ROWS BETWEEN задаёт оконную рамку — диапазон строк, по которому вычисляется функция

Почему не GROUP BY и не JOIN

Сразу проясним, что оконные функции — это не то же самое, что GROUP BY. Они не уменьшают количество строк, а возвращают столько же значений, сколько получили на вход. Во-вторых, в отличие от GROUP BY, OVER может обращаться к другим строкам. И в-третьих, они могут считать скользящие средние и кумулятивные суммы.

Окей, с GROUP BY разобрались. Но в SQL практически всегда можно пойти несколькими путями. К примеру, может возникнуть желание использовать подзапросы или JOIN. Конечно, JOIN по производительности предпочтительнее подзапросов, а производительность конструкций JOIN и OVER окажется одинаковой. Но OVER даёт больше свободы, чем жёсткий JOIN. Да и объём кода в итоге окажется гораздо меньше.

Для начала

Оконные функции начинаются с оператора OVER и настраиваются с помощью трёх других операторов: PARTITION BY, ORDER BY и ROWS. Про ORDER BY, PARTITION BY и вспомогательные функции LAG, LEAD, RANK мы расскажем подробнее.
Все примеры основаны на датасете олимпийских медалистов от Datacamp. Таблица называется summer_medals и содержит результаты Олимпиад с 1896 по 2010:

ROW_NUMBER и ORDER BY

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

			SELECT
athlete,
event,
ROW_NUMBER() OVER() AS row_number
FROM Summer_Medals
ORDER BY row_number ASC;
Результат ROW_NUMBER без ORDER BY в OVER
			SELECT
sport,
ROW_NUMBER() OVER(ORDER BY sport ASC) AS Row_N
FROM (
SELECT DISTINCT sport
FROM Summer_Medals
) AS sports
ORDER BY sport ASC;
Результат ROW_NUMBER с ORDER BY в OVER

PARTITION BY и LAG, LEAD и RANK

PARTITION BY позволяет сгруппировать строки по значению определённого столбца. Это полезно, если данные логически делятся на какие-то категории и нужно что-то сделать с данной строкой с учётом других строк той же группы (скажем, сравнить теннисиста с остальными теннисистами, но не с бегунами или пловцами). Этот оператор работает только с оконными функциями типа LAG, LEAD, RANK и т. д.

LAG

Функция LAG берёт строку и возвращает ту, которая шла перед ней. Например, мы хотим найти всех олимпийских чемпионов по теннису (мужчин и женщин отдельно), начиная с 2004 года, и для каждого из них выяснить, кто был предыдущим чемпионом.
Решение этой задачи требует нескольких шагов. Сначала надо создать табличное выражение, которое сохранит результат запроса «чемпионы по теннису с 2004 года» как временную именованную структуру для дальнейшего анализа. А затем разделить их по полу и выбрать предыдущего чемпиона с помощью LAG:

			-- Табличное выражение ищет теннисных чемпионов и выбирает нужные столбцы
WITH Tennis_Gold AS (
SELECT
Athlete,
Gender,
Year,
Country
FROM
Summer_Medals
WHERE
Year >= 2004 AND
Sport = 'Tennis' AND
event = 'Singles' AND
Medal = 'Gold')
		
			-- Оконная функция разделяет по полу и берёт чемпиона из предыдущей строки
SELECT
Athlete as Champion,
Gender,
Year,
LAG(Athlete) OVER (PARTITION BY gender
ORDER BY Year ASC) AS Last_Champion
FROM Tennis_Gold
ORDER BY Gender ASC, Year ASC;
Результат LAG с PARTITION BY по полу

LEAD

Функция LEAD похожа на LAG, но вместо предыдущей строки возвращает следующую. Можно узнать, кто стал следующим чемпионом после того или иного спортсмена:

			-- Табличное выражение ищет теннисных чемпионов и выбирает нужные столбцы
WITH Tennis_Gold AS (
SELECT
Athlete,
Gender,
Year,
Country
FROM
Summer_Medals
WHERE
Year >= 2004 AND
Sport = 'Tennis' AND
event = 'Singles' AND
Medal = 'Gold')
		
			-- Оконная функция разделяет по полу и берёт чемпиона из следующей строки
SELECT
Athlete as Champion,
Gender,
Year,
LEAD(Athlete) OVER (PARTITION BY gender
ORDER BY Year ASC) AS Future_Champion
FROM Tennis_Gold
ORDER BY Gender ASC, Year ASC;
Результат LEAD с PARTITION BY по полу

RANK

Оператор RANK похож на ROW_NUMBER, но присваивает одинаковые номера строкам с одинаковыми значениями, а «лишние» номера пропускает. Есть также DENSE_RANK, который не пропускает номеров. Звучит запутанно, так что проще показать на примере. Вот ранжирование стран по числу олимпиад, в которых они участвовали, разными операторами:

Сравнение ROW_NUMBER, RANK и DENSE_RANK
  • Row_number — ничего интересного, строки просто пронумерованы по возрастанию.
  • Rank_number — строки ранжированы по возрастанию, но нет номера 3. Вместо этого, 2 строки делят номер 2, а за ними сразу идёт номер 4.
  • Dense_rank — то же самое, что и rank_number, но номер 3 не пропущен. Номера идут подряд, но зато никто не оказался пятым из пяти.

На практике RANK полезен в соревновательных сценариях: если два спортсмена разделили второе место, следующий должен быть четвёртым, а не третьим — ведь перед ним реально три человека. DENSE_RANK удобнее для задач вроде «покажи топ-3 категории», где важна непрерывная нумерация без пропусков.

Вот код:

			-- Табличное выражение выбирает страны и считает годы
WITH countries AS (
SELECT
Country,
COUNT(DISTINCT year) AS participated
FROM
Summer_Medals
WHERE
Country in ('GBR', 'DEN', 'FRA', 'ITA','AUT')
GROUP BY
Country)

-- Разные оконные функции ранжируют страны
SELECT
Country,
participated,
ROW_NUMBER()
OVER(ORDER BY participated DESC) AS Row_Number,
RANK()
OVER(ORDER BY participated DESC) AS Rank_Number,
DENSE_RANK()
OVER(ORDER BY participated DESC) AS Dense_Rank
FROM countries
ORDER BY participated DESC;

Агрегирующие оконные функции

Привычные агрегатные функции — SUM, AVG, COUNT, MAX, MIN — тоже можно использовать как оконные. В этом случае они вычисляют значение по группе строк, но не схлопывают результат в одну строку, как GROUP BY. Это позволяет, например, показать каждую строку рядом с итогом по группе.

Кумулятивная сумма медалей по годам для каждой страны:

			SELECT
  Country,
  Year,
  COUNT(*) AS medals,
  SUM(COUNT(*)) OVER (
    PARTITION BY Country
    ORDER BY Year
  ) AS cumulative_medals
FROM Summer_Medals
WHERE Country IN ('GBR', 'FRA', 'ITA')
GROUP BY Country, Year
ORDER BY Country, Year;

SUM с OVER и ORDER BY автоматически вычисляет нарастающий итог: для каждой строки суммируются все значения от начала группы до текущей строки включительно.

Скользящее среднее — среднее количество медалей за текущую и две предыдущие Олимпиады:

			SELECT
  Country,
  Year,
  COUNT(*) AS medals,
  ROUND(AVG(COUNT(*)) OVER (
    PARTITION BY Country
    ORDER BY Year
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 1) AS moving_avg
FROM Summer_Medals
WHERE Country IN ('GBR', 'FRA')
GROUP BY Country, Year
ORDER BY Country, Year;

Обратите внимание на ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — это оконная рамка, которая ограничивает вычисление тремя строками. Подробнее о рамках — в следующем разделе.

ROWS и RANGE — оконные рамки

По умолчанию оконная функция с ORDER BY вычисляется от начала группы до текущей строки. Но это поведение можно настроить с помощью оконных рамок — ROWS и RANGE. Рамка записывается после ORDER BY:

			-- Скользящее среднее за 3 строки
-- (текущая + 2 предыдущие)
AVG(value) OVER (
  ORDER BY year
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

-- Сумма по всей группе
-- (от начала до конца)
SUM(value) OVER (
  ORDER BY year
  ROWS BETWEEN UNBOUNDED PRECEDING
    AND UNBOUNDED FOLLOWING
)

-- Среднее по окну из 3 строк
-- (одна до, текущая, одна после)
AVG(value) OVER (
  ORDER BY year
  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)

Разница между ROWS и RANGE: ROWS считает физические строки, а RANGE — логические значения. Если в ORDER BY есть одинаковые значения, RANGE включит все строки с тем же значением, а ROWS — только указанное количество. В большинстве случаев ROWS предсказуемее и используется чаще.

Напоследок

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

Помимо рассмотренных функций существуют и другие полезные инструменты: NTILE для разбиения на равные группы, FIRST_VALUE и LAST_VALUE для доступа к граничным значениям окна, а также PERCENT_RANK и CUME_DIST для процентильного ранжирования.

Часто задаваемые вопросы

Чем оконные функции отличаются от GROUP BY?

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

В чём разница между RANK и DENSE_RANK?

RANK пропускает номера после одинаковых значений (1, 2, 2, 4), а DENSE_RANK — нет (1, 2, 2, 3). Выбор зависит от задачи: DENSE_RANK удобнее для отображения «top-N», а RANK точнее отражает фактическую позицию.

Когда лучше использовать LAG/LEAD, а когда self-JOIN?

LAG/LEAD проще и читабельнее, когда нужно обратиться к соседней строке в рамках упорядоченного набора. Self-JOIN оправдан, если требуется более сложная логика соединения — например, сопоставление строк по нескольким условиям, не связанным с порядком.

Поддерживают ли оконные функции все СУБД?

Оконные функции входят в стандарт SQL:2003 и поддерживаются всеми основными СУБД: PostgreSQL, MySQL (с версии 8.0), SQL Server (с 2005), Oracle, SQLite (с 3.25). Синтаксис практически идентичен, различия касаются лишь некоторых специфических функций.

Можно ли использовать SUM и AVG как оконные функции?

Да. Все стандартные агрегатные функции (SUM, AVG, COUNT, MAX, MIN) работают как оконные, если добавить OVER(). В отличие от GROUP BY, они не схлопывают строки, а добавляют вычисленное значение к каждой строке. Это удобно для кумулятивных сумм, скользящих средних и сравнения строки с итогом по группе.

Что такое оконная рамка (ROWS BETWEEN)?

Оконная рамка определяет диапазон строк, по которым вычисляется функция. Указывается после ORDER BY: например, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW означает «текущая строка и две предыдущие». Без явной рамки используется диапазон от начала группы до текущей строки. Оконные функции часто спрашивают на собеседовании — подборка вопросов.

В этой статье
  1. Почему не GROUP BY и не JOIN
  2. Для начала
  3. ROW_NUMBER и ORDER BY
  4. PARTITION BY и LAG, LEAD и RANK
  5. Агрегирующие оконные функции
  6. ROWS и RANGE — оконные рамки
  7. Напоследок
  8. Часто задаваемые вопросы
Следите за новыми постами
Следите за новыми постами по любимым темам
Рекомендуем

Нашли опечатку? Выделите фрагмент и
отправьте нажатием Ctrl+Enter