Оконные функции SQL: что это такое и как работают — с примерами

Оконные функции SQL — это функции, которые выполняют вычисления над набором строк, связанных с текущей строкой, не сворачивая результат в одну строку, как это делает GROUP BY. Определяются с помощью оператора OVER() и доступны в PostgreSQL, MySQL 8+, MS SQL Server и других СУБД.

Если вы пишете SELECT, JOIN и GROUP BY на автомате, но при виде конструкции ROW_NUMBER() OVER (PARTITION BY …) теряетесь — этот материал именно для вас. Кстати, если SQL нужно немного вспомнить, можно заглянуть в статью о базовых конструкциях SQL.

В этой статье есть не только примеры кода, но и файл sql с комментариями, таблицами с данными и запросами, так что можно (и даже нужно!) поупражняться. В итоге мы пройдём путь от идеи «окна» до реальных аналитических запросов, которые можно применять в отчётах, дашбордах и backend-проектах с PostgreSQL.

Что такое оконные функции в SQL

Определение оконной функции

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

Оператор OVER() задаёт «окно» — подмножество строк, над которым оконная функция производит вычисление; PARTITION BY делит это окно на разделы, а ORDER BY определяет порядок строк внутри каждого раздела.

Чем оконные функции отличаются от агрегатных

Обычная связка SUM или AVG с GROUP BY даёт одну строку на группу — детализация теряется. Оконная функция делает то же самое вычисление, но ничего не выбрасывает: вы получаете и агрегат, и исходные строки одновременно.

Допустим, к каждой транзакции нужно добавить суммарную выручку её отдела. Через GROUP BY пришлось бы городить подзапрос или CTE или дополнительный JOIN с агрегированной таблицей. А с оконной функцией хватит одной строчки: SUM(amount) OVER (PARTITION BY department)

Синтаксис: оператор OVER()

Базовый синтаксис OVER()

Вот шаблон, по которому строится любая оконная функция:

функция([аргументы]) OVER (
    [PARTITION BY столбец1, столбец2, ...]
    [ORDER BY столбец3 [ASC|DESC], ...]
    [ROWS|RANGE BETWEEN ... AND ...]
)

Содержимое скобок можно опускать целиком, но сам OVER() обязателен — именно он превращает знакомую функцию в оконную. Пустые скобки OVER() означают: «считай по всему результату, как единому куску».

PARTITION BY — секционирование окна

PARTITION BY — это способ сказать СУБД: «раздели данные на независимые куски и считай внутри каждого отдельно». Разделы не пересекаются, и значения из одного раздела никак не влияют на соседний.

Пропустили PARTITION BY? Тогда все строки обрабатываются как один большой раздел.

На практике это выглядит так — нумеруем сотрудников по убыванию зарплаты, причём отдельно для каждого отдела:

SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Как только строки перешли к следующему отделу — нумерация начинается заново с единицы. Именно этого мы и добивались.

ORDER BY внутри OVER()

Здесь важно не запутаться: ORDER BY встречается в двух разных ролях. Внешний ORDER BY в конце запроса отвечает за сортировку вывода. ORDER BY внутри OVER() — совсем другая история: он задаёт порядок, в котором функция будет обходить строки раздела. Это влияет на сам результат вычисления, а не на то, как его показать.

Без ORDER BY ранжирующие функции вроде ROW_NUMBER и RANK теряют смысл — непонятно, кто первый, а кто последний. А для накопительных сумм именно он задаёт ось времени — поэтому важно, чтобы у дат, чисел и текстовых полей были корректные типы данных SQL:

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

ROWS / RANGE — рамка окна (фрейм)

Даже когда разделы заданы через PARTITION BY, «окно» можно сузить ещё сильнее — до нескольких строк вокруг текущей. Именно для этого и нужен фрейм. Работает он в паре с ORDER BY.

Типичные сценарии — и как они выглядят в коде:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- от первой строки раздела до текущей
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW          -- текущая + две предыдущие (окно шириной 3)
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING  -- от текущей до конца раздела

В чём разница между ROWS и RANGE? ROWS работает со строками буквально, штучно. RANGE оперирует значениями: если у нескольких строк одинаковое значение в ORDER BY, все они считаются одним «шагом» и попадают в рамку вместе.

Для чего применяются оконные функции в SQL

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

Теперь перечислим главные сценарии, с которыми сталкиваются аналитики и разработчики:

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

Практические примеры оконных функций

Пример 1 — ROW_NUMBER(): нумерация и дедупликация

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

WITH ranked AS (
    SELECT
        user_id,
        event_time,
        event_type,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY event_time DESC
        ) AS rn
    FROM events
)
SELECT user_id, event_time, event_type
FROM ranked
WHERE rn = 1;

Внутри CTE каждая строка получает свой номер — отдельно для каждого пользователя, от самой свежей к самой старой. Во внешнем запросе просто берём все строки, где rn = 1. Кстати, конструкция ROW_NUMBER() OVER (PARTITION BY … ), наверное, самый ходовой способ дедупликации в аналитике; запомните её, пригодится многократно.

Пример 2 — RANK() и DENSE_RANK(): ранжирование

Задача посложнее: найти три самых продаваемых товара в каждой категории.

SELECT
    category,
    product_name,
    revenue,
    RANK()       OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS dense_rnk
FROM products
WHERE RANK() OVER (...) <= 3;  -- ВНИМАНИЕ: так нельзя, см. раздел про ошибки

Отфильтровать «первую тройку» прямо в WHERE не получится — оконную функцию туда поставить нельзя. Значит, оборачиваем в CTE:

WITH ranked AS (
    SELECT
        category, product_name, revenue,
        DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS dr
    FROM products
)
SELECT * FROM ranked WHERE dr <= 3;

Вы спросите, в чем отличие между RANK и DENSE_RANK? Представьте, что два товара делят первое место. RANK присвоит им обоим ранг 1, а следующий получит сразу 3 (номер 2 как бы пропадает). DENSE_RANK в той же ситуации выдаст 1, 1, 2 — без «дыр». Это критично в любом запросе с OVER (PARTITION BY … ), где встречаются одинаковые значения.

Пример 3 — SUM() OVER(): нарастающий итог

Хотим к каждой транзакции добавить сумму всех предыдущих — чтобы видеть, как росла выручка день за днём.

SELECT
    txn_date,
    amount,
    SUM(amount) OVER (
        ORDER BY txn_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM transactions
ORDER BY txn_date;

Фрейм ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — «от начала раздела и до меня» — стандарт для накопительных вычислений. Небольшой подводный камень: если ORDER BY есть, а фрейм не задан, СУБД по умолчанию применит RANGE вместо ROWS. При совпадающих датах это даст не тот результат, которого вы ждёте. Берите за правило: пишете ORDER BY — пишите и фрейм.

Пример 4 — AVG() OVER(): скользящее среднее

Классика при анализе продаж — сгладить ежедневные колебания и увидеть тренд. Берём среднее за последние 7 дней.

SELECT
    sale_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales
ORDER BY sale_date;

Считаем просто: «шесть дней до плюс сегодняшний» — итого семидневное окно. Скользящее среднее убирает пики и провалы (вспомните продажи в выходные или на праздниках) и оставляет общую траекторию. Именно поэтому в финансовых и маркетинговых дашбордах этот приём встречается повсеместно.

Пример 5 — LAG() и LEAD(): доступ к соседним строкам

Финансовый отчёт в стиле «сколько выросла выручка по сравнению с прошлым месяцем». Без оконных функций пришлось бы соединять таблицу саму с собой.

SELECT
    month,
    revenue,
    LAG(revenue)  OVER (ORDER BY month) AS prev_revenue,
    LEAD(revenue) OVER (ORDER BY month) AS next_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month;

LAG(revenue) — значение из строки выше, LEAD(revenue) — из строки ниже. У обеих есть скрытые возможности: LAG(revenue, 2) заглянет на две строки назад, а LAG(revenue, 1, 0) подставит ноль, если предыдущей строки просто нет. Это самый изящный способ считать динамику в оконных функциях в SQL — никаких JOIN, никаких подзапросов.

Оконные функции vs GROUP BY: сравнение

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

Что сравниваем	GROUP BY	Оконные функции
Сколько строк на выходе	Одна на каждую группу	Столько же, сколько на входе
Что видно кроме агрегата	Только ключ группировки	Все поля исходной строки
Как отфильтровать результат	Через HAVING	Только через подзапрос или CTE
Несколько разных агрегатов	Легко, в одном запросе	Каждый со своим OVER()
Ранжирование внутри группы	Невозможно	ROW_NUMBER, RANK, DENSE_RANK
Накопительная сумма	Нужен подзапрос	SUM() OVER (ORDER BY …)
Когда выбирать	Сводки, итоговые отчёты	Аналитика, рейтинги, динамика

Если итог задачи — сводка вида «сумма по региону, средний чек по категории», берите GROUP BY. Если нужно сохранить все строки и при этом добавить к ним агрегат, ранг или соседнее значение — задача под оконные функции.

Типичные ошибки при работе с оконными функциями

1. Попытка вставить оконную функцию в WHERE. Очень частая ловушка. СУБД сначала применяет WHERE и HAVING, а оконные функции вычисляются уже потом — поэтому такой запрос падает с ошибкой:

-- НЕ РАБОТАЕТ:
SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees
WHERE rn = 1;  -- Column "rn" does not exist

-- РАБОТАЕТ: заверните в CTE или подзапрос
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT * FROM ranked WHERE rn = 1;

2. Путаница между двумя ORDER BY. Один ORDER BY сидит внутри OVER() и управляет логикой вычисления. Второй, в самом конце запроса, отвечает только за порядок вывода. Они работают независимо — держите это в голове и не удивляйтесь, если результат отсортирован «не так, как вы считали».

3. Забыли указать фрейм. Написали ORDER BY, но не дописали ROWS BETWEEN? СУБД сама подставит RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. И всё было бы хорошо, пока в ваших данных не встретятся одинаковые значения — тогда результат удивит. Повторимся, лучшая привычка: если пишете ORDER BY в окне, пишите и фрейм.

4. Лишние проходы по данным. Каждое новое уникальное OVER() теоретически может означать ещё один проход. Если в одном запросе у вас несколько одинаковых окон, используйте именованное окно — синтаксис WINDOW в PostgreSQL:

SELECT
    salary,
    SUM(salary)  OVER w AS total,
    AVG(salary)  OVER w AS avg_sal,
    RANK()       OVER w AS rnk
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

5. DISTINCT и оконные функции плохо дружат. SELECT DISTINCT сработает уже после того, как окно всё посчитает и результат может оказаться совсем не тем, что вы ожидали. Если задача именно в дедупликации, используйте CTE с ROW_NUMBER(), это и надёжнее, и предсказуемее.

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

Что такое оконные функции в SQL?

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

Для чего применяются оконные функции в SQL?

Оконные функции используют для ранжирования строк (ROW_NUMBER, RANK), подсчёта нарастающих итогов (SUM OVER), скользящего среднего (AVG OVER + ROWS), сравнения значений текущей и предыдущей строки (LAG/LEAD), а также процентильного ранжирования (PERCENT_RANK, NTILE).

Чем отличается PARTITION BY от GROUP BY в SQL?

GROUP BY сворачивает строки в группы и возвращает одну строку на группу. PARTITION BY внутри OVER() делит строки на разделы для оконной функции, но каждая исходная строка остаётся в результате. PARTITION BY не удаляет строки из выборки — это главное отличие.

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

Нет. Оконные функции вычисляются после WHERE, GROUP BY и HAVING, поэтому их нельзя использовать в WHERE напрямую. Для фильтрации по результату оконной функции нужно обернуть запрос в подзапрос или CTE и применить WHERE к нему.

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

ROW_NUMBER() присваивает уникальный номер каждой строке без пропусков. RANK() присваивает одинаковый ранг строкам с равными значениями и пропускает следующий номер (1, 1, 3). DENSE_RANK() тоже даёт одинаковый ранг равным строкам, но без пропуска следующего номера (1, 1, 2).

Заключение

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

Держите в голове четыре опорные точки: OVER() всегда обязателен; PARTITION BY ограничивает раздел; ORDER BY внутри окна управляет логикой, а не отображением; фрейм ROWS/RANGE сужает окно до конкретных строк. И одно золотое правило: фильтровать по результату оконной функции можно только через CTE.

И это не предел. Следующим шагом можно поближе познакомиться с NTILE(), PERCENT_RANK() и CUME_DIST() для работы с квантилями, а также попробовать более продвинутые кейсы применения WINDOW … AS.

Всем упорства и терпения в покорении SQL!

Советуем дополнительно почитать по теме: