Оконные функции SQL: ROW_NUMBER, RANK, LAG и другие с примерами

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

Что такое окно (window)?

Обычный GROUP BY схлопывает строки:

SELECT department, SUM(salary)
FROM employees
GROUP BY department;
-- 1 строка на отдел

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

Оконная функция считает то же самое, но оставляет каждую строку:

SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;
-- все строки сохраняются, добавляется колонка с суммой по отделу

Синтаксис

функция() OVER (
  PARTITION BY столбец   -- разбить на группы (как GROUP BY)
  ORDER BY столбец       -- порядок внутри группы
  ROWS/RANGE ...         -- размер фрейма (необязательно)
)

Все три части необязательны. OVER () без ничего — вся таблица как одно окно.

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

Самые частые на собеседованиях.

SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

Пример данных:

Разница:

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

Типичная задача: топ-1 зарплата в каждом отделе

SELECT name, department, salary
FROM (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn = 1;

LAG и LEAD — соседние строки

LAG — предыдущая строка. LEAD — следующая.

SELECT
  month,
  revenue,
  LAG(revenue)  OVER (ORDER BY month) AS prev_month,
  LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly_stats;

Считаем рост выручки месяц к месяцу

SELECT
  month,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS diff,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
    1
  ) AS growth_pct
FROM monthly_stats;

Важно: LAG смотрит на предыдущую строку, а не на предыдущий календарный месяц. Если в данных есть пропуски (например, нет данных за февраль), LAG вернёт значение за январь, даже когда текущая строка — март. Для календарного выравнивания нужно делать LEFT JOIN на таблицу-календарь или генерировать ряды через GENERATE_SERIES.

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

SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

По умолчанию SUM() OVER (ORDER BY ...) суммирует от начала до текущей строки включительно.

Нарастающий итог по каждому пользователю

SELECT
  user_id,
  date,
  amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY date) AS user_running_total
FROM transactions;

Скользящее среднее

SELECT
  date,
  value,
  AVG(value) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS avg_7d
FROM metrics;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — последние 7 строк включая текущую. Это называется рамка окна (frame): мы говорим базе «смотри не на все строки сразу, а только на 6 предыдущих и текущую». ROWS означает, что считаем именно строки — даже если даты идут с пропусками, база возьмёт ровно 6 предыдущих строк в таблице.

NTILE — разбить на квантили

SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

Делит строки на 4 равные группы (квартили). Полезно для анализа распределения.

FIRST_VALUE и LAST_VALUE

SELECT name, department, salary,
  FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner
FROM employees;

Ловушка с LAST_VALUE: по умолчанию фрейм — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это значит, что LAST_VALUE вернёт не последнее значение в партиции, а значение текущей строки — классическая ошибка. Всегда явно указывайте фрейм:

LAST_VALUE(name) OVER (
  PARTITION BY department
  ORDER BY salary DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Надёжная альтернатива — использовать FIRST_VALUE с сортировкой по убыванию: это понятнее и не требует помнить про фрейм.

Производительность

Оконные функции — не магия. PARTITION BY и ORDER BY внутри окна требуют сортировки данных. При больших объёмах без подходящего индекса это может превратиться в дорогую дисковую сортировку. Убедитесь, что столбцы из PARTITION BY и ORDER BY проиндексированы — особенно если оконная функция применяется к миллионам строк.

Шпаргалка

Практика

Задачи с оконными функциями есть в нашем тренажёре и в разделе подготовки к собеседованиям. Попробуй — теория усваивается в 10 раз лучше, когда пишешь руками.