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

Оконные функции (Window Functions) — это водораздел между начинающим пользователем баз данных и настоящим аналитиком. На любом техническом собеседовании в финтех или бигтех задача на оконные функции присутствует со стопроцентной вероятностью.

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

В чем фундаментальная разница между GROUP BY и OVER()

Классический GROUP BY «схлопывает» исходные строки таблицы в агрегированные итоги. Если у вас было 10 000 чеков, то после группировки по категориям останется всего 10 строк. Доступ к индивидуальным деталям каждого заказа теряется.

Оконная функция не схлопывает строки. Она производит расчет по выделенному срезу данных (окну), но возвращает результат в каждую отдельную исходную строку.

1. Ранжирующие функции: ROW_NUMBER vs RANK vs DENSE_RANK

Это любимый вопрос на скринингах. Представьте отдел продаж, где менеджеры совершили сделки со следующими суммами: Анна (500k), Борис (500k), Виктор (300k).

SELECT 
    manager_name,
    deal_amount,
    ROW_NUMBER() OVER (ORDER BY deal_amount DESC) AS row_num,  -- 1, 2, 3 (строгая нумерация)
    RANK()       OVER (ORDER BY deal_amount DESC) AS rnk,      -- 1, 1, 3 (пропуск ранга после дубля)
    DENSE_RANK() OVER (ORDER BY deal_amount DESC) AS dense_rnk -- 1, 1, 2 (без пропуска ранга)
FROM sales_deals;

Когда что использовать в бизнесе:

• ROW_NUMBER() — дедупликация данных, отбор ровно одной последней строки на пользователя;
• DENSE_RANK() — расчет пьедестала почета (топ-3 цен, топ-5 грейдов без дырок в нумерации);
• RANK() — расчет олимпийских мест с честным пропуском позиций.

2. Функции смещения: LAG и LEAD (расчет динамики)

Как сравнить выручку текущего дня со вчерашней без медленного JOIN table t2 ON t1.date = t2.date + 1? Для этого созданы функции смещения:

• LAG(column, offset, default) — заглядывает назад на N строк;
• LEAD(column, offset, default) — заглядывает вперед на N строк.

SELECT 
    order_date,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY order_date) AS yesterday_revenue,
    revenue - LAG(revenue, 1, 0) OVER (ORDER BY order_date) AS diff_rubles
FROM daily_revenue;

3. Нарастающий итог (Running Total) и скользящее среднее (Moving Average)

Для сглаживания сезонных колебаний в финансовой аналитике часто используют 7-дневное скользящее среднее. Вот как это элегантно пишется через фреймы:

SELECT 
    report_date,
    daily_users,
    -- Нарастающий итог с начала времен
    SUM(daily_users) OVER (
        ORDER BY report_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_users,
    -- Скользящее среднее за 7 дней (текущий день + 6 предыдущих)
    AVG(daily_users) OVER (
        ORDER BY report_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM active_users_log;

3 главные ошибки начинающих на собеседовании

1. Попытка использовать оконную функцию в секции WHERE: Оконные функции вычисляются на шаге SELECT (после фильтрации WHERE и GROUP BY). Если нужно отфильтровать результат окна — всегда оборачивайте его в CTE (WITH) или подзапрос.
2. Забытый ORDER BY в LAG/LEAD или ROW_NUMBER: без явной сортировки СУБД вернет строки в недетерминированном физическом порядке чтения с диска, что приведет к скрытой ошибке.
3. Непонимание разницы между ROWS и RANGE: по умолчанию в большинстве СУБД действует RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, что при одинаковых значениях в ORDER BY агрегирует все одинаковые строки разом. Для строгого построчного расчета используйте ROWS.

На курсах индивидуального менторства по SQL мы прорабатываем более 50 вариаций задач на оконные функции, чтобы на реальном собеседовании вы решали их спокойно и уверенно.