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

Коротко. Оконная функция в SQL выполняет расчет по группе строк, но, в отличие от GROUP BY, не схлопывает их в одну — на выходе остается столько же строк, сколько было на входе. Задается она через ключевое слово OVER, внутри которого описывают окно: PARTITION BY делит данные на независимые группы, ORDER BY задает порядок внутри группы, а фрейм ROWS или RANGE уточняет, какие строки относительно текущей попадают в расчет.

Задача, которую не решает GROUP BY

Представьте таблицу продаж магазина спорттоваров по месяцам и отделам:

month department revenue

2026-01 Одежда 500

2026-01 Обувь 300

2026-01 Инвентарь 200

2026-02 Одежда 600

2026-02 Обувь 300

2026-02 Инвентарь 400

2026-03 Одежда 700

2026-03 Обувь 500

2026-03 Инвентарь 300

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

SELECT department, SUM(revenue) AS total

FROM sales

GROUP BY department;

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

Оконная функция решает задачу одной строкой:

SELECT

month,

department,

revenue,

SUM(revenue) OVER (PARTITION BY department) AS dept_total

FROM sales;

Результат:

month department revenue dept_total

2026-01 Одежда 500 1800

2026-02 Одежда 600 1800

2026-03 Одежда 700 1800

2026-01 Обувь 300 1100

2026-02 Обувь 300 1100

2026-03 Обувь 500 1100

2026-01 Инвентарь 200 900

2026-02 Инвентарь 400 900

2026-03 Инвентарь 300 900

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

Что такое окно и как оно задается

Синтаксис любой оконной функции выглядит так:

функция(аргументы) OVER (

PARTITION BY столбец

ORDER BY столбец

фрейм

)

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

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

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

Фрейм уточняет, какие именно строки относительно текущей участвуют в расчете. Это самая недооцененная часть синтаксиса — про нее подробнее ниже.

Все три компонента необязательны, но от их наличия результат меняется кардинально.

Где в запросе вычисляются оконные функции

Этот момент почти не объясняют в статьях для новичков, а он объясняет сразу несколько загадочных ошибок.

SQL выполняет запрос не в том порядке, в котором вы его пишете. Сначала отрабатывает FROM, потом WHERE, потом GROUP BY и HAVING, и только после этого — оконные функции. Дальше идут SELECT, DISTINCT, ORDER BY и LIMIT.

Из этого следуют два практических вывода.

Оконная функция видит только строки, прошедшие WHERE. Если вы отфильтровали данные за один месяц, сумма в окне посчитается тоже только по этому месяцу.

Оконную функцию нельзя использовать в WHERE. Такой запрос не выполнится:

SELECT month, department, revenue

FROM sales

WHERE ROW_NUMBER() OVER (PARTITION BY department ORDER BY revenue DESC) <= 2;

-- ошибка

К моменту выполнения WHERE оконная функция еще не посчитана. Как обойти это ограничение — разберем в разделе про топ-N.

Агрегатные оконные функции

Знакомые SUM, AVG, MIN, MAX, COUNT становятся оконными, если добавить к ним OVER.

SELECT

month,

department,

revenue,

SUM(revenue) OVER (PARTITION BY month) AS month_total,

AVG(revenue) OVER (PARTITION BY month) AS month_avg,

MAX(revenue) OVER (PARTITION BY month) AS month_max,

COUNT(*) OVER (PARTITION BY month) AS depts_count

FROM sales;

Для января это даст итог 1000, среднее 333.3, максимум 500 и три отдела — и все это в каждой из трех январских строк.

Полезный частный случай — доля от общего. Пустые скобки OVER () берут всю таблицу:

SELECT

month,

department,

revenue,

ROUND(revenue * 100.0 / SUM(revenue) OVER (), 1) AS share_percent

FROM sales;

Продажа одежды за январь в 500 из общих 3800 даст 13.2 процента.

Обратите внимание на умножение на 100.0, а не на 100: в большинстве СУБД деление двух целых чисел дает целое, и без дробного множителя вы получите ноль.

Накопительный итог

Стоит добавить в окно ORDER BY, как поведение агрегатной функции меняется: она начинает считать не по всей группе, а нарастающим итогом от начала до текущей строки.

SELECT

month,

department,

revenue,

SUM(revenue) OVER (

PARTITION BY department

ORDER BY month

) AS running_total

FROM sales;

Для отдела одежды получим 500, затем 1100, затем 1800. Для обуви — 300, 600, 1100.

Почему так происходит, объясняет фрейм по умолчанию. Как только в окне появляется ORDER BY, SQL неявно подставляет рамку «от первой строки до текущей». Без ORDER BY рамкой остается вся партиция целиком — поэтому в первом примере мы и получили общий итог.

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

Фреймы ROWS и RANGE

Фрейм задает рамку строк вокруг текущей. Общий синтаксис:

ROWS BETWEEN <начало> AND <конец>

Границы описывают словами: UNBOUNDED PRECEDING — от начала окна, N PRECEDING — на N строк назад, CURRENT ROW — текущая строка, N FOLLOWING — на N строк вперед, UNBOUNDED FOLLOWING — до конца окна.

Классическое применение — скользящее среднее. Посчитаем среднее за текущий и предыдущий месяц:

SELECT

month,

department,

revenue,

AVG(revenue) OVER (

PARTITION BY department

ORDER BY month

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

) AS moving_avg

FROM sales;

Для одежды выйдет 500, затем 550, затем 650.

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

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

Функции ранжирования

Три функции решают задачу «пронумеровать и расставить по местам», и различаются они поведением при одинаковых значениях. Возьмем зарплаты в отделе продаж: 90000, 85000, 85000, 70000.

SELECT

employee,

salary,

ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,

RANK() OVER (ORDER BY salary DESC) AS rank,

DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank

FROM employees;

Результат:

salary row_num rank dense_rank

90000 1 1 1

85000 2 2 2

85000 3 2 2

70000 4 4 3

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

RANK дает одинаковый ранг равным значениям, а следующий ранг пропускает — после двух вторых мест идет четвертое.

DENSE_RANK тоже дает одинаковый ранг равным, но идет дальше без пропусков — после двух вторых мест идет третье.

Есть и четвертая функция — NTILE(n). Она делит строки окна на n примерно равных корзин и проставляет номер корзины. Так удобно разбивать клиентов на квартили по сумме покупок:

SELECT

customer,

total_spent,

NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile

FROM customers;

Первая четверть клиентов получит единицу, последняя — четверку.

Функции смещения: LAG и LEAD

Эти функции позволяют заглянуть в соседние строки окна и достать оттуда значение — без всяких самосоединений.

LAG смотрит назад, LEAD — вперед. Самая частая задача: сравнить показатель с предыдущим периодом.

SELECT

month,

department,

revenue,

LAG(revenue) OVER (

PARTITION BY department

ORDER BY month

) AS prev_month,

revenue - LAG(revenue) OVER (

PARTITION BY department

ORDER BY month

) AS growth

FROM sales;

Для одежды получим прирост NULL, затем 100, затем 100. У первой строки каждой группы предыдущего значения нет — отсюда NULL.

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

LAG(revenue, 1, 0) OVER (PARTITION BY department ORDER BY month)

Здесь смещение на одну строку, а вместо пустого значения подставится ноль. Это удобно, когда результат идет в дальнейшие вычисления, где NULL все испортит.

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

FIRST_VALUE, LAST_VALUE и ловушка фрейма

FIRST_VALUE возвращает первое значение в окне, LAST_VALUE — последнее. С первой функцией проблем не бывает, а вот вторая — источник самой известной ошибки в теме оконных функций.

SELECT

month,

department,

revenue,

LAST_VALUE(revenue) OVER (

PARTITION BY department

ORDER BY month

) AS last_revenue

FROM sales;

Кажется, что в каждой строке появится продажа последнего месяца. На деле в столбце окажется значение текущей строки.

Причина в том самом фрейме по умолчанию: раз есть ORDER BY, рамка тянется от начала окна до текущей строки. Внутри такой рамки последняя строка — это и есть текущая. Чтобы получить настоящее последнее значение, рамку надо задать явно:

LAST_VALUE(revenue) OVER (

PARTITION BY department

ORDER BY month

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

)

Именно на этой ошибке спотыкаются даже опытные аналитики, и именно ее любят спрашивать на собеседованиях.

Практика: топ-N записей в каждой группе

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

WITH ranked AS (

SELECT

month,

department,

revenue,

ROW_NUMBER() OVER (

PARTITION BY department

ORDER BY revenue DESC

) AS rn

FROM sales

)

SELECT month, department, revenue

FROM ranked

WHERE rn <= 2;

Внутренний запрос считает номер строки, внешний фильтрует. Этот шаблон стоит запомнить: он подходит для любой задачи вида «первые N в каждой категории».

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

В некоторых СУБД (ClickHouse, BigQuery, Snowflake, DuckDB) есть более короткая запись через QUALIFY, которая позволяет фильтровать по окну без подзапроса. В PostgreSQL и MySQL ее нет, поэтому универсальный вариант — CTE.

Практика: убираем дубликаты

Второй шаблон, который экономит часы работы. Допустим, в таблице клиентов задвоились записи по email, и нужно оставить только самую свежую.

WITH numbered AS (

SELECT

*,

ROW_NUMBER() OVER (

PARTITION BY email

ORDER BY created_at DESC

) AS rn

FROM customers

)

SELECT *

FROM numbered

WHERE rn = 1;

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

Именованные окна

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

SELECT

month,

department,

revenue,

SUM(revenue) OVER w AS running_total,

AVG(revenue) OVER w AS running_avg,

ROW_NUMBER() OVER w AS rn

FROM sales

WINDOW w AS (PARTITION BY department ORDER BY month);

Запрос становится короче и понятнее, а при правке окна не нужно вносить одно и то же изменение в трех местах. Конструкцию WINDOW поддерживают PostgreSQL, MySQL начиная с версии 8.0 и большинство современных СУБД.

Где оконные функции поддерживаются

Стандарт описал их еще в 2003 году, но в популярных базах они появлялись по-разному.

В PostgreSQL они есть с версии 8.4 и поддержаны полнее всего. В MySQL — только с версии 8.0, вышедшей в 2018 году, в более старых версиях их нет вообще. В MariaDB — с 10.2, в SQLite — с 3.25. Oracle, SQL Server и ClickHouse поддерживают их давно.

Практический вывод: если запрос с OVER выдает синтаксическую ошибку, первым делом проверьте версию СУБД. На старом MySQL 5.7 такой код просто не заработает.

Отличаются и детали. Например, конструкция FILTER (WHERE ...), которая позволяет считать функцию только по части строк, есть в PostgreSQL и SQLite, но отсутствует в MySQL — там ту же задачу решают через CASE внутри агрегата.

Аналитические функции распределения

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

CUME_DIST возвращает долю строк со значением меньше или равным текущему — число от 0 до 1.

PERCENT_RANK показывает относительный ранг: первая строка получает 0, последняя 1.

PERCENTILE_CONT и PERCENTILE_DISC считают процентили, в том числе медиану. Первая при необходимости интерполирует значение между двумя соседними, вторая возвращает реальное значение из данных.

SELECT

category,

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price)

OVER (PARTITION BY category) AS median_price

FROM products;

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

Частые ошибки

Оконная функция в WHERE. Не работает по порядку выполнения запроса. Выносите расчет в CTE или подзапрос.

Забытый ORDER BY внутри OVER. Без него ROW_NUMBER расставит номера в произвольном порядке, а SUM вернет общий итог вместо накопительного.

Путаница между ORDER BY в окне и в запросе. Сортировка внутри OVER влияет только на расчет функции. Порядок вывода строк задает отдельный ORDER BY в конце запроса.

LAST_VALUE без явного фрейма. Возвращает текущую строку вместо последней. Разобрано выше.

Ожидание, что PARTITION BY схлопнет строки. Он не группирует, а только делит окно. Количество строк на выходе всегда равно количеству на входе.

Деление целых чисел. При расчете долей используйте 100.0, иначе результат округлится до нуля.

Забытая обработка NULL. LAG и LEAD на границах окна возвращают NULL, и дальнейшая арифметика с ними даст NULL. Подставляйте значение по умолчанию третьим аргументом.

Шпаргалка: какая функция для какой задачи

• Итог по группе рядом с каждой строкой — SUM() OVER (PARTITION BY ...)
• Накопительный итог — SUM() OVER (PARTITION BY ... ORDER BY ...)
• Доля от общего — value / SUM(value) OVER ()
• Скользящее среднее — AVG() OVER (... ROWS BETWEEN N PRECEDING AND CURRENT ROW)
• Пронумеровать строки — ROW_NUMBER()
• Расставить места с учетом равных значений — RANK() или DENSE_RANK()
• Разбить на квартили или децили — NTILE(n)
• Сравнить с предыдущим периодом — LAG()
• Заглянуть в следующий период — LEAD()
• Первое или последнее значение группы — FIRST_VALUE() и LAST_VALUE() с явным фреймом
• Топ-N в каждой категории — ROW_NUMBER() внутри CTE плюс фильтр снаружи
• Удалить дубликаты — ROW_NUMBER() по ключу дубликата плюс условие на единицу

Коротко о главном

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

Две вещи объясняют большинство неожиданных результатов. Первая: при наличии ORDER BY фрейм по умолчанию тянется от начала окна до текущей строки — отсюда и накопительные итоги, и странное поведение LAST_VALUE. Вторая: оконные функции вычисляются после WHERE, поэтому фильтровать по их результату можно только через подзапрос или CTE.

Для аналитика данных этот инструмент обязательный: когорты, воронки, динамика к прошлому периоду и топ-N по категориям без него превращаются в громоздкие запросы. Разобраться в SQL, статистике и работе с данными с нуля можно на курсе Аналитик данных в PROWEB — 5 месяцев, с 16 лет. Если хотите двигаться в сторону машинного обучения, посмотрите курс Data Science, а определиться с направлением поможет бесплатный IT-тест.

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

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

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

Что делает ключевое слово OVER?

Оно превращает обычную функцию в оконную и описывает окно — набор строк, по которому идет расчет. Внутри скобок задают PARTITION BY для деления на группы, ORDER BY для порядка и фрейм для рамки. Пустые скобки означают, что окном будет вся выборка.

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

Нет. SQL вычисляет оконные функции после WHERE, поэтому на момент фильтрации результата еще не существует. Чтобы отфильтровать по нему, вынесите расчет в подзапрос или CTE, а условие поставьте во внешнем запросе.

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

Обе функции дают одинаковый ранг строкам с равными значениями. Разница в том, что идет дальше: RANK пропускает ранги, поэтому после двух вторых мест следует четвертое, а DENSE_RANK идет без пропусков, и следующим будет третье.

Почему LAST_VALUE возвращает не то значение?

Потому что при наличии ORDER BY фрейм по умолчанию заканчивается на текущей строке, и последней в рамке оказывается она сама. Чтобы получить настоящее последнее значение окна, добавьте ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Чем ROWS отличается от RANGE?

ROWS отсчитывает физические строки, RANGE работает по значениям столбца сортировки и включает в рамку все строки с одинаковым значением. При уникальных значениях результат совпадает, при дубликатах — расходится. Для предсказуемости чаще выбирают ROWS.

Как вывести топ-3 записи в каждой группе?

Посчитать ROW_NUMBER() с разбиением по группе и сортировкой по нужному показателю внутри CTE или подзапроса, а во внешнем запросе оставить строки с номером не больше трех.

Работают ли оконные функции в MySQL?

Да, начиная с версии 8.0. В MySQL 5.7 и более ранних их нет, и запрос с OVER выдаст синтаксическую ошибку. В PostgreSQL они доступны давно, в SQLite — с версии 3.25.

Замедляют ли оконные функции запрос?

Обычно наоборот: они заменяют подзапросы и самосоединения, которые работают медленнее. Но сортировка внутри окна требует ресурсов, поэтому на больших таблицах помогают индексы по столбцам из PARTITION BY и ORDER BY.

Что такое фрейм окна?

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