Оконные функции SQL: полный гайд с реальными примерами

Без оконных функций в аналитике никуда. На собесе они почти всегда — если не дали задачу на 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. WINDOW FUNCTIONS  — посчитали окна на уже сжатом наборе
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-тестам
• Продуктовые кейсы — 225 кейсов с разбором
• AI-собеседование — разбор решений с честной обратной связью
• Конспекты по SQL и статистике — 210 модулей теории
• Продуктовые метрики — 265 метрик с формулами и SQL-запросами
• Как стать аналитиком данных в 2026 — полный гайд по входу в профессию

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