Оконные функции SQL: шпаргалка для собеседования

Зачем аналитику оконные функции

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

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

Общий синтаксис

функция() OVER (
    PARTITION BY столбец_группировки
    ORDER BY столбец_сортировки
    ROWS BETWEEN начало AND конец
)

• PARTITION BY — делит данные на группы (окна). Аналог GROUP BY, но без свёртки строк.
• ORDER BY — задаёт порядок строк внутри окна.
• ROWS BETWEEN — определяет рамку: какие строки участвуют в вычислении.

Не все части обязательны — зависит от конкретной функции.

Функции ранжирования

ROW_NUMBER()

Присваивает уникальный порядковый номер каждой строке внутри окна.

SELECT
    user_id,
    order_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS order_num
FROM orders

Результат: у каждого пользователя заказы пронумерованы 1, 2, 3… по дате. Номера всегда уникальны, даже при одинаковых значениях order_date.

На собеседовании спрашивают: «Найдите первый заказ каждого пользователя». Решение — обернуть в подзапрос и отфильтровать WHERE order_num = 1.

RANK()

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

SELECT
    student_id,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM exam_results

Если у двух студентов балл 95, оба получат ранг 1. Следующий студент получит ранг 3 (не 2).

DENSE_RANK()

Как RANK(), но без пропусков в нумерации.

SELECT
    student_id,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM exam_results

Два студента с баллом 95 — ранг 1. Следующий — ранг 2.

Классический вопрос на собеседовании: «Чем отличаются ROW_NUMBER, RANK и DENSE_RANK?» Ответ: ROW_NUMBER всегда уникален, RANK пропускает номера при дубликатах, DENSE_RANK не пропускает.

NTILE(n)

Делит строки на n равных групп.

SELECT
    user_id,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM user_stats

Пользователи разбиты на 4 квартиля по сумме покупок. Первый квартиль — топ-25% по расходам.

На собеседовании: используется в задачах про сегментацию — «Разделите пользователей на 10 равных групп по активности».

Функции смещения

LAG(столбец, смещение)

Возвращает значение из предыдущей строки (или на n строк назад).

SELECT
    DATE,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY DATE) AS prev_day_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY DATE) AS daily_change
FROM daily_stats

Получаем выручку за предыдущий день и разницу — рост или падение.

LEAD(столбец, смещение)

Обратная LAG — берёт значение из следующей строки.

SELECT
    user_id,
    session_date,
    LEAD(session_date, 1) OVER (PARTITION BY user_id ORDER BY session_date) AS next_session
FROM sessions

Для каждой сессии пользователя видим дату следующей сессии. Если следующей нет — NULL.

На собеседовании: «Посчитайте среднее время между сессиями пользователя». Решение — через LAG или LEAD вычислить разницу дат, затем AVG.

Агрегатные функции с окном

SUM() OVER

Нарастающий итог — одна из самых частых задач.

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

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

Нарастающий итог по группам:

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

AVG() OVER

Скользящее среднее — незаменимо для сглаживания метрик.

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

Среднее DAU за последние 7 дней (текущий + 6 предыдущих). Именно так считают скользящее среднее в продуктовой аналитике.

Оконные рамки: ROWS BETWEEN

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

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

Примеры:

-- Сумма текущей + 2 предыдущих строки
SUM(x) OVER (ORDER BY DATE ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- Среднее в окне ±1 строка от текущей
AVG(x) OVER (ORDER BY DATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

-- Нарастающий итог с начала
SUM(x) OVER (ORDER BY DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

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

Комбинированные задачи

На собеседованиях часто просят совместить несколько оконных функций. Пример:

Задача: Для каждого пользователя найдите заказ с максимальной суммой и выведите долю этого заказа от общей суммы покупок.

WITH ranked AS (
    SELECT
        user_id,
        order_id,
        amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,
        SUM(amount) OVER (PARTITION BY user_id) AS total_amount
    FROM orders
)
SELECT
    user_id,
    order_id,
    amount,
    ROUND(amount::numeric / total_amount * 100, 1) AS pct_of_total
FROM ranked
WHERE rn = 1

Здесь ROW_NUMBER находит максимальный заказ, а SUM OVER считает общую сумму по пользователю — обе функции в одном запросе.

Как тренироваться

Оконные функции нужно не просто понять — их нужно написать руками десятки раз. Теория забывается, навык остаётся.

Тренажёр Карьерник содержит вопросы по оконным функциям с разборами: от базового отличия RANK и ROW_NUMBER до сложных комбинаций с ROWS BETWEEN. Можно тренироваться по 15 минут в день в Telegram — этого достаточно, чтобы за неделю довести тему до автоматизма.

Больше примеров вопросов по SQL — в разделе подготовки. А если готовитесь комплексно, посмотрите примеры вопросов по всем темам.

FAQ

Какие оконные функции чаще всего спрашивают на собеседовании?

ROW_NUMBER, RANK и LAG/LEAD — абсолютные лидеры. За ними идут SUM OVER для нарастающих итогов и AVG OVER для скользящих средних. NTILE встречается реже, но появляется в задачах на сегментацию. Обязательно знайте разницу между ROW_NUMBER, RANK и DENSE_RANK — этот вопрос задают почти всегда.

Чем ROWS отличается от RANGE в оконной рамке?

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

Можно ли использовать несколько оконных функций в одном запросе?

Да, и это делают постоянно. Каждая оконная функция может иметь свой PARTITION BY и ORDER BY. Например, в одном SELECT можно одновременно пронумеровать строки через ROW_NUMBER и посчитать нарастающую сумму через SUM OVER — они будут работать независимо.