Оконные функции SQL простыми словами

Что такое оконные функции простыми словами

На собеседовании на аналитика данных оконные функции всплывают почти всегда — и именно на них спотыкаются те, кто привык к GROUP BY. Задача звучит безобидно: «покажи каждый заказ и рядом долю от выручки категории» или «найди топ-3 товара в каждом отделе». На обычной агрегации это превращается в подзапросы и JOIN-ы, а на оконных функциях — в один читаемый запрос. Разберём всё по порядку и простыми словами.

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

GROUP BY агрегирует: было 1000 строк — стало 10 (по одной на группу). Оконная функция сохраняет все 1000 строк и добавляет к каждой вычисленное значение. Это принципиальная разница, и именно её любят проверять на собесе.

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

SELECT
    order_id,
    category,
    amount,
    SUM(amount) OVER (PARTITION BY category) AS category_total,
    ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY category), 1) AS pct
FROM orders

С GROUP BY пришлось бы сначала агрегировать выручку по категориям, а потом джойнить результат обратно к заказам. С оконной функцией — один запрос без JOIN. Обратите внимание на 100.0 в формуле доли: если написать 100 * amount / SUM(...) на целых числах, PostgreSQL выполнит целочисленное деление и вернёт нули. Умножение на 100.0 приводит выражение к числу с плавающей точкой.

Подробнее о разнице между подходами — в статье GROUP BY vs PARTITION BY.

Синтаксис OVER: PARTITION BY, ORDER BY, фрейм

Любая оконная функция использует конструкцию OVER(). Внутри неё — до трёх частей:

функция() OVER (
    PARTITION BY столбец      -- разбивка на группы (окна)
    ORDER BY столбец          -- порядок строк внутри окна
    ROWS BETWEEN ... AND ...  -- рамка: какие строки участвуют
)

PARTITION BY — аналог GROUP BY, но без свёртки. Делит данные на независимые окна, внутри каждого функция считается отдельно. Если PARTITION BY не указать, всё множество строк считается одним окном.

ORDER BY — задаёт порядок строк внутри окна. Обязателен для функций ранжирования (ROW_NUMBER, RANK) и смещения (LAG, LEAD), потому что без порядка понятие «предыдущая строка» или «первое место» не имеет смысла. Для агрегатов он опционален, но именно он превращает обычную сумму в нарастающий итог.

Фрейм (ROWS BETWEEN / RANGE BETWEEN) — определяет, какие именно строки внутри окна участвуют в вычислении относительно текущей. Это самая недооценённая часть синтаксиса, и ниже мы разберём её отдельно.

Ранжирование: ROW_NUMBER, RANK, DENSE_RANK

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

SELECT
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
    RANK()       OVER (ORDER BY score DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM candidates

ROW_NUMBER всегда выдаёт уникальный номер. При одинаковых значениях порядок между ними не детерминирован — Алиса и Борис оба с 95, но один получит 1, другой 2 произвольно (если не добавить второй критерий в ORDER BY).

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

DENSE_RANK тоже даёт одинаковый ранг при равенстве, но без пропусков (1, 1, 2). Удобно, когда нужны «уровни» значений без дыр в нумерации.

На практике правило простое: ROW_NUMBER берут для top-N per group, когда нужен ровно один результат на группу; RANK и DENSE_RANK — когда важно сохранить совпадающие позиции (рейтинги, лидерборды). Развёрнутое сравнение с примерами — RANK vs ROW_NUMBER vs DENSE_RANK.

Смещение: LAG и LEAD

LAG и LEAD достают значение из соседней строки, не прибегая к самосоединению таблицы.

LAG(столбец, n) — значение из строки на n позиций назад (по умолчанию n = 1). LEAD(столбец, n) — значение из строки на n позиций вперёд. Оба требуют ORDER BY, иначе «предыдущая» строка не определена.

Классическое применение — рост выручки месяц к месяцу:

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(revenue) AS rev
    FROM sales
    GROUP BY 1
)
SELECT
    month,
    rev,
    LAG(rev) OVER (ORDER BY month) AS prev_month,
    ROUND(100.0 * (rev - LAG(rev) OVER (ORDER BY month))
        / NULLIF(LAG(rev) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly

LAG и LEAD возвращают NULL, если предыдущей или следующей строки нет (для первого месяца предыдущего значения не существует). Поэтому деление обёрнуто в NULLIF(..., 0) — это защита от деления на ноль, если у прошлого месяца выручка оказалась нулевой. Значение по умолчанию можно задать третьим аргументом: LAG(rev, 1, 0) вернёт 0 вместо NULL.

Агрегаты как окно: SUM, AVG, COUNT OVER

Любая агрегатная функция работает как оконная, если добавить OVER(). Это даёт три самых востребованных в аналитике паттерна.

Нарастающий итог (running total)

SELECT
    DATE,
    new_users,
    SUM(new_users) OVER (ORDER BY DATE) AS cumulative_users
FROM daily_registrations

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

Скользящее среднее за 7 дней (moving average)

SELECT
    DATE,
    dau,
    AVG(dau) OVER (
        ORDER BY DATE
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS dau_7d_avg
FROM daily_metrics

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — текущая строка плюс 6 предыдущих, то есть окно в 7 дней. Так сглаживают дневные колебания DAU в продуктовой аналитике, чтобы график не прыгал от выходных к будням.

COUNT OVER — количество внутри окна

SELECT
    user_id,
    order_id,
    COUNT(*) OVER (PARTITION BY user_id) AS total_orders
FROM orders

К каждому заказу добавлено общее количество заказов пользователя — без подзапроса. Маленькая, но важная оговорка: COUNT(DISTINCT ...) в окне PostgreSQL не поддерживает. Если нужно уникальное число внутри окна, считают через коррелированный подзапрос или предварительную агрегацию в CTE.

NTILE: деление на равные группы

NTILE(n) делит строки окна на n примерно равных по размеру групп и проставляет каждой строке номер её группы. Это рабочий инструмент для квартилей, децилей и любой сегментации «по равным частям».

SELECT
    user_id,
    ltv,
    NTILE(4) OVER (ORDER BY ltv DESC) AS ltv_quartile
FROM users

Здесь пользователи сортируются по LTV и делятся на 4 квартиля: квартиль 1 — самые ценные 25%, квартиль 4 — наименее ценные. Если число строк не делится на n нацело, NTILE распределяет «лишние» строки по первым группам — поэтому первые группы могут быть на одну строку больше. Частая задача с собеседования формулируется именно так: «раздели клиентов на 10 групп по выручке» — это NTILE(10).

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

Эти функции достают значение с краёв окна.

FIRST_VALUE(столбец) — значение из первой строки окна. LAST_VALUE(столбец) — из последней. NTH_VALUE(столбец, n) — из n-й строки.

SELECT
    user_id,
    order_date,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY user_id ORDER BY order_date
    ) AS first_order_amount
FROM orders

С FIRST_VALUE всё интуитивно, а вот LAST_VALUE — классическая ловушка. По умолчанию, когда указан ORDER BY, рамка окна заканчивается на текущей строке (CURRENT ROW), а не на последней строке окна. Поэтому LAST_VALUE вернёт значение текущей строки, а не реальный последний заказ. Чтобы получить настоящее последнее значение, рамку нужно задать явно:

LAST_VALUE(amount) OVER (
    PARTITION BY user_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Фрейм окна: ROWS BETWEEN и RANGE BETWEEN

Рамка (frame) определяет, какие строки участвуют в вычислении относительно текущей. Границы задаются так:

ROWS считает физические строки: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — это ровно 3 строки. RANGE работает по значениям ORDER BY — при дубликатах включает все строки с одинаковым значением сразу.

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

Частые задачи на собеседовании

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

Топ-N на группу

Найти 3 самых дорогих заказа в каждой категории:

WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn <= 3

Паттерн «ROW_NUMBER + WHERE rn <= N» — один из самых частых на собесе. Ключевой момент: фильтр по результату окна вынесен в CTE, потому что в самом WHERE оконную функцию использовать нельзя (об этом ниже).

Разница с предыдущей строкой через LAG

Определить, вернулся ли пользователь на следующий день после прошлой сессии:

WITH daily_sessions AS (
    SELECT DISTINCT user_id, session_date::DATE AS day
    FROM sessions
),
with_prev AS (
    SELECT
        user_id,
        day,
        LAG(day) OVER (PARTITION BY user_id ORDER BY day) AS prev_day
    FROM daily_sessions
)
SELECT
    prev_day AS cohort_day,
    COUNT(DISTINCT user_id) FILTER (WHERE day - prev_day = 1) AS retained,
    COUNT(DISTINCT user_id) AS total
FROM with_prev
WHERE prev_day IS NOT NULL
GROUP BY prev_day

Подробнее о расчётах Retention — как считать Retention.

Нарастающий итог с разбивкой

Скользящее среднее конверсии по каждому каналу привлечения отдельно:

SELECT
    channel,
    DATE,
    conversion_rate,
    AVG(conversion_rate) OVER (
        PARTITION BY channel
        ORDER BY DATE
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS cr_7d_avg
FROM channel_daily_stats

PARTITION BY channel гарантирует, что окно каждого канала не «протекает» в соседний — среднее считается строго внутри своего канала.

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

Забытый ORDER BY в функциях ранжирования. ROW_NUMBER() OVER (PARTITION BY user_id) без ORDER BY — порядок строк не определён, и нумерация при каждом запуске может оказаться разной. Для ранжирования и смещения порядок обязателен.

Рамка по умолчанию при дубликатах. SUM(x) OVER (ORDER BY date) без явной рамки использует RANGE и при одинаковых датах суммирует все дубликаты сразу, ломая нарастающий итог. Пишите ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, когда нужна строгая последовательность строк.

LAST_VALUE без полной рамки. По умолчанию рамка заканчивается на текущей строке, поэтому LAST_VALUE вернёт текущую строку, а не последнюю в окне. Нужна явная рамка UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

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

GROUP BY там, где нужно окно. Если задача требует и детальные строки, и агрегат рядом — это оконная функция, а не GROUP BY с обратным JOIN.

Несколько окон с одинаковым OVER. Каждое уникальное OVER заставляет SQL пересортировать данные. Если окно повторяется, опишите его один раз через WINDOW:

SELECT
    ROW_NUMBER() OVER w AS rn,
    SUM(amount) OVER w AS running_total
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY created_at)

Связанные темы

• Оконные функции SQL: шпаргалка — краткая шпаргалка с 15 задачами
• RANK vs ROW_NUMBER vs DENSE_RANK — три функции ранжирования в деталях
• GROUP BY vs PARTITION BY — агрегация против оконных функций
• Как использовать window-функции в SQL — практический разбор синтаксиса
• Как найти N-ю максимальную зарплату — классическая задача на ранжирование

FAQ

Что такое оконная функция простыми словами?

Это функция, которая считает значение для строки по группе связанных с ней строк (окну), но не схлопывает строки в одну, как GROUP BY. На выходе остаются все исходные строки, и к каждой добавлен агрегат, ранг или соседнее значение.

Чем отличаются ROW_NUMBER, RANK и DENSE_RANK?

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

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

SUM(value) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Если нужен нарастающий итог внутри каждого месяца, добавьте PARTITION BY DATE_TRUNC('month', date). Явная рамка ROWS защищает от сюрпризов при одинаковых датах.

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

Нет. Оконные функции выполняются после WHERE и GROUP BY. Чтобы отфильтровать по результату окна, вынесите его в CTE или подзапрос:

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn = 1

Что будет, если не указать PARTITION BY?

Всё множество строк станет одним окном. SUM(amount) OVER () — сумма по всей таблице в каждой строке, ROW_NUMBER() OVER (ORDER BY date) — сквозная нумерация без разбивки на группы.

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

ROWS считает физические строки: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — ровно 3 строки. RANGE работает по значениям в ORDER BY и при дубликатах включает все строки с одинаковым значением. На практике используйте ROWS — поведение предсказуемее.

Какие СУБД поддерживают оконные функции?

Все основные: PostgreSQL, MySQL 8+, ClickHouse, BigQuery, Snowflake, Redshift, SQL Server, Oracle. Синтаксис практически одинаковый, различия — в поддержке RANGE и отдельных функций вроде NTH_VALUE и PERCENT_RANK. Для подготовки к собеседованию хватит синтаксиса PostgreSQL.