Оконные функции SQL: примеры, OVER, PARTITION BY и ошибки

💡 Никогда не работал с SQL? Сначала пройди серию SQL с нуля для аналитика — 10 связанных частей от «что такое БД» до оконных функций.

Без оконных функций в аналитике никуда. На собесе они почти всегда — если не дали задачу на ROW_NUMBER, то на LAG или running total. В работе — та же история: когорты, retention, дедупликация, топ-N, скользящие метрики — всё это считается через оконные.

Дальше разберём по порядку: восемь функций, PARTITION BY, ORDER BY, frame clauses, бенчмарки против GROUP BY, типичные грабли и пять задач с собесов. Параллельно можно тренироваться в SQL-тренажёре — там есть отдельная категория на оконные функции.

Что такое оконная функция

Обычный SUM или AVG схлопывает строки: GROUP BY city превращает 10 000 заказов в 50 городов, и детали теряются.

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

GROUP BY против оконной — на пальцах

Простая таблица заказов:

1. Через GROUP BY:

SELECT
 user_id,
 SUM(amount) AS total
FROM orders
GROUP BY user_id;

Получаем две строки, заказы схлопнулись:

2. Через оконную функцию:

SELECT
 user_id,
 order_date,
 amount,
 SUM(amount) OVER (PARTITION BY user_id) AS user_total
FROM orders;

Все пять строк на месте, просто у каждой добавился user_total — сумма по её пользователю. С цветной подсветкой партиций видно лучше:

Одним запросом получили и каждый заказ, и сумму по его пользователю. На этом паттерне строятся половина аналитических запросов: доля заказа в месячной выручке, заказы выше среднего по клиенту, первый заказ каждого. Всё в один SELECT, без подзапросов и JOIN.

Синтаксис OVER

Общая форма такая:

функция() OVER (
 PARTITION BY <поля> -- на какие группы резать (опционально)
 ORDER BY <поля> -- в каком порядке внутри группы (опционально)
 <frame_clause> -- какой кусок окна учитывать (опционально)
)

Три части внутри OVER отвечают за разное:

Восемь функций, которых хватает на 99% задач

Этот список закроет и собес, и 99% реальных задач. В SQL-тренажёре можно сразу тренироваться после каждого раздела — там похожие задачи с проверкой.

ROW_NUMBER() — сквозная нумерация

Даёт каждой строке уникальный номер внутри окна. Если в ORDER BY есть одинаковые значения — порядок между ними непредсказуем. Чтобы результат был стабильным, добавь второй ключ сортировки, обычно id.

SELECT
 user_id,
 order_date,
 amount,
 ROW_NUMBER() OVER (
 PARTITION BY user_id
 ORDER BY order_date
 ) AS n
FROM orders;

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

WITH numbered AS (
 SELECT
 *,
 ROW_NUMBER() OVER (
 PARTITION BY user_id
 ORDER BY order_date
 ) AS n
 FROM orders
)
SELECT *
FROM numbered
WHERE n = 1;

RANK() — ранжирование с пропусками

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

DENSE_RANK() — ранжирование без пропусков

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

Сравнение всех трёх функций на одном примере — продажи по сумме. Одинаковые ранги подсвечены зелёным, различия (где RANK пропускает номер, а DENSE_RANK нет) — оранжевым.

Обратите внимание на третью строку: при одинаковых amount=1200 в первых двух строках RANK присваивает обеим «1», а следующей строке — сразу «3» (второе место «съедается»). DENSE_RANK даёт тем же двум «1», но следующей — «2», без пропуска.

LAG() — значение предыдущей строки

SELECT
 user_id,
 order_date,
 amount,
 LAG(amount) OVER (
 PARTITION BY user_id
 ORDER BY order_date
 ) AS prev_amount,
 amount - LAG(amount) OVER (
 PARTITION BY user_id
 ORDER BY order_date
 ) AS diff
FROM orders;

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

LEAD() — значение следующей строки

Работает зеркально LAG — смотрит не назад, а вперёд. Пригодится когда нужно «сколько дней до следующего события» или сравнить текущую строку с будущей.

-- PostgreSQL / стандартный SQL: разница между датами в днях
SELECT
 user_id,
 order_date,
 LEAD(order_date) OVER (
 PARTITION BY user_id
 ORDER BY order_date
 ) AS next_date,
 LEAD(order_date) OVER (
 PARTITION BY user_id
 ORDER BY order_date
 ) - order_date AS days_to_next
FROM orders;

Для SQLite та же задача решается через julianday():

-- SQLite: julianday возвращает число дней от базовой даты
SELECT
 user_id,
 order_date,
 julianday(LEAD(order_date) OVER (
 PARTITION BY user_id
 ORDER BY order_date
 )) - julianday(order_date) AS days_to_next
FROM orders;

На этом строится половина retention-аналитики: дни между покупками, признаки оттока, средний цикл. Сами формулы — в разделе Метрики.

SUM / AVG / COUNT OVER — агрегат по окну

Любой агрегат (SUM, AVG, COUNT, MIN, MAX) становится оконным, если добавить OVER (...). Агрегат считается по окну, строки остаются на месте.

С ORDER BY внутри OVER получается накопительная сумма — сумма с начала окна до текущей строки.

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

Визуально видно как накопительная сумма растёт от заказа к заказу — каждый новый заказ прибавляется к предыдущему итогу:

Так считаются LTV по когортам, накопительная выручка с начала месяца и другие метрики, где важна динамика во времени.

FIRST_VALUE() / LAST_VALUE()

Берут значение первой или последней строки окна. Используются когда нужна «якорная» точка: например, сравнить каждый заказ с первым заказом пользователя — видно, растёт ли чек со временем.

SELECT
 user_id,
 order_date,
 amount,
 FIRST_VALUE(amount) OVER (
 PARTITION BY user_id
 ORDER BY order_date
 ) AS first_order,
 amount - FIRST_VALUE(amount) OVER (
 PARTITION BY user_id
 ORDER BY order_date
 ) AS diff_vs_first
FROM orders;

NTILE(N) — разбиение на N корзин

SELECT
 user_id,
 total_spent,
 NTILE(4) OVER (ORDER BY total_spent DESC) AS spending_quartile
FROM user_totals;

Квартиль 1 — топ 25% по тратам, квартиль 4 — нижние 25%. На этом строится RFM и любая сегментация клиентов по поведению.

Формулы LTV, ARPU и ретеншна с готовыми SQL-запросами — в каталоге Метрики. Теория про сегментацию в целом — в разделе Теория.

Frame clauses — границы окна

Когда в OVER есть ORDER BY, окно по умолчанию — «от начала партиции до текущей строки». Но часто нужно другое: «последние 7 дней», «весь диапазон партиции», «только будущие строки». Тут в игру входит frame clause.

ROWS или RANGE

-- ROWS: ровно N физических строк независимо от их значений
AVG(amount) OVER (
 ORDER BY order_date
 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

-- RANGE: все строки в логическом диапазоне
-- (синтаксис PostgreSQL — поддержка INTERVAL зависит от СУБД)
AVG(amount) OVER (
 ORDER BY order_date
 RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
)

Оранжевым залиты строки, попадающие в окно когда курсор стоит на пятой строке. Когда курсор сдвигается на шестую — окно сдвигается вместе с ним. Это и есть sliding window.

Пример: скользящее среднее выручки за 7 дней

Стандартная задача: сгладить дневную выручку скользящим средним, чтобы убрать недельную сезонность (выходные против будней).

Два шага через CTE. Сначала считаем дневную выручку обычным GROUP BY, потом прогоняем её через оконное AVG с окном в 7 строк:

WITH daily AS (
 SELECT
 order_date,
 SUM(amount) AS daily_revenue
 FROM orders
 GROUP BY order_date
)
SELECT
 order_date,
 daily_revenue,
 AVG(daily_revenue) OVER (
 ORDER BY order_date
 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
 ) AS rolling_7d_avg
FROM daily
ORDER BY order_date;

Если положить это на график — дневная выручка дёргается вверх-вниз по неделе, а скользящая средняя идёт гладкой линией. Тренд сразу читается.

Плюсы и минусы

Когда оконная функция — лучший инструмент

• Нужны детали и агрегат одновременно. Топ-N заказов на пользователя с сохранением всех полей — без GROUP BY, в один запрос.
• Накопительные суммы. SUM или AVG с ORDER BY — это накопительный итог в одну строку запроса, без самосоединений.
• Доступ к соседним строкам. LAG и LEAD — разница с предыдущей или следующей строкой. Без оконных функций это делается только через самосоединение.
• Перцентили и квартили. NTILE, PERCENT_RANK, CUME_DIST считаются одной функцией — раньше для этого использовали CROSS JOIN.
• Читаемость. Один OVER вместо трёх подзапросов и двух JOIN.

Когда оконную функцию лучше не использовать

• Агрегат без деталей. Если строки-источники не нужны, обычный GROUP BY быстрее и проще.
• Простая фильтрация. WHERE amount > (SELECT AVG(amount) FROM orders) через подзапрос часто быстрее и понятнее, чем оконная функция.
• MySQL < 8.0. В старых MySQL оконных функций нет. Вместо них — самосоединения или пользовательские переменные.
• Очень большие партиции. Окно на десятки миллионов строк может работать медленнее GROUP BY из-за сортировки внутри окна.

Производительность: сравнение

Замеры на 1 млн заказов (PostgreSQL 15, локальный SSD, прогретый кеш):

Реальные задачи с собеседований

Пять задач с технических собесов аналитика — от простой до продвинутой. Попробуй решить сам, прежде чем смотреть ответ (кнопка «Показать решение» развернёт SQL). Похожие задачи с автопроверкой — в SQL-тренажёре, разборы больших кейсов — в Продуктовых кейсах.

Задача 1 (уровень Junior) — топ-3 товара в каждой категории

Условие: в таблице products с полями category, product, revenue найти три самых прибыльных товара в каждой категории.

SELECT category, product, revenue
FROM (
 SELECT
 category,
 product,
 revenue,
 ROW_NUMBER() OVER (
 PARTITION BY category
 ORDER BY revenue DESC
 ) AS rn
 FROM products
) t
WHERE rn <= 3;

Задача 2 (уровень Middle) — дни между покупками

Условие: для каждого заказа вывести количество дней, прошедших с предыдущего заказа того же пользователя.

-- PostgreSQL: разница дат возвращает число дней
SELECT
 user_id,
 order_date,
 order_date - LAG(order_date) OVER (
 PARTITION BY user_id
 ORDER BY order_date
 ) AS days_since_prev
FROM orders;

-- SQLite: julianday возвращает число дней от точки отсчёта
SELECT
 user_id,
 order_date,
 julianday(order_date) - julianday(
 LAG(order_date) OVER (
 PARTITION BY user_id
 ORDER BY order_date
 )
 ) AS days_since_prev
FROM orders;

Задача 3 (уровень Middle) — накопительная доля клиента в выручке

Условие: отсортировать клиентов по убыванию трат и посчитать, какую кумулятивную долю в общей выручке они занимают. Это основа ABC-анализа.

WITH user_totals AS (
 SELECT
 user_id,
 SUM(amount) AS total_spent
 FROM orders
 GROUP BY user_id
)
SELECT
 user_id,
 total_spent,
 SUM(total_spent) OVER (ORDER BY total_spent DESC) AS cumulative_spent,
 ROUND(
 100.0 * SUM(total_spent) OVER (ORDER BY total_spent DESC) /
 SUM(total_spent) OVER (),
 2
 ) AS cumulative_pct
FROM user_totals
ORDER BY total_spent DESC;

Задача 4 (уровень Middle+) — выручка с начала месяца нарастающим итогом

Условие: по таблице daily_sales (order_date, daily_revenue) вывести накопительную выручку с начала каждого календарного месяца (metric month-to-date).

SELECT
 order_date,
 daily_revenue,
 SUM(daily_revenue) OVER (
 PARTITION BY strftime('%Y-%m', order_date)
 ORDER BY order_date
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 ) AS mtd_revenue
FROM daily_sales
ORDER BY order_date;

Задача 5 (уровень Senior) — клиенты с тремя уменьшениями чека подряд

Условие: найти пользователей, у которых сумма заказа падает три раза подряд. Простой маркер снижения платёжеспособности.

Это классический паттерн «gaps and islands» — один из самых хитрых в SQL. Идея: каждый заказ помечаем флагом «меньше предыдущего?», строим id группы подряд идущих уменьшений, считаем длину каждой такой серии.

WITH diffs AS (
 -- Шаг 1: для каждого заказа считаем разницу с предыдущим
 SELECT
 user_id,
 order_date,
 amount,
 amount - LAG(amount) OVER (
 PARTITION BY user_id
 ORDER BY order_date
 ) AS diff
 FROM orders
),
flags AS (
 -- Шаг 2: флаг «уменьшение» = 1, иначе 0.
 -- Первая строка пользователя (LAG=NULL → diff=NULL) в счёт не идёт.
 SELECT
 user_id,
 order_date,
 amount,
 CASE WHEN diff < 0 THEN 1 ELSE 0 END AS is_decrease
 FROM diffs
 WHERE diff IS NOT NULL
),
groups AS (
 -- Шаг 3: идентификатор группы подряд идущих уменьшений.
 -- Накопительная сумма НЕ-уменьшений меняется только когда цепочка прерывается,
 -- значит всем строкам одной цепочки присвоится один и тот же grp.
 SELECT
 user_id,
 order_date,
 is_decrease,
 SUM(CASE WHEN is_decrease = 0 THEN 1 ELSE 0 END) OVER (
 PARTITION BY user_id
 ORDER BY order_date
 ) AS grp
 FROM flags
),
runs AS (
 -- Шаг 4: длина каждой цепочки уменьшений
 SELECT
 user_id,
 grp,
 COUNT(*) AS decrease_streak
 FROM groups
 WHERE is_decrease = 1
 GROUP BY user_id, grp
)
SELECT DISTINCT user_id
FROM runs
WHERE decrease_streak >= 3;

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

Четыре самые частые ошибки. Если их помнить, оконная часть собеса пройдёт без сюрпризов.

Порядок выполнения SQL с оконной функцией

Эту шпаргалку стоит держать в голове — сразу понятно, где оконную функцию можно применять, а где нет.

1. FROM / JOIN — собрали строки
2. WHERE — отфильтровали
3. GROUP BY / HAVING — сжали в группы (если есть)
4. ОКОННЫЕ ФУНКЦИИ — посчитали окна на уже сжатом наборе
5. SELECT — выбрали поля
6. ORDER BY / LIMIT — финальный порядок

Вот почему WHERE ROW_NUMBER() = 1 не работает: WHERE — шаг 2, оконная функция — шаг 4. Когда WHERE отрабатывает, ROW_NUMBER ещё не посчитан. А ORDER BY ROW_NUMBER() OVER (...) работает нормально — сортировка идёт на шаге 6, позже оконных функций.

Что где поддерживается

Не все базы работают с оконными функциями одинаково. Если приходится переключаться между СУБД — держи таблицу под рукой.

Отдельно про QUALIFY в BigQuery и Snowflake. Работает как WHERE, только после оконных функций. Без него пришлось бы писать CTE, с ним — один SELECT:

-- BigQuery / Snowflake: один запрос без обёртки
SELECT
 user_id,
 order_date,
 amount,
 ROW_NUMBER() OVER (
 PARTITION BY user_id
 ORDER BY order_date
 ) AS rn
FROM orders
QUALIFY rn = 1;

В PostgreSQL, MySQL и SQLite QUALIFY нет, там нужна обёртка через CTE или подзапрос.

Шпаргалка

Как закрепить

Только теория не даёт навыка — нужно решать. План на неделю, после которого оконные функции становятся рабочим инструментом.

Связанные материалы

• SQL-тренажёр — 300 задач с проверкой кода в браузере, первые 5 бесплатно
• Вопросы с собеседований — 2000+ карточек по SQL, Python, статистике, A/B-тестам
• Продуктовые кейсы — 440+ кейсов с разбором
• AI-собеседование — разбор решений с честной обратной связью
• Конспекты по SQL и статистике — 210 модулей теории
• Продуктовые метрики — 340+ метрик с формулами и SQL-запросами
• Как стать аналитиком данных в 2026 — полный гайд по входу в профессию

Закрой гайд, открой SQL-тренажёр, реши первую задачу на ROW_NUMBER. Через 20 минут оконные перестанут казаться сложными.

Частые вопросы

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

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

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

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

Что делают PARTITION BY и ORDER BY внутри OVER?

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

Как посчитать нарастающий итог оконной функцией?

Через SUM(amount) OVER (ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Без явной рамки при наличии ORDER BY по умолчанию берётся RANGE до текущей строки — на дубликатах дат это даёт другой результат, поэтому рамку лучше указывать явно.

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

Нет — окна вычисляются после WHERE и GROUP BY, поэтому напрямую в WHERE их нет. Оберните запрос в CTE или подзапрос и фильтруйте снаружи: WITH t AS (SELECT *, ROW_NUMBER() OVER (...) rn FROM ...) SELECT * FROM t WHERE rn = 1. Потренироваться — в SQL-тренажёре.