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

Коротко

Все числа ниже посчитаны на учебной базе: 35 341 событие у 4 613 пользователей с 1 июня по 30 августа 2026 года и 1 251 оплата.

• lag(x, n, default) берёт x из строки на n позиций раньше, lead(x, n, default) — на n позиций позже. По умолчанию n = 1, а default = null.
• default срабатывает, только когда соседней строки нет вообще. Если соседняя строка есть, но в ней NULL, вернётся NULL.
• partition by user_id не даёт LAG заглянуть в строки другого пользователя. Без него пауза у первого события человека посчитается от последнего события предыдущего.
• ORDER BY внутри окна должен быть уникальным. Сортировка по дате без tie-breaker дала три разных ответа на один вопрос в DuckDB и PostgreSQL, а правильный — 2 750 из 2 750 — получился только с order by event_time, event_id.
• Отфильтровать по результату LAG в where нельзя: нужен CTE или подзапрос. DuckDB умеет qualify, PostgreSQL 16 — нет.
• lag(x ignore nulls) работает в DuckDB. PostgreSQL 16 отвечает синтаксической ошибкой, там пропуски заполняют через count() over и max() over.

Как устроены LAG и LEAD: синтаксис и три аргумента

Обе функции оконные, поэтому без over (...) их не бывает. Внутри over два параметра: partition by делит строки на независимые группы, order by задаёт порядок внутри группы. «Предыдущая строка» существует только относительно этого порядка — в таблице самой по себе строки не упорядочены.

Первый аргумент — выражение, значение которого нужно достать: колонка, count(*), amount * 2. Второй — смещение, на сколько строк смотреть назад или вперёд. Третий — что вернуть, если строки на таком расстоянии нет. lag(x, -1) в PostgreSQL и DuckDB работает как lead(x), но читать такой код тяжелее, поэтому для «следующей» строки берите lead.

Если одно окно нужно нескольким функциям, вынесите его в window w as (...). Запрос станет короче, и порядок сортировки не разойдётся между колонками — это частый источник странных цифр, когда окно копируют руками и в одной копии забывают tie-breaker.

lag(expr, offset, default)  over (partition by ... order by ...)
lead(expr, offset, default) over (partition by ... order by ...)

select
  user_id,
  event_time,
  lag(event_time)  over w as prev_time,
  lead(event_time) over w as next_time
from events
window w as (partition by user_id order by event_time, event_id);

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

Самая частая задача для LAG — время между соседними действиями человека. Разница двух timestamp даёт интервал, а partition by user_id гарантирует, что первое событие каждого пользователя получит NULL, а не паузу от чужого события.

У пользователя 1 первые шесть событий выглядят так: первое без предыдущего, дальше паузы 22:00:00, 05:00:00, 2 days 23:00:00, 23:00:00 и 1 day. По всей базе NULL получают 4 613 строк — ровно по одной на пользователя. Это быстрая проверка окна: если NULL всего один, потерялся partition by, и первое событие каждого человека получило паузу от чужого события.

Распределение пауз сразу говорит о продукте. Медиана — 47 часов, 90-й перцентиль — 260 часов, самая короткая пауза — 35 минут. Для сессий это важный факт, к нему вернёмся ниже.

select
  user_id,
  event_time,
  event_name,
  event_time - lag(event_time) over w as gap
from events
window w as (partition by user_id order by event_time, event_id)
order by user_id, event_time;

Почему LAG возвращает разные ответы, если в ORDER BY есть ничьи

Если две строки внутри партиции равны по всем колонкам order by, их порядок не определён. База вправе поставить их как угодно, и LAG вернёт то значение, которое оказалось раньше при этом конкретном запуске. Ошибки не будет, будет правдоподобное неверное число.

Проверка на учебной базе. Каждый workspace_created идёт через 35 минут после app_open того же человека, значит правильный ответ на вопрос «что было перед созданием пространства» — app_open во всех 2 750 случаях. Но если упорядочить события по дате, а не по времени, внутри дня появляются ничьи: у 3 778 пар «пользователь × день» больше одного события. У 11 433 строк из 35 341 предыдущее событие меняется в зависимости от того, как база расставила события внутри дня.

Один и тот же запрос дал три разных ответа. Ни один не совпал с правильным, и ни один не выглядит подозрительно, если не знать ответа заранее. Лекарство — добавить в order by колонку, которая делает порядок уникальным: event_time, event_id. В учебной базе ничьих по event_time внутри пользователя нет, но в живом трекинге события с одинаковой секундой встречаются постоянно.

with x as (
  select
    event_name,
    lag(event_name) over (
      partition by user_id
      order by cast(event_time as date)   -- ничьи внутри дня
    ) as prev_name
  from events
)
select prev_name, count(*)
from x
where event_name = 'workspace_created'
group by prev_name;

Что возвращает LAG, если предыдущей строки нет

По умолчанию NULL. Третий аргумент меняет это значение, но только для строк, у которых соседа на нужном расстоянии нет: первой строки партиции для LAG и последней для LEAD. На ряду 10, NULL, 30 вызов lag(x, 1, 0) вернёт 0, 10 и NULL. Во второй строке соседом был NULL, и функция честно его вернула. Чтобы заменить и такие значения, нужен coalesce поверх LAG.

С заменой NULL на ноль легко испортить метрику. На дневном DAU dau - lag(dau, 1, 0) для 1 июня даёт прирост +32: как будто за день пришли 32 новых активных пользователя. На деле сравнивать не с чем, и NULL в этой строке честнее нуля. Default удобен, когда он действительно означает «ничего не было»: ноль событий до регистрации, пустая строка вместо следующего шага.

Тип default должен приводиться к типу выражения. lag(paid_at, 1, 'нет') падает в обоих движках: PostgreSQL пишет invalid input syntax for type date: "нет", DuckDB — Conversion Error: invalid date field format. Если нужна подпись, сначала приведите дату к тексту.

Как сравнить DAU со вчера и с тем же днём прошлой недели

LAG работает по строкам, поэтому на сырых событиях он найдёт предыдущее событие, а не предыдущий день. Сначала соберите одну строку на день, потом применяйте LAG. Окно можно поставить и прямо поверх агрегата: lag(count(distinct user_id)) over (order by ...) в запросе с group by работает в обоих движках, потому что окна считаются после группировки.

Смещение больше единицы полезно для недельной сезонности. В учебной базе DAU в субботу ниже пятничного в 12 субботах из 13, и сравнение «к вчера» каждую субботу показывает падение. lag(dau, 7) сравнивает субботу с прошлой субботой, и шум дней недели уходит.

Последняя строка требует отдельного внимания. 30 августа DAU равен 223 против 530 накануне: −307 к вчера и −54,9% к прошлому воскресенью. Это не авария, а неполный день: последнее событие в выгрузке — 12:59. LAG не знает, что день не закончился, поэтому неполный хвост отрезают фильтром до расчёта или помечают в отчёте.

lag(dau, 7) предполагает, что в ряду нет пропущенных дат. В учебной базе все 91 день на месте. Если в витрине бывают дни без строк, семь строк назад окажутся девятью днями назад, и календарь нужно достроить через generate_series до применения LAG.

with daily as (
  select
    cast(event_time as date) as event_date,
    count(distinct user_id) as dau
  from events
  group by 1
)
select
  event_date,
  dau,
  dau - lag(dau) over w as diff_1d,
  lag(dau, 7) over w as dau_week_ago,
  round(100.0 * (dau - lag(dau, 7) over w) / lag(dau, 7) over w, 1) as wow_pct
from daily
window w as (order by event_date)
order by event_date;

Как разметить сессии через LAG и почему порог важнее кода

Сессия в потоке событий — это цепочка действий без паузы длиннее порога. LAG даёт паузу, флаг «пауза не меньше порога или предыдущего события нет» отмечает начало сессии, а сумма флагов по пользователю — номер сессии. Полная разметка с sum() over разобрана в статье про оконные функции. Здесь посмотрим на то, что обычно пропускают: насколько ответ зависит от порога.

Раз самая короткая пауза в учебной базе — 35 минут, порог в 30 минут делает сессией каждое событие: 35 341 сессия на 35 341 событие. Порог в час склеивает app_open с последующим workspace_created и даёт 32 591 сессию. Три часа добавляют к нему report_created, который идёт через 85 минут после создания пространства, — 30 477. Код одинаковый, меняется одно число, а крайние ответы расходятся на 4 864 сессии, или на 14%.

Порог выбирают по распределению пауз, которое мы уже посчитали через LAG: если между шагами одного сценария проходит больше получаса, стандартные 30 минут разорвут сценарий на части.

with g as (
  select
    user_id,
    event_time - lag(event_time) over (
      partition by user_id order by event_time, event_id
    ) as gap
  from events
)
select
  count(*) filter (where gap is null or gap >= interval '30 minutes') as sessions_30m,
  count(*) filter (where gap is null or gap >= interval '60 minutes') as sessions_60m,
  count(*) filter (where gap is null or gap >= interval '3 hours')    as sessions_3h
from g;

Что пользователь делает после события: LEAD

Вернёмся к вопросу продакта. LEAD подставляет в строку workspace_created имя и время следующего события того же человека, и дальше это обычная группировка. 1 775 человек из 2 750 (64,5%) следующим шагом создают отчёт, 796 (28,9%) просто открывают приложение в другой день, 98 (3,6%) приглашают коллег, 72 (2,6%) делают экспорт. У 9 человек (0,3%) после создания пространства событий нет: выгрузка закончилась раньше, чем они вернулись.

Медиана времени до следующего шага говорит больше, чем сама доля. До отчёта проходит около 1,4 часа, до повторного открытия — 24,4 часа. В учебной базе все 1 775 отчётов созданы ровно через 85 минут после пространства — так устроен генератор данных. На живых данных такое совпадение минимума и максимума означало бы автоматическое событие, а не решение человека.

Главная ловушка LEAD — фильтр в том же запросе. Условие where event_name = 'workspace_created' выполняется раньше окна, и LEAD видит только отфильтрованные строки. Каждый пользователь создаёт одно пространство, поэтому в таком запросе все 2 750 следующих событий окажутся NULL. Если оставить в фильтре три типа событий, картина исказится иначе: 1 775 отчётов, 331 экспорт и 644 NULL. Считайте окно на полном потоке, а фильтр ставьте снаружи.

Смещение 2 отвечает на вопрос «что через шаг»: lead(event_name, 2) после создания пространства — это 2 048 открытий приложения, 337 приглашений, 264 экспорта и 101 NULL.

with next_step as (
  select
    user_id,
    event_name,
    lead(event_name) over w as next_name,
    lead(event_time) over w as next_time
  from events
  window w as (partition by user_id order by event_time, event_id)
)
select
  coalesce(next_name, '(нет события)') as next_name,
  count(*) as users,
  round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
from next_step
where event_name = 'workspace_created'
group by 1
order by users desc;

Через сколько дней вторая оплата и почему NULL не значит «не вернулся»

LEAD по оплатам даёт дату следующего платежа, а row_number() в том же окне оставляет только первую оплату каждого покупателя. Из 912 покупателей вторую оплату сделали 290. Наивная доля — 31,8%.

Но NULL в next_paid_at смешивает два случая: человек не заплатил повторно и человек ещё не успел. Интервал до второй оплаты во всех 290 случаях — ровно 30 дней, это продление месячной подписки. Значит, у первых оплат после 31 июля второй платёж просто не мог попасть в выгрузку до 30 августа. Таких 410, и у всех NULL. Среди 502 первых оплат, у которых было 30 дней, повторно заплатили 290 — 57,8%, почти вдвое больше наивной доли.

Правило общее для LEAD по времени: прежде чем считать долю NULL, отрежьте строки, у которых следующее событие физически не могло наступить до конца данных.

with p as (
  select
    user_id,
    paid_at,
    row_number() over w as payment_num,
    lead(paid_at)  over w as next_paid_at
  from payments
  window w as (partition by user_id order by paid_at, payment_id)
)
select
  paid_at <= date '2026-07-31' as had_30_days,
  count(*) as buyers,
  count(next_paid_at) as repeat_buyers,
  round(100.0 * count(next_paid_at) / count(*), 1) as repeat_pct
from p
where payment_num = 1
group by 1;

Почему LAG нельзя поставить в WHERE и что делать вместо этого

Окна считаются после where, group by и having, поэтому условие на их результат в where невозможно. PostgreSQL отвечает window functions are not allowed in WHERE, DuckDB — WHERE clause cannot contain window functions. Переносимый способ — посчитать LAG в CTE или подзапросе и фильтровать снаружи.

Так находятся возвраты после долгого перерыва: событие, перед которым пауза не меньше 14 дней. В учебной базе их 1 852 у 1 618 пользователей, в DuckDB и PostgreSQL число одинаковое.

В DuckDB, Snowflake и BigQuery есть qualify — аналог having для окон, и запрос укорачивается на один уровень. PostgreSQL 16 на qualify отвечает синтаксической ошибкой. Если код должен работать в обоих местах, оставайтесь на CTE.

-- работает в PostgreSQL и DuckDB
with g as (
  select
    user_id,
    event_time,
    event_time - lag(event_time) over (
      partition by user_id order by event_time, event_id
    ) as gap
  from events
)
select count(*) as comebacks, count(distinct user_id) as users
from g
where gap >= interval '14 days';

-- только DuckDB: фильтр по окну без CTE
select user_id, event_time,
  event_time - lag(event_time) over (
    partition by user_id order by event_time, event_id
  ) as gap
from events
qualify gap >= interval '14 days';

Есть ли IGNORE NULLS в LAG

Иногда нужна не предыдущая строка, а предыдущее известное значение: последний тариф, последняя непустая версия приложения. Стандарт SQL описывает для этого ignore nulls. В DuckDB 1.5.4 lag(x ignore nulls) работает: на ряду 10, NULL, NULL, 40, NULL он возвращает NULL, 10, 10, 10, 40. PostgreSQL 16 отвечает синтаксической ошибкой и на lag(x ignore nulls), и на lag(x) ignore nulls over (...).

В PostgreSQL то же получается в три шага. count(x) over (order by t) растёт только на непустых значениях и нумерует группы «значение и пустые строки после него». max(x) over (partition by группа) протягивает значение внутри группы. Обычный LAG поверх протянутой колонки даёт предыдущее известное значение — тот же результат, что lag(x ignore nulls) в DuckDB.

with grp as (
  select t, x, count(x) over (order by t) as grp
  from v
), filled as (
  select t, x, max(x) over (partition by grp) as last_known
  from grp
)
select t, x, lag(last_known) over (order by t) as prev_known
from filled
order by t;

Что попробовать на учебной базе

Откройте песочницу SQL-курса и повторите запрос про следующий шаг после workspace_created, но для report_created: куда люди идут после первого отчёта. Затем замените order by event_time, event_id на сортировку по дате и сравните ответ. Если числа поменялись, вы увидели ничьи своими глазами.

Второе упражнение — посчитать долю повторных оплат по тарифам с отсечкой по дате. Проверьте, что без отсечки доля занижена одинаково для всех тарифов или по-разному.