SQLучебникдля начинающихwindow functionsROW_NUMBERLAGчасть-10

SQL с нуля. Часть 10: Window Functions intro — ROW_NUMBER, RANK, LAG, LEAD

2026-06-02 9 мин

Это часть 10 (финальная) из 10 учебника «SQL с нуля для аналитика». Содержание серии в конце поста. ← Часть 9

TL;DR: Window Functions считают значение для каждой строки, не схлопывая их в группы. ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) нумерует. RANK и DENSE_RANK ранжируют с учётом ties. LAG/LEAD берут предыдущее/следующее значение. SUM() OVER (PARTITION BY ...) — running total. Window — самый частый advanced-вопрос на собесах.

В этой части:

Чем Window отличается от GROUP BY?

!Window: PARTITION делит данные на группы, ORDER BY сортирует, frame определяет окно для агрегата

Как работает Running total — step-by-step:

!Running total: как растёт cumulative sum по дням

GROUP BY схлопывает строки в одну на группу:

-- Было 1000 заказов, после GROUP BY user_id → 100 строк (по числу юзеров)
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;

Window сохраняет все строки, добавляя колонку:

-- Было 1000 заказов, после window → 1000 строк, каждой добавлена сумма по юзеру
SELECT
    order_id,
    user_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id) AS user_total
FROM orders;

Когда нужно «оставить детальные строки + добавить агрегат» — Window. Когда «свернуть в группы» — GROUP BY.

Типичный случай: «топ-3 продукта в каждой категории». Через GROUP BY невозможно (теряется product_id). Через Window: ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) <= 3.

Что такое ROW_NUMBER?

ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) нумерует строки в каждой группе. 
-- Нумерация заказов в каждом юзере по времени
SELECT
    order_id,
    user_id,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_num
FROM orders;

Результат: первый заказ юзера = 1, второй = 2, и т.д.

Топ-1 по группам — самый частый паттерн:

-- Последний заказ каждого юзера
SELECT * FROM (
    SELECT
        o.*,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders o
) sub
WHERE rn = 1;

Чем RANK отличается от ROW_NUMBER?

При одинаковых значениях (ties):

ЗначениеROW_NUMBERRANKDENSE_RANK
100111
100211
9033 (skip 2)2
80443

Топ-3 по выручке (включая ties): DENSE_RANK <= 3.

Что такое LAG и LEAD?

LAG(col, n) OVER (... ORDER BY ts) — значение col за N строк назад. LEAD(col, n) OVER (... ORDER BY ts) — за N строк вперёд. 
-- Текущая выручка vs предыдущий месяц
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_diff
FROM monthly_revenue
ORDER BY month;

Полезно для:

Типичная метрика: MoM growth. revenue - LAG(revenue) OVER (ORDER BY month) — абсолютное изменение. (revenue - LAG(revenue)) / LAG(revenue) * 100 — относительное в процентах.

Как сделать running total (cumulative sum)?

Window-агрегат с ORDER BY:

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

Результат: каждая строка содержит сумму всех предыдущих + текущая.

С partition — running total в каждой группе отдельно:

SELECT
    day,
    country,
    revenue,
    SUM(revenue) OVER (PARTITION BY country ORDER BY day) AS country_cum
FROM daily_revenue;

Какие 6 типичных задач аналитика через Window?

Топ-N в каждой группе

См. ROW_NUMBER пример выше.

Running total

См. SUM OVER пример.

Moving average (7-дневное среднее)

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

(ROWS / RANGE frame подробно — в Window frames гайде).

Procent of total

SELECT
    country,
    revenue,
    revenue * 100.0 / SUM(revenue) OVER () AS pct_of_total
FROM country_revenue;

Сравнение с предыдущим периодом

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS diff
FROM monthly_revenue;

Sessionization (определение сессий по 30-мин разрывам)

SELECT
    user_id,
    event_ts,
    SUM(CASE WHEN event_ts - LAG(event_ts) OVER (
        PARTITION BY user_id ORDER BY event_ts
    ) > INTERVAL '30 min' THEN 1 ELSE 0 END) OVER (
        PARTITION BY user_id ORDER BY event_ts
    ) AS session_id
FROM events;

Что такое OVER без PARTITION BY?

OVER () — все строки = одна большая группа. Полезно для процентов от total: 
SELECT
    country,
    revenue,
    revenue / SUM(revenue) OVER () AS share
FROM country_revenue;

Какие 4 ошибки с window functions?

Частые вопросы про Window Functions

Window работает после WHERE?

Window вычисляется после WHERE, GROUP BY, HAVING, но до ORDER BY и LIMIT. Поэтому WHERE rn = 1 не работает на том же level.

Что такое frame (ROWS BETWEEN ...)?

Окно «соседних строк» для агрегата. По default SUM() OVER (ORDER BY x) = RANGE UNBOUNDED PRECEDING. Подробнее в ROWS vs RANGE vs GROUPS.

Window медленнее GROUP BY?

Обычно да, потому что хранит все строки. На больших таблицах — оптимизируй через индексы или материализацию.

Можно ли COUNT(DISTINCT) в window?

В PG до 16 — нет. В PG 16+ — да. В CH — да.

ROW_NUMBER vs DENSE_RANK для топ-N?

ROW_NUMBER если ты хочешь ровно N строк (даже при ties). DENSE_RANK если ты хочешь «топ-3 уровня» (может быть больше N строк при ties).

Что дальше после учебника?

Поздравляю — ты прошёл базы SQL для аналитика! Что делать дальше:

Например, типичный план после учебника: неделя 1-2 — pet-проект на NYC Taxi с PostgreSQL + Superset, неделя 3 — добавить dbt, неделя 4 — публикация на GitHub + LinkedIn пост. После — подача резюме на Junior DA позиции.

В Pro — безлимит мок-собесов на AI-интервью + 491 SQL-задача + 612 тестовых заданий + 55+ блог-постов.

Навигация по учебнику

← Часть 9 | Часть 10: Window Functions intro | Серия завершена ✓

Содержание серии: 1 · 2 · 3 · 4 · 5 · 6 · 7 · 8 · 9 · 10

← Вернуться к оглавлению

См. также:

Источники

SQL-тренажёр
Тренируй window functions на PostgreSQL 16. 491 задача, первые 5 бесплатно.
Открыть тренажёр →

Похожие статьи