Как использовать оконные функции в SQL

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

Оконные функции — это «суперсила» аналитика в SQL. Они позволяют делать то, что в обычном GROUP BY невозможно: running total, сравнение с предыдущей строкой, ранжирование в группе без свёртки. На собеседовании middle-аналитика — это топ-1 тема по SQL.

Ловушка: оконные функции кажутся загадочными. PARTITION BY vs GROUP BY, ORDER BY внутри OVER, ROWS BETWEEN — легко запутаться. В этой статье разберём всё по шагам.

В статье:

• Базовый синтаксис и PARTITION BY
• ROW_NUMBER, RANK, DENSE_RANK
• SUM / AVG OVER (running totals, averages)
• LAG, LEAD (предыдущая / следующая строка)
• Window frames (ROWS BETWEEN)
• Практические примеры

Базовый синтаксис

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

PARTITION BY — разбиение на «группы» (без свёртки строк). ORDER BY — порядок внутри группы. ROWS BETWEEN — окно (какие строки включить).

1. ROW_NUMBER — номер строки

SELECT
    user_id,
    order_id,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_num
FROM orders;

Каждому юзеру свои номера 1, 2, 3...

Классическое применение — последняя запись

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

2. RANK / DENSE_RANK

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

Разница: при связке зарплат RANK пропускает номер, DENSE_RANK нет.

3. SUM OVER — running total

SELECT
    day,
    revenue,
    SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue;

Накопительная сумма.

Running total внутри группы

SELECT
    user_id,
    order_id,
    created_at,
    SUM(total) OVER (PARTITION BY user_id ORDER BY created_at) AS cumulative_spent
FROM orders;

Сколько пользователь потратил к этому моменту.

4. AVG OVER — moving average

SELECT
    day,
    revenue,
    AVG(revenue) OVER (
        ORDER BY day
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7d
FROM daily_revenue;

Скользящее среднее за 7 дней (включая текущий).

5. LAG / LEAD

SELECT
    day,
    revenue,
    LAG(revenue) OVER (ORDER BY day) AS prev_day,
    LEAD(revenue) OVER (ORDER BY day) AS next_day,
    revenue - LAG(revenue) OVER (ORDER BY day) AS dod_change
FROM daily_revenue;

6. FIRST_VALUE / LAST_VALUE

Первое / последнее значение в окне:

SELECT
    user_id,
    order_id,
    total,
    FIRST_VALUE(total) OVER (
        PARTITION BY user_id ORDER BY created_at
    ) AS first_order_amount,
    LAST_VALUE(total) OVER (
        PARTITION BY user_id ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order_amount
FROM orders;

Подвох LAST_VALUE: без явного ROWS окно — только до текущей строки. Нужно UNBOUNDED FOLLOWING.

7. NTILE — разделение на N групп

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

Разбивает на 10 равных частей. Decile 1 — top 10% платящих.

8. Window frames (ROWS vs RANGE)

ROWS — по количеству строк

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW   -- 7 строк
ROWS UNBOUNDED PRECEDING                    -- от начала до текущей
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- всё окно

RANGE — по значениям ORDER BY

RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW  -- по датам

9. Практика: % от total

SELECT
    category,
    revenue,
    revenue * 100.0 / SUM(revenue) OVER () AS pct_of_total
FROM category_revenue;

OVER () без условий = по всей таблице.

10. Практика: топ-3 в каждой группе

SELECT * FROM (
    SELECT
        category,
        product,
        sales,
        DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rnk
    FROM products
) t
WHERE rnk <= 3;

Частые ошибки

1. Window functions в WHERE

Нельзя:

-- ошибка
SELECT * FROM orders WHERE ROW_NUMBER() OVER (...) = 1;

Оберните в подзапрос или CTE.

2. Забыть ORDER BY для RANK

Без ORDER BY результат random.

3. Не использовать PARTITION BY

SUM OVER (ORDER BY day) без PARTITION считает по всей таблице. Для per-user нужно PARTITION.

4. LAST_VALUE без UNBOUNDED FOLLOWING

Default окно — только до текущей строки. LAST_VALUE даст текущую.

На собесе

Оконные функции — 20-30% всех SQL-задач на собесе middle. Готовьте:

• Топ-N в каждой группе
• Running total
• Percent of total
• DoD/WoW сравнения через LAG
• Дедупликация через ROW_NUMBER

Связанные темы

• Оконные функции SQL — шпаргалка
• Задачи на оконные функции
• ROW_NUMBER vs RANK vs DENSE_RANK
• LAG и LEAD в SQL — шпаргалка
• Как посчитать running total в SQL

FAQ

Window functions медленные?

Могут быть, но обычно оптимизированы. Индекс на PARTITION / ORDER BY колонке помогает.

В MySQL работают?

С MySQL 8.0+. В старших — нет, только через self-join.

PARTITION BY vs GROUP BY?

PARTITION BY — не сворачивает строки. GROUP BY — сворачивает в 1 на группу.

Можно window в window?

Нельзя напрямую. Через CTE или подзапрос.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.