Оконные функции SQL: как работают и зачем их спрашивают на собесе

Оконные функции это та часть SQL, на которой аналитики делятся на два лагеря. Одни пишут ROW_NUMBER() OVER (...) не задумываясь, другие спотыкаются и городят подзапросы там, где хватило бы одной строки. На собесе это видно сразу, поэтому оконки спрашивают почти всегда. Разберём, как они устроены и какие задачи на них дают.

Диалект PostgreSQL, но синтаксис оконных функций почти одинаков везде.

Что такое оконная функция

Обычная агрегатная функция схлопывает строки: SUM, COUNT, AVG с GROUP BY превращают группу в одну строку. Оконная функция считает то же самое, но строки не схлопывает: она смотрит на «окно» соседних строк и приписывает результат каждой строке, оставляя их все на месте.

Ключевое отличие, которое любят спрашивать: чем OVER (PARTITION BY ...) принципиально отличается от GROUP BY? GROUP BY country вернёт по одной строке на страну. AVG(salary) OVER (PARTITION BY country) вернёт все строки сотрудников, и рядом с каждым будет средняя зарплата по его стране. Данные не теряются, просто появляется аналитика в контексте.

SELECT
  name,
  country,
  salary,
  AVG(salary) OVER (PARTITION BY country) AS avg_by_country
FROM employees;

Нумерация: ROW_NUMBER, RANK, DENSE_RANK

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

• ROW_NUMBER() даёт подряд 1, 2, 3, 4, всегда уникально, даже если значения равны.
• RANK() на равных даёт одинаковый ранг, а потом делает разрыв: 1, 2, 2, 4.
• DENSE_RANK() тоже даёт равным одинаковый ранг, но без разрыва: 1, 2, 2, 3.

Самое частое применение ROW_NUMBER это выбрать последний заказ каждого пользователя целиком, со всеми колонками. Через GROUP BY это неудобно (агрегат не тащит остальные поля), а через оконку в три строки:

SELECT *
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t
WHERE rn = 1;

Нумеруем заказы внутри каждого юзера по дате убывания, самому свежему достаётся rn = 1, его и берём. Тем же приёмом решается «топ-5 самых дорогих товаров»: отсортировать по цене и взять rn <= 5.

Сдвиг по строкам: LAG и LEAD

Функции, чтобы заглянуть в соседнюю строку. LAG смотрит назад, LEAD вперёд. Незаменимо для сравнения с прошлым периодом.

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS delta
FROM monthly;

LAG(revenue) OVER (ORDER BY month) возвращает выручку предыдущего месяца в той же строке. Дальше одним вычитанием получаем прирост месяц к месяцу. На собесе за аналитика этот приём просят почти гарантированно.

Рамка окна: накопительный итог и скользящее среднее

Тут начинается то, где заканчивается уверенность у большинства. У окна есть рамка (frame): какие именно строки попадают в расчёт относительно текущей. Задаётся через ROWS BETWEEN ... AND ....

Накопительный итог (running total) выручки по дням, это рамка от начала до текущей строки:

SELECT
  day,
  revenue,
  SUM(revenue) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM daily;

Семидневное скользящее среднее, это рамка из текущей строки и шести предыдущих:

AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

И любимая ловушка на понимание рамки: LAST_VALUE(x) OVER (ORDER BY day) возвращает не последнее значение партиции, как ждут, а значение текущей строки. Потому что рамка по умолчанию тянется до текущей строки, а не до конца окна. Чтобы получить настоящий последний элемент, рамку надо задать явно: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Кто это знает, тот действительно разобрался в оконках.

NTILE: разбить на равные группы

NTILE(n) делит строки на n примерно равных по численности групп. Нужно разложить пользователей на 10 групп по величине трат (децили)? Одна функция:

SELECT user_id, total_spent,
       NTILE(10) OVER (ORDER BY total_spent DESC) AS decile
FROM user_spend;

Первый дециль это топ-10% по тратам, десятый это нижние 10%. Удобно для сегментации, RFM-анализа, отсечения китов от остальных.

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

• Путают ROW_NUMBER и RANK на равных значениях (см. выше).
• Забывают про рамку и попадаются на LAST_VALUE.
• Пытаются фильтровать по оконной функции прямо в WHERE. Нельзя: оконки считаются после WHERE, поэтому нумерацию и фильтр по ней разносят на два уровня (подзапрос или CTE), как в примере с последним заказом.

Где потренироваться

Оконки ставятся только руками: прочитать про PARTITION BY мало, надо написать десяток запросов, пока рамка и разница рангов не улягутся в голове. В Сеньорчике оконные функции разобраны внутри трека SQL, задачами с вариантами и разбором, а движок возвращает тему, если вы на ней плывёте. Ещё живые вопросы по SQL и метрикам, в подборке для аналитика данных, а более широкий разбор SQL-задач с собесов, в отдельном посте. Начать можно бесплатно.