Window functions advanced: ROWS BETWEEN и LAG-трюки

За пределами ROW_NUMBER

Базовые оконные функции (ROW_NUMBER, RANK, LAG, SUM OVER) знают все. Но настоящая сила открывается, когда начинаете использовать frame clauses, dynamic partitioning и комбинировать LAG/LEAD с нетривиальной логикой.

В этой статье — продвинутые паттерны, которые отличают senior-уровень владения SQL от middle.

Frame clause: ROWS vs RANGE

В оконной функции после ORDER BY можно указать рамку (frame), которая задаёт, какие строки входят в окно относительно текущей.

SUM(amount) OVER (
    ORDER BY DATE
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7d_sum

Это скользящая сумма за 7 дней — 6 предыдущих плюс текущий. Стандартный приём для smoothing метрик вроде DAU.

Но есть нюанс — ROWS и RANGE работают по-разному.

ROWS считает физические строки. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — ровно 7 строк независимо от значений.

RANGE работает по значениям. RANGE BETWEEN INTERVAL '6 day' PRECEDING AND CURRENT ROW — все строки, у которых дата в интервале 6 дней. Если в один день 5 строк — все они учитываются.

На собеседовании любят спрашивать разницу. Ответ: ROWS для физических строк, RANGE для значений (нужен для работы с дубликатами в ORDER BY).

Centered moving average

Скользящее среднее с центрированием — не просто «за последние 7 дней», а «3 до + текущий + 3 после»:

SELECT
    DATE,
    dau,
    AVG(dau) OVER (
        ORDER BY DATE
        ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ) AS centered_ma_7d
FROM daily_metrics;

Даёт более сглаженную кривую, чем обычное rolling average. Полезно для анализа трендов без лага.

Минус: для последних 3 дней окно неполное. Надо либо exclude их из визуализации, либо смириться с искажением на концах.

UNBOUNDED PRECEDING

Для нарастающего итога используют UNBOUNDED PRECEDING:

SELECT
    DATE,
    daily_new_users,
    SUM(daily_new_users) OVER (
        ORDER BY DATE
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_users
FROM signups_daily;

По умолчанию, если указан ORDER BY но не указан frame, Postgres использует RANGE UNBOUNDED PRECEDING AND CURRENT ROW. Для уникальных значений это то же самое что ROWS, но для дубликатов — разное. Всегда лучше явно указывать ROWS.

LAG с N offset

Базовый LAG(col) возвращает предыдущее значение. Но есть второй параметр — offset:

SELECT
    DATE,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY DATE) AS prev_day,
    LAG(revenue, 7) OVER (ORDER BY DATE) AS prev_week,
    LAG(revenue, 30) OVER (ORDER BY DATE) AS prev_month
FROM daily_revenue;

Это даёт сравнение с разными периодами в одном запросе. Полезно для расчёта недельных/месячных изменений.

Третий параметр — default value для случая, когда смещение выходит за границы:

LAG(revenue, 1, 0) OVER (ORDER BY DATE) AS prev_day_or_zero

Без default вернулся бы NULL. С ним — 0. Удобно, чтобы не обрабатывать NULL потом в процентных расчётах.

Session detection

Один из самых красивых приёмов оконных функций — определение сессий пользователя.

Задача: у каждого user есть event-ы, нужно разбить их на сессии (30-минутный gap разделяет сессии).

WITH events_with_gap AS (
    SELECT
        user_id,
        event_time,
        event_time - LAG(event_time) OVER (
            PARTITION BY user_id ORDER BY event_time
        ) AS gap
    FROM events
),
events_with_session_start AS (
    SELECT
        user_id,
        event_time,
        CASE
            WHEN gap IS NULL OR gap > INTERVAL '30 min' THEN 1
            ELSE 0
        END AS is_session_start
    FROM events_with_gap
)
SELECT
    user_id,
    event_time,
    SUM(is_session_start) OVER (
        PARTITION BY user_id ORDER BY event_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_id
FROM events_with_session_start;

Три шага:

Первый — вычислить gap между событиями пользователя через LAG.

Второй — отметить события, где gap больше 30 минут, как начало новой сессии.

Третий — cumulative SUM по session starts даёт session_id: каждый новый session start увеличивает id.

После этого GROUP BY (user_id, session_id) позволяет считать метрики по сессиям.

Это classic-задача на middle+ SQL-собесе. Если умеете разложить на шаги и объяснить — зачёт.

Глубокая практика таких задач сильно помогает на технических интервью в топовых компаниях. В тренажёре Карьерник есть специальный блок сложных оконных задач с разборами.

Top-N по группе

Классическая задача — выбрать топ-3 товара в каждой категории:

WITH ranked AS (
    SELECT
        category,
        product_id,
        revenue,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY revenue DESC
        ) AS rn
    FROM product_stats
)
SELECT category, product_id, revenue
FROM ranked
WHERE rn <= 3;

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

Для «топ-3 уникальных позиций по выручке» — DENSE_RANK. Для «строго 3 строки» — ROW_NUMBER.

Running max и min

Не очевидное применение — отслеживать максимум за всё время до текущей точки:

SELECT
    DATE,
    revenue,
    MAX(revenue) OVER (
        ORDER BY DATE
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS all_time_high
FROM daily_revenue;

Это показывает, был ли это исторический максимум или нет. Удобно для финансовых метрик.

Аналогично MIN() OVER — исторический минимум.

NTILE: распределение на кванты

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

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

Это разбивает пользователей на 10 дециль. Decile=1 — топ 10%, decile=10 — нижние 10%.

Популярно для RFM-анализа и сегментации. Быстро и без ручных порогов.

FIRST_VALUE и LAST_VALUE

Берут первое или последнее значение в окне. Ловушка — дефолтный frame для этих функций.

-- Это не то, что вы ожидаете
SELECT
    user_id,
    order_id,
    order_date,
    FIRST_VALUE(order_id) OVER (
        PARTITION BY user_id ORDER BY order_date
    ) AS first_order,
    LAST_VALUE(order_id) OVER (
        PARTITION BY user_id ORDER BY order_date
    ) AS last_order  -- БАГ: это не последний заказ!
FROM orders;

FIRST_VALUE работает интуитивно. LAST_VALUE — нет. Дефолтный frame заканчивается на CURRENT ROW, поэтому LAST_VALUE возвращает не последнее значение в партиции, а последнее на данный момент — то есть текущее.

Чтобы получить реально последний:

LAST_VALUE(order_id) OVER (
    PARTITION BY user_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_order

Явно указываем, что окно — от начала до конца партиции.

Альтернатива — использовать FIRST_VALUE с обратным ORDER BY:

FIRST_VALUE(order_id) OVER (
    PARTITION BY user_id ORDER BY order_date DESC
) AS last_order

Проще и без ловушки.

Процент от общей суммы

Удобный паттерн — считать процент вклада:

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

Двойной SUM — внешний работает как оконная функция над агрегатом внутреннего GROUP BY. Даёт общую сумму в каждой строке результата.

Typical ошибки

Забыть про ORDER BY в оконной функции, когда она нужна. LAG без ORDER BY возвращает undefined.

Использовать RANGE по умолчанию, ожидая поведения ROWS. С дубликатами в ORDER BY результат будет другой.

LAST_VALUE без явного frame — получаете current row, не последнее значение.

Помещать оконную функцию в WHERE напрямую. Нельзя: оконные функции выполняются после WHERE. Нужен subquery или CTE.

Читайте также

• Оконные функции SQL: шпаргалка
• Задачи на оконные функции
• LAG и LEAD в SQL
• PARTITION BY в SQL

FAQ

Можно ли использовать оконные функции в WHERE?

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

Window function или GROUP BY?

Window — когда нужно сохранить все строки и добавить агрегат в контексте. GROUP BY — когда нужно свернуть данные в сгруппированный результат.

Performance window functions?

Сравнимо с GROUP BY для простых случаев. Сложные окна с большими PARTITION могут быть тяжелее. Всегда смотрите EXPLAIN.

Можно ли несколько оконных функций в одном запросе?

Да, с одинаковыми или разными OVER. Postgres оптимизирует, где может, объединяя их обработку.