Оконные функции SQL
Оконные функции — самая сложная и самая ценная тема на SQL-собеседованиях в Яндекс, Ozon, Авито. Позволяют считать ранги, скользящие суммы, сравнивать строки между собой без GROUP BY.
Содержание
Что такое оконная функция
Обычные агрегатные функции (SUM, COUNT) сворачивают группу строк в одну. Оконные функции вычисляют значение для каждой строки, учитывая соседние строки — «окно».
Ключевое слово — OVER(). Всё что написано в скобках определяет окно.
-- Для каждого заказа — суммарная выручка пользователя
SELECT
user_id,
order_id,
amount,
SUM(amount) OVER (PARTITION BY user_id) AS user_total
FROM orders;PARTITION BY и ORDER BY
PARTITION BY — делит строки на разделы (как GROUP BY, но не схлопывает строки). ORDER BY внутри OVER — определяет порядок строк в окне (нужен для ранжирующих функций и нарастающего итога).
-- Ранг заказа по сумме внутри каждого пользователя
SELECT
user_id,
order_id,
amount,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
FROM orders;ROW_NUMBER, RANK, DENSE_RANK
ROW_NUMBER() — уникальный номер строки, без пропусков, без повторов. RANK() — ранг с пропусками при одинаковых значениях (1, 1, 3, 4...). DENSE_RANK() — ранг без пропусков (1, 1, 2, 3...).
Классическая задача — найти N-ю запись по условию (первый заказ каждого пользователя, топ-3 товара в категории).
-- Первый заказ каждого пользователя
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
FROM orders
) t
WHERE rn = 1;LAG и LEAD — соседние строки
LAG(col, n) — значение колонки из строки на n позиций выше. LEAD(col, n) — значение колонки из строки на n позиций ниже.
Применение: рост выручки месяц к месяцу, день к дню.
-- Месячная выручка и прирост к прошлому месяцу
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
revenue - LAG(revenue) OVER (ORDER BY month) AS diff
FROM monthly_revenue;Нарастающий итог (running total)
SUM() OVER с ORDER BY — нарастающий итог. Каждая строка получает сумму всех предыдущих строк плюс текущую.
-- Нарастающая выручка по дням
SELECT
order_date,
daily_revenue,
SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total
FROM daily_stats;Процент от группы
Частая задача: доля каждого товара/пользователя в общей выручке.
-- Доля каждой категории в общей выручке
SELECT
category,
revenue,
ROUND(
revenue * 100.0 / SUM(revenue) OVER (),
2
) AS pct
FROM category_revenue
ORDER BY pct DESC;Закрепи знания на практике
Решай реальные задачи с собеседований прямо в браузере — без установки.
Решить задачи на оконные функции →