8 апреля 2026 г.

Оконные функции SQL — полный гайд для аналитика

Q: Какие оконные функции учить в первую очередь?

ROW_NUMBER, LAG/LEAD и SUM OVER. Эти три покрывают 80% задач на собеседованиях: top-N per group, сравнение с предыдущим периодом и нарастающий итог. После них — RANK/DENSE_RANK и AVG OVER для скользящих средних.

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

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

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

Зависит от объёма данных и наличия индексов. Каждая оконная функция с уникальным OVER выполняет отдельную сортировку. Если окно одинаковое — используйте WINDOW для переиспользования. На типичных аналитических запросах с десятками тысяч строк оконные функции работают быстро.

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

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

Что такое оконные функции и зачем они нужны

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

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 пришлось бы сначала агрегировать, потом джойнить обратно. С оконной функцией — один запрос.

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

Синтаксис OVER()

Любая оконная функция использует конструкцию OVER():

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

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

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

Frame clause (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

name	score	rn	rnk	drnk
Алиса	95	1	1	1
Борис	95	2	1	1
Вера	90	3	3	2
Глеб	85	4	4	3

ROW_NUMBER — всегда уникальный номер. При одинаковых значениях порядок не детерминирован.
RANK — одинаковые значения получают одинаковый ранг, следующий номер пропускается (1, 1, 3).
DENSE_RANK — одинаковые значения получают одинаковый ранг, пропуска нет (1, 1, 2).

Развёрнутое сравнение с примерами — RANK vs ROW_NUMBER vs DENSE_RANK.

Когда что использовать: ROW_NUMBER — для top-N per group (нужен ровно один результат на группу). RANK/DENSE_RANK — когда важно сохранить одинаковые позиции (рейтинги, лидерборды).

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

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

Классическое применение — month-over-month growth:

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, если предыдущей/следующей строки нет. Можно задать значение по умолчанию третьим аргументом: LAG(rev, 1, 0).

Агрегатные функции с окном: 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 дней. Именно так считают скользящее среднее в продуктовой аналитике, чтобы сгладить дневные колебания.

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

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

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

FIRST_VALUE, LAST_VALUE, NTH_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

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

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

Frame clause: ROWS BETWEEN и RANGE BETWEEN

Рамка определяет, какие строки участвуют в вычислении относительно текущей.

Граница	Значение
`UNBOUNDED PRECEDING`	Начало окна
`n PRECEDING`	n строк назад
`CURRENT ROW`	Текущая строка
`n FOLLOWING`	n строк вперёд
`UNBOUNDED FOLLOWING`	Конец окна

ROWS считает физические строки. RANGE работает по значениям — при дубликатах в ORDER BY включает все строки с одинаковым значением.

Важный нюанс: если указать ORDER BY, но не указать рамку, по умолчанию используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это может привести к неожиданным результатам при дубликатах. На практике всегда лучше указывать рамку явно.

Практические примеры

Top-N per group

Найти 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 = 1 (или rn <= N) — один из самых частых на собеседованиях.

Retention через 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.

Скользящее среднее за 7 дней с PARTITION BY

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

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

Типичные ошибки

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

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

3. LAST_VALUE без полной рамки. По умолчанию рамка заканчивается на текущей строке, и LAST_VALUE вернёт текущую строку, а не последнюю в окне.

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

5. Несколько оконных функций с разным ORDER BY. Работает, но каждый OVER пересортировывает данные. Если окно одинаковое, используйте 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)

Вопросы с собеседований

Чем отличаются 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).

Как найти предыдущее/следующее значение?

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

Зачем нужна оконная рамка?

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

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

Нет. Оконные функции выполняются после WHERE. Чтобы отфильтровать по результату оконной функции, нужен подзапрос или 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) — сквозная нумерация без разбивки на группы.

Потренируйтесь решать задачи — откройте тренажёр с 1500+ вопросами для подготовки к собеседованиям аналитиков.

Ещё по теме

Оконные функции SQL: шпаргалка — краткая шпаргалка с 15 задачами
RANK vs ROW_NUMBER vs DENSE_RANK
GROUP BY vs PARTITION BY
SQL-тренажёр для аналитика
1500+ примеров вопросов

FAQ

Какие оконные функции учить в первую очередь?

ROW_NUMBER, LAG/LEAD и SUM OVER. Эти три покрывают 80% задач на собеседованиях: top-N per group, сравнение с предыдущим периодом и нарастающий итог. После них — RANK/DENSE_RANK и AVG OVER для скользящих средних.

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

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

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

Зависит от объёма данных и наличия индексов. Каждая оконная функция с уникальным OVER выполняет отдельную сортировку. Если окно одинаковое — используйте WINDOW для переиспользования. На типичных аналитических запросах с десятками тысяч строк оконные функции работают быстро.

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

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

Тренироваться в Telegram