Оконные функции SQL: руководство с примерами
Что такое оконные функции
Оконные функции — один из самых мощных инструментов SQL. Они позволяют выполнять вычисления по набору строк, связанных с текущей строкой, но при этом не схлопывают результат. Это ключевое отличие от GROUP BY: после группировки вы теряете отдельные строки, а с оконной функцией — сохраняете каждую строку и добавляете к ней вычисленное значение.
Допустим, есть таблица orders:
| order_id | department | amount |
|---|---|---|
| 1 | Продажи | 50000 |
| 2 | Продажи | 30000 |
| 3 | Маркетинг | 40000 |
| 4 | Маркетинг | 25000 |
| 5 | Продажи | 45000 |
Задача: показать каждый заказ и рядом — общую сумму по его отделу.
С GROUP BY вы получите только агрегат:
SELECT department, SUM(amount) AS dept_total
FROM orders
GROUP BY department| department | dept_total |
|---|---|
| Продажи | 125000 |
| Маркетинг | 65000 |
Отдельные заказы пропали. А если нужно видеть и каждый заказ, и итог по отделу?
С оконной функцией строки остаются на месте:
SELECT
order_id,
department,
amount,
SUM(amount) OVER (PARTITION BY department) AS dept_total
FROM orders| order_id | department | amount | dept_total |
|---|---|---|---|
| 1 | Продажи | 50000 | 125000 |
| 2 | Продажи | 30000 | 125000 |
| 5 | Продажи | 45000 | 125000 |
| 3 | Маркетинг | 40000 | 65000 |
| 4 | Маркетинг | 25000 | 65000 |
Каждая строка сохранилась, но рядом появился итог по отделу. Именно поэтому оконные функции называют «окнами» — они смотрят на группу (окно) строк, вычисляют значение и записывают его в текущую строку.
Оконные функции поддерживаются во всех основных СУБД: PostgreSQL, MySQL 8+, ClickHouse, BigQuery, Redshift, Snowflake. Синтаксис практически одинаковый.
Синтаксис OVER()
Общая формула оконной функции:
функция(...) OVER (
[PARTITION BY столбец1, столбец2, ...]
[ORDER BY столбец3 [ASC|DESC], ...]
[ROWS|RANGE BETWEEN ... AND ...]
)Разберём каждую часть.
Функция — что вычисляем. Это может быть агрегатная функция (SUM, AVG, COUNT, MIN, MAX) или специальная оконная (ROW_NUMBER, RANK, LAG, LEAD и другие).
PARTITION BY — как разбиваем строки на группы (окна). Аналог GROUP BY, но без схлопывания. Если PARTITION BY не указан, окном считается весь результирующий набор.
ORDER BY — в каком порядке строки внутри окна. Обязателен для функций ранжирования и смещения. Для агрегатных функций влияет на фрейм (об этом ниже).
Фрейм (ROWS BETWEEN / RANGE BETWEEN) — какие именно строки из окна участвуют в вычислении. По умолчанию зависит от наличия ORDER BY.
Пример: нумерация заказов внутри каждого отдела по убыванию суммы:
SELECT
order_id,
department,
amount,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY amount DESC
) AS rank_in_dept
FROM orders| order_id | department | amount | rank_in_dept |
|---|---|---|---|
| 1 | Продажи | 50000 | 1 |
| 5 | Продажи | 45000 | 2 |
| 2 | Продажи | 30000 | 3 |
| 3 | Маркетинг | 40000 | 1 |
| 4 | Маркетинг | 25000 | 2 |
PARTITION BY department создаёт два окна (Продажи и Маркетинг), ORDER BY amount DESC задаёт порядок внутри каждого окна, ROW_NUMBER() нумерует строки.
Функции ранжирования
SQL предлагает четыре функции для присвоения рангов строкам. Они похожи, но ведут себя по-разному при одинаковых значениях.
Возьмём таблицу scores:
| student | score |
|---|---|
| Алиса | 95 |
| Борис | 90 |
| Вика | 90 |
| Гриша | 85 |
| Даша | 80 |
ROW_NUMBER()
Присваивает уникальный порядковый номер каждой строке. При одинаковых значениях порядок между ними не определён (зависит от СУБД).
SELECT
student,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
FROM scores| student | score | rn |
|---|---|---|
| Алиса | 95 | 1 |
| Борис | 90 | 2 |
| Вика | 90 | 3 |
| Гриша | 85 | 4 |
| Даша | 80 | 5 |
Борис и Вика набрали одинаково, но получили разные номера (2 и 3). Кто из них второй — зависит от порядка, в котором СУБД обработала строки.
RANK()
При одинаковых значениях присваивает одинаковый ранг, но следующий ранг пропускается.
SELECT
student,
score,
RANK() OVER (ORDER BY score DESC) AS rnk
FROM scores| student | score | rnk |
|---|---|---|
| Алиса | 95 | 1 |
| Борис | 90 | 2 |
| Вика | 90 | 2 |
| Гриша | 85 | 4 |
| Даша | 80 | 5 |
Борис и Вика — оба на 2-м месте. Ранга 3 нет, следующий — 4.
DENSE_RANK()
Как RANK(), но без пропусков — ранги идут подряд.
SELECT
student,
score,
DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM scores| student | score | drnk |
|---|---|---|
| Алиса | 95 | 1 |
| Борис | 90 | 2 |
| Вика | 90 | 2 |
| Гриша | 85 | 3 |
| Даша | 80 | 4 |
После двух вторых мест сразу идёт третье, а не четвёртое.
NTILE(n)
Делит строки на n примерно равных групп (квартили, децили и т.д.).
SELECT
student,
score,
NTILE(3) OVER (ORDER BY score DESC) AS tile
FROM scores| student | score | tile |
|---|---|---|
| Алиса | 95 | 1 |
| Борис | 90 | 1 |
| Вика | 90 | 2 |
| Гриша | 85 | 2 |
| Даша | 80 | 3 |
5 строк, 3 группы — первые две группы получают по 2 строки, третья — 1.
Сравнение функций ранжирования
| Ситуация | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| Уникальные значения | 1, 2, 3, 4 | 1, 2, 3, 4 | 1, 2, 3, 4 |
| Два одинаковых на 2-м месте | 2, 3 | 2, 2 | 2, 2 |
| Следующее значение после дубля | 4 | 4 | 3 |
Правило выбора: нужен уникальный номер — ROW_NUMBER. Нужен «честный» ранг с пропусками — RANK. Нужен ранг без пропусков — DENSE_RANK.
Функции смещения
Функции смещения позволяют обращаться к значению из другой строки окна — предыдущей, следующей, первой или последней.
LAG и LEAD
LAG(столбец, n) — значение на n строк назад. LEAD(столбец, n) — на n строк вперёд. По умолчанию n = 1.
Таблица daily_revenue:
| day | revenue |
|---|---|
| 2026-04-01 | 100000 |
| 2026-04-02 | 120000 |
| 2026-04-03 | 95000 |
| 2026-04-04 | 130000 |
| 2026-04-05 | 110000 |
Задача: показать выручку за предыдущий день и изменение.
SELECT
day,
revenue,
LAG(revenue) OVER (ORDER BY day) AS prev_day_revenue,
revenue - LAG(revenue) OVER (ORDER BY day) AS change
FROM daily_revenue| day | revenue | prev_day_revenue | change |
|---|---|---|---|
| 2026-04-01 | 100000 | NULL | NULL |
| 2026-04-02 | 120000 | 100000 | 20000 |
| 2026-04-03 | 95000 | 120000 | -25000 |
| 2026-04-04 | 130000 | 95000 | 35000 |
| 2026-04-05 | 110000 | 130000 | -20000 |
Для первой строки предыдущего значения нет — возвращается NULL. Можно задать значение по умолчанию третьим аргументом: LAG(revenue, 1, 0).
LEAD работает аналогично, но смотрит вперёд:
SELECT
day,
revenue,
LEAD(revenue) OVER (ORDER BY day) AS next_day_revenue
FROM daily_revenue| day | revenue | next_day_revenue |
|---|---|---|
| 2026-04-01 | 100000 | 120000 |
| 2026-04-02 | 120000 | 95000 |
| 2026-04-03 | 95000 | 130000 |
| 2026-04-04 | 130000 | 110000 |
| 2026-04-05 | 110000 | NULL |
FIRST_VALUE и LAST_VALUE
FIRST_VALUE(столбец) — значение из первой строки окна. LAST_VALUE(столбец) — из последней.
SELECT
day,
revenue,
FIRST_VALUE(revenue) OVER (ORDER BY day) AS first_day_revenue,
revenue - FIRST_VALUE(revenue) OVER (ORDER BY day) AS diff_from_start
FROM daily_revenue| day | revenue | first_day_revenue | diff_from_start |
|---|---|---|---|
| 2026-04-01 | 100000 | 100000 | 0 |
| 2026-04-02 | 120000 | 100000 | 20000 |
| 2026-04-03 | 95000 | 100000 | -5000 |
| 2026-04-04 | 130000 | 100000 | 30000 |
| 2026-04-05 | 110000 | 100000 | 10000 |
С LAST_VALUE есть подвох: по умолчанию при наличии ORDER BY фрейм заканчивается на текущей строке, а не на последней строке окна. Чтобы LAST_VALUE вернула значение из действительно последней строки, нужно явно указать фрейм:
LAST_VALUE(revenue) OVER (
ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)Это одна из классических ловушек — подробнее про фреймы в следующем разделе.
Агрегатные функции с OVER
Любую агрегатную функцию (SUM, AVG, COUNT, MIN, MAX) можно превратить в оконную, добавив OVER().
Нарастающий итог (Running Total)
Классическая задача — показать, как накапливается выручка день за днём.
SELECT
day,
revenue,
SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue| day | revenue | running_total |
|---|---|---|
| 2026-04-01 | 100000 | 100000 |
| 2026-04-02 | 120000 | 220000 |
| 2026-04-03 | 95000 | 315000 |
| 2026-04-04 | 130000 | 445000 |
| 2026-04-05 | 110000 | 555000 |
Когда вы пишете SUM(revenue) OVER (ORDER BY day), SQL по умолчанию суммирует от начала окна до текущей строки (фрейм ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Поэтому получается нарастающий итог.
Скользящее среднее
Среднее за последние 3 дня:
SELECT
day,
revenue,
AVG(revenue) OVER (
ORDER BY day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3d
FROM daily_revenue| day | revenue | moving_avg_3d |
|---|---|---|
| 2026-04-01 | 100000 | 100000 |
| 2026-04-02 | 120000 | 110000 |
| 2026-04-03 | 95000 | 105000 |
| 2026-04-04 | 130000 | 115000 |
| 2026-04-05 | 110000 | 111667 |
Для первых строк в окне меньше трёх значений — среднее считается по тому, что есть.
Доля от общей суммы
Какую долю выручки принёс каждый отдел:
SELECT
department,
amount,
SUM(amount) OVER () AS total,
ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS pct
FROM ordersOVER () без PARTITION BY и без ORDER BY — окно охватывает все строки. Это удобный способ получить общий итог рядом с каждой строкой.
Фреймы: ROWS BETWEEN и RANGE BETWEEN
Фрейм определяет, какие строки внутри окна участвуют в вычислении. Это самая тонкая часть оконных функций.
Синтаксис фрейма
ROWS BETWEEN начало AND конецГде начало и конец — одно из:
UNBOUNDED PRECEDING— начало окнаn PRECEDING— n строк назадCURRENT ROW— текущая строкаn FOLLOWING— n строк вперёдUNBOUNDED FOLLOWING— конец окна
Фрейм по умолчанию
Это важно:
- Без ORDER BY: фрейм = всё окно (
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - С ORDER BY: фрейм = от начала до текущей строки (
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Именно поэтому SUM(...) OVER (ORDER BY day) даёт нарастающий итог, а SUM(...) OVER () — общую сумму по всем строкам.
ROWS vs RANGE
ROWS — считает физические строки. 2 PRECEDING означает ровно 2 строки назад.
RANGE — считает по значению. 2 PRECEDING означает строки, где значение ORDER BY-столбца отличается от текущего не больше чем на 2. Если несколько строк имеют одинаковое значение, все они попадут в фрейм.
На практике ROWS используется гораздо чаще — поведение предсказуемее и понятнее. RANGE полезен, когда в данных есть пропуски (например, нет записи за выходные) и вы хотите считать среднее по диапазону дат, а не по количеству строк.
Типичные паттерны фреймов
Нарастающий итог:
SUM(x) OVER (ORDER BY DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)Скользящее среднее за 7 дней:
AVG(x) OVER (ORDER BY DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)6 PRECEDING + текущая строка = 7 строк. Частая ошибка — писать 7 PRECEDING, получая окно из 8 строк.
Общий итог по всему окну (для долей):
SUM(x) OVER ()Сумма от текущей строки до конца:
SUM(x) OVER (ORDER BY DATE ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)Совет: лучший способ понять оконные функции — взять реальную таблицу, написать запрос и вручную проверить результат на 5-10 строках. Когда вы один раз увидите, как фрейм «скользит» по строкам, всё встанет на свои места.
Практические задачи
Задача 1: Top-N в каждой группе
Найти по 2 самых дорогих заказа в каждом отделе.
WITH ranked AS (
SELECT
order_id,
department,
amount,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY amount DESC
) AS rn
FROM orders
)
SELECT order_id, department, amount
FROM ranked
WHERE rn <= 2Почему ROW_NUMBER, а не RANK? Потому что RANK при одинаковых значениях выдаст одинаковый ранг, и в результат могут попасть 3+ строки вместо ровно 2. ROW_NUMBER гарантирует уникальные номера.
Задача 2: Нарастающий итог выручки по месяцам
Показать выручку за каждый месяц и накопленный итог с начала года.
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS monthly_revenue,
SUM(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS ytd_revenue
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY monthОбратите внимание на SUM(SUM(amount)) — внутренний SUM агрегирует по GROUP BY, а внешний SUM с OVER вычисляет нарастающий итог. Оконные функции выполняются после GROUP BY, поэтому такая вложенность допустима.
Задача 3: Скользящее среднее за 7 дней
Посчитать 7-дневное скользящее среднее количества заказов.
SELECT
order_date,
COUNT(*) AS daily_orders,
AVG(COUNT(*)) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7d
FROM orders
GROUP BY order_date
ORDER BY order_dateСнова AVG(COUNT(*)) — сначала GROUP BY считает количество заказов за день, затем оконная AVG берёт среднее за 7 строк (текущая + 6 предыдущих).
Задача 4: Сравнение с предыдущим периодом
Для каждого месяца показать выручку, выручку за прошлый месяц и процент изменения.
WITH monthly AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month),
1
) AS change_pct
FROM monthly
ORDER BY monthLAG здесь вызывается несколько раз — это нормально, СУБД оптимизирует повторные вызовы с одинаковым окном.
Задача 5: Доля от общей суммы по категории
Показать каждый заказ и его долю от выручки своего отдела и от общей выручки.
SELECT
order_id,
department,
amount,
ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY department), 1) AS pct_of_dept,
ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS pct_of_total
FROM ordersДва окна в одном запросе: PARTITION BY department для доли внутри отдела, пустое OVER () для доли от общей суммы.
Типичные ошибки
1. Забыть ORDER BY в функции ранжирования
-- Неправильно: порядок случайный
ROW_NUMBER() OVER (PARTITION BY department) AS rn
-- Правильно: порядок задан явно
ROW_NUMBER() OVER (PARTITION BY department ORDER BY amount DESC) AS rnБез ORDER BY СУБД не знает, в каком порядке нумеровать строки. Результат будет непредсказуемым и может меняться от запуска к запуску.
2. Использовать RANK вместо ROW_NUMBER для Top-N
-- Опасно: при одинаковых значениях RANK вернёт больше N строк
WHERE rnk <= 3 -- может вернуть 4, 5... строк
-- Безопасно: ROW_NUMBER всегда вернёт ровно N строк
WHERE rn <= 3Если нужно ровно N строк — используйте ROW_NUMBER. Если допустимо показать всех, кто «разделил» место — RANK или DENSE_RANK.
3. Ошибка на единицу в ROWS BETWEEN
-- Неправильно: окно из 8 строк (7 предыдущих + текущая)
ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
-- Правильно: окно из 7 строк (6 предыдущих + текущая)
ROWS BETWEEN 6 PRECEDING AND CURRENT ROWЕсли нужно скользящее окно из N строк, пишите N-1 PRECEDING AND CURRENT ROW.
4. LAST_VALUE без явного фрейма
-- Неправильно: вернёт текущую строку, а не последнюю в окне
LAST_VALUE(revenue) OVER (ORDER BY day)
-- Правильно: явно указываем фрейм до конца окна
LAST_VALUE(revenue) OVER (
ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)По умолчанию с ORDER BY фрейм заканчивается на текущей строке, поэтому LAST_VALUE возвращает значение текущей строки — не то, что ожидалось. FIRST_VALUE от этой проблемы не страдает, потому что начало фрейма всегда совпадает с началом окна.
FAQ
В чём разница между ROW_NUMBER, RANK и DENSE_RANK?
ROW_NUMBER всегда присваивает уникальные последовательные номера. При одинаковых значениях — порядок произвольный. RANK присваивает одинаковые значения дубликатам, но пропускает следующие ранги (1, 2, 2, 4). DENSE_RANK тоже присваивает одинаковые значения дубликатам, но ранги идут без пропусков (1, 2, 2, 3). Для задач типа «top-N в каждой группе» обычно используют ROW_NUMBER, для рейтингов и лидербордов — RANK или DENSE_RANK.
Можно ли использовать несколько оконных функций в одном запросе?
Да, и это обычная практика. В одном SELECT можно комбинировать любое количество оконных функций с разными окнами:
SELECT
day,
revenue,
SUM(revenue) OVER (ORDER BY day) AS running_total,
LAG(revenue) OVER (ORDER BY day) AS prev_day,
AVG(revenue) OVER () AS overall_avg,
ROW_NUMBER() OVER (ORDER BY revenue DESC) AS rank_by_revenue
FROM daily_revenueЕсли несколько функций используют одинаковое окно, СУБД вычислит его один раз. Для читаемости можно вынести окно в WINDOW-клаузу (поддерживается в PostgreSQL и ряде других СУБД):
SELECT
day,
revenue,
SUM(revenue) OVER w AS running_total,
LAG(revenue) OVER w AS prev_day
FROM daily_revenue
WINDOW w AS (ORDER BY day)Оконные функции замедляют запрос?
Оконные функции требуют сортировки данных внутри каждого окна, что может быть ресурсоёмко на больших таблицах. Несколько советов по оптимизации: старайтесь использовать одно и то же окно (одинаковый PARTITION BY и ORDER BY) в нескольких функциях — СУБД отсортирует данные один раз. Индекс на столбцах PARTITION BY + ORDER BY может ускорить запрос. Если нужны оконные функции для отфильтрованной выборки — сначала отфильтруйте данные в CTE или подзапросе, а потом применяйте оконную функцию. На практике оконные функции обычно работают быстрее, чем эквивалентные решения через коррелированные подзапросы или self-join.
В чём разница между ROWS и RANGE?
ROWS считает физические строки. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — это ровно 3 строки: текущая и две перед ней. RANGE считает по значению столбца ORDER BY. RANGE BETWEEN 2 PRECEDING AND CURRENT ROW — все строки, где значение ORDER BY-столбца попадает в диапазон от (текущее - 2) до текущего. Если в данных есть дубликаты по ORDER BY-столбцу, RANGE включит их все, а ROWS — нет. Для большинства задач (скользящее среднее, нарастающий итог) подходит ROWS. RANGE полезен, когда данные содержат пропуски — например, если за некоторые даты нет записей, и вы хотите считать среднее за календарный диапазон, а не за количество строк.