LAG и LEAD в SQL: как сравнить строку с предыдущей и следующей

Если вам нужно сравнить строку с соседней — посмотреть, какая выручка была в прошлом месяце, сколько секунд прошло между двумя кликами пользователя, когда он купил в следующий раз, — вам не нужен ни self-join, ни курсор, ни выгрузка в Python. В SQL для этого есть две оконные функции: LAG заглядывает в предыдущую строку окна, LEAD — в следующую. Пишете LAG(revenue) OVER (ORDER BY month) — и в текущей строке появляется выручка прошлого месяца, рядом с текущей. Дальше вычитаете, делите, ставите флаг. Ниже — как это устроено и где легко наступить на грабли.

Чем LAG отличается от LEAD?

Обе функции берут значение из другой строки того же окна, разница только в направлении. LAG смотрит назад — на строку, которая по сортировке идёт раньше. LEAD смотрит вперёд — на строку, которая идёт позже. Всё остальное у них одинаковое: и синтаксис, и поведение на границах, и зависимость от ORDER BY.

На практике LAG встречается в разы чаще, потому что мы обычно спрашиваем «а как было в прошлый раз» — прошлый месяц, прошлая покупка, предыдущее событие. LEAD полезен, когда нужно заглянуть в будущее относительно текущей строки: когда пользователь вернулся после этой сессии, сколько он прожил до следующего платежа, каким был следующий статус заказа. По сути это одна и та же операция «сдвинь колонку на N строк», просто в разные стороны.

Важно понять главное: это не агрегация. SUM или AVG схлопывают группу в одно число, а LAG/LEAD не меняют количество строк. Сколько строк было на входе — столько и останется, просто в каждой добавится колонка со значением соседа. Именно поэтому их удобно смешивать с обычными полями в одном SELECT.

Как читать синтаксис LAG(col, n, default) OVER (...)?

Полная форма выглядит так:

LAG(expression, offset, default_value)
    OVER (PARTITION BY ... ORDER BY ...)

Разберу по частям. expression — что тащим из соседней строки, обычно имя колонки. offset — на сколько строк назад смотреть; по умолчанию 1, то есть ровно предыдущая строка. Если написать LAG(revenue, 2), получите значение через строку. default_value — что подставить, если соседа нет (об этом отдельно ниже). Второй и третий аргументы необязательны, чаще всего пишут просто LAG(revenue).

В скобках OVER — самое главное. PARTITION BY режет данные на независимые группы: сдвиг не перепрыгнет из одной группы в другую. Для пользовательских метрик это почти всегда PARTITION BY user_id, чтобы событие одного человека не подтянуло событие другого. ORDER BY задаёт, что вообще считать «предыдущим» и «следующим». Без него понятия «соседняя строка» просто не существует.

SELECT
    user_id,
    order_date,
    amount,
    LAG(amount) OVER (
        PARTITION BY user_id
        ORDER BY order_date
    ) AS prev_amount
FROM orders;

Здесь для каждого пользователя заказы выстраиваются по дате, и prev_amount — сумма его же предыдущего заказа. Если хотите пощупать это руками на живой базе, соберите такой запрос в SQL-тренажёре — там сразу видно, как меняется вывод от перестановки ORDER BY.

Как посчитать рост выручки месяц к месяцу?

Это классическая задача, ради которой половина аналитиков и узнаёт про LAG. Есть помесячная выручка, нужно посчитать прирост в процентах к прошлому месяцу — тот самый MoM.

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) AS diff,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
              / LAG(revenue) OVER (ORDER BY month),
        1
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month;

Логика простая: LAG(revenue) даёт выручку предыдущего месяца, вычитаем — получаем абсолютный прирост, делим на прошлую выручку и умножаем на 100 — получаем процент. Обратите внимание на 100.0: если написать целочисленную 100, во многих СУБД деление округлится до нуля, и вы будете долго искать, почему рост всегда «0%».

Если строите так квартал к кварталу или год к году, меняется только гранулярность строк, а формула та же. А когда захотите сравнить не с прошлым месяцем, а с тем же месяцем год назад (YoY, чтобы убрать сезонность), увеличьте offset: LAG(revenue, 12) при помесячных данных вернёт значение ровно двенадцать строк назад. Тему динамики выручки я подробнее разбираю в разборе метрик и их формул — там видно, как MoM-прирост стыкуется с остальными продуктовыми показателями.

Как измерить время между событиями пользователя?

Второй сценарий, где LAG незаменим, — интервалы. Есть лог событий, нужно понять, сколько прошло между соседними действиями одного человека. Тут обязателен PARTITION BY user_id, иначе разрыв посчитается между разными людьми и превратится в мусор.

SELECT
    user_id,
    event_time,
    event_type,
    LAG(event_time) OVER (
        PARTITION BY user_id
        ORDER BY event_time
    ) AS prev_event_time,
    event_time - LAG(event_time) OVER (
        PARTITION BY user_id
        ORDER BY event_time
    ) AS gap
FROM events;

Колонка gap — это пауза между текущим и предыдущим событием. В PostgreSQL вычитание двух timestamp даёт interval, из которого легко достать секунды через EXTRACT(EPOCH FROM gap). Из таких интервалов вырастает куча полезного: среднее время между покупками, время до второго действия, тайминг в воронке. Похожие задачи на интервалы и оконки регулярно встречаются на собеседованиях — примеры формулировок собраны в подборке вопросов для аналитика.

Как пометить начало новой сессии?

А вот где интервалы превращаются в настоящую логику. Сессионизация: сырой поток событий надо разбить на сессии, считая, что если человек молчал дольше 30 минут — началась новая. Сначала LAG даёт время предыдущего события, потом сравниваем разрыв с порогом.

WITH gaps AS (
    SELECT
        user_id,
        event_time,
        LAG(event_time) OVER (
            PARTITION BY user_id
            ORDER BY event_time
        ) AS prev_time
    FROM events
)
SELECT
    user_id,
    event_time,
    CASE
        WHEN prev_time IS NULL
          OR event_time - prev_time > INTERVAL '30 minutes'
        THEN 1 ELSE 0
    END AS is_new_session
FROM gaps;

Флаг is_new_session равен 1 в двух случаях: это самое первое событие пользователя (prev_time IS NULL) или пауза превысила полчаса. Дальше одним движением можно превратить эти флажки в сквозной номер сессии — накопительной суммой:

SELECT
    user_id,
    event_time,
    SUM(is_new_session) OVER (
        PARTITION BY user_id
        ORDER BY event_time
    ) AS session_number
FROM sessionized;

Каждый новый флаг увеличивает счётчик, между флагами номер держится — и события внутри одной сессии получают одинаковый session_number. Это уже готовая заготовка для расчёта длины сессии, глубины и bounce rate. Такие продуктовые разборы «от сырого лога до метрики» есть в библиотеке кейсов, где логика собрана целиком, а не кусками.

Откуда берётся NULL на границах окна?

Это подвох номер один. У самой первой строки в группе нет предыдущей — значит LAG вернёт NULL. У самой последней нет следующей — LEAD вернёт NULL. Само по себе это нормально и логично, проблемы начинаются, когда этот NULL попадает в арифметику. Любое выручка - NULL даёт NULL, деление на NULL тоже, и в отчёте появляются пустые ячейки там, где вы ждали числа.

Есть два способа с этим жить. Первый — третий аргумент функции: LAG(revenue, 1, 0) подставит 0 вместо отсутствующего соседа. Но осторожнее: если потом делите на это значение, ноль в знаменателе уронит запрос ошибкой деления на ноль. Для процентного прироста ноль как «прошлая выручка» — плохая идея. Второй способ надёжнее — оставить NULL и обработать его явно через CASE или NULLIF:

SELECT
    month,
    revenue,
    CASE
        WHEN LAG(revenue) OVER (ORDER BY month) IS NULL THEN NULL
        ELSE ROUND(
            100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
                  / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
            1
        )
    END AS mom_growth_pct
FROM monthly_revenue;

Почему ORDER BY внутри окна — не формальность

Отсюда правило: у LAG и LEAD всегда должен быть явный ORDER BY в окне, и сортировать нужно по той колонке, которая задаёт вашу хронологию — по дате, по времени события, по номеру. Не путайте его с ORDER BY в конце запроса: тот отвечает только за то, как строки лягут в итоговый вывод, и на работу оконной функции не влияет. Это два независимых ORDER BY, и первый — обязательный. Если разбивка на группы тоже важна, добавляйте PARTITION BY, иначе сдвиг «переползёт» границу между пользователями или товарами. Разбор оконного синтаксиса целиком, с рамками и порядком вычисления, лежит в справочнике по SQL, а пошаговый заход в оконки — в курсе по SQL.

То же самое в pandas: shift() вместо LAG

Если данные уже в датафрейме, аналог LAG — метод shift. shift(1) сдвигает колонку вниз (смотрит назад, как LAG), shift(-1) — вверх (вперёд, как LEAD). Ровно как в SQL, критично сначала отсортировать, а для помесячной или пользовательской логики — сгруппировать.

# рост выручки месяц к месяцу
df = df.sort_values('month')
df['prev_revenue'] = df['revenue'].shift(1)
df['mom_growth_pct'] = (df['revenue'] / df['prev_revenue'] - 1) * 100

# разрыв между событиями внутри пользователя
df = df.sort_values(['user_id', 'event_time'])
df['prev_time'] = df.groupby('user_id')['event_time'].shift(1)
df['gap'] = df['event_time'] - df['prev_time']

Короткая шпаргалка и типичные грабли

Собираю в одном месте то, на чём чаще всего спотыкаются.

• LAG смотрит назад, LEAD — вперёд; в остальном они идентичны.
• Без ORDER BY в окне результат бессмысленный, хотя запрос не упадёт. Это ошибка номер один.
• Забыли PARTITION BY — сдвиг перепрыгнет границу между пользователями и подтянет чужие данные.
• На первой и последней строке группы вылезает NULL — обрабатывайте через третий аргумент, CASE или NULLIF.
• Делите — не забудьте 100.0 вместо 100 и защиту знаменателя NULLIF(x, 0).
• offset больше 1 — это шаг «через строку»: LAG(x, 12) на помесячных данных даёт значение год назад, удобно для YoY.

Если хотите закрепить не по одному примеру, а прогнать десятки вариаций с автопроверкой — в SQL-тренажёре есть отдельные блоки по оконным функциям, а полный доступ ко всем задачам, кейсам и разборам открывает Pro. Это самый быстрый способ довести оконки до автоматизма к собеседованию.