Когда в окне есть ORDER BY, у нас появляется концепция «соседа» — строки, которая идёт до или после текущей в этом порядке. Семейство функций LAG/LEAD/FIRST_VALUE/LAST_VALUE/NTH_VALUE позволяет тянуть значения из этих соседей в текущую строку.
Это инструменты для дельт — «насколько этот платёж больше предыдущего», временных промежутков — «сколько прошло между логином и checkout», first-touch / last-touch атрибуции — «что было самой первой покупкой клиента».
LAG — заглянуть назад
LAG(expr, offset, default) возвращает значение expr из строки, которая стоит на offset строк раньше текущей в порядке ORDER BY. Если такой строки нет (мы в начале окна) — возвращается default (по умолчанию NULL).
offsetпо умолчанию1— предыдущая строка.defaultпо умолчаниюNULL.
Для каждой строки LAG достаёт значение из предыдущей в порядке ORDER BY. Для самой первой — NULL (или default).
Каждый платёж видит сумму предыдущего платежа того же клиента — и дельту:
Для первого платежа клиента LAG вернёт NULL (нет предыдущего), и delta тоже будет NULL. Это часто хочется заменить на 0 или специальную метку — для этого используют COALESCE или передают default третьим аргументом LAG.
LEAD — заглянуть вперёд
LEAD(expr, offset, default) — то же самое, но в обратную сторону: значение из строки, стоящей после текущей.
Время между логином и следующим событием клиента — через LEAD:
time_to_next показывает, сколько прошло между этим событием и следующим — полезно для анализа сессий, измерения «времени до checkout», обнаружения долгих пауз.
Это один из самых частых паттернов работы с временными рядами в SQL: «время до следующего события».
Сессионизация: LAG как граница сессии
Классическая задача аналитика — разделить поток событий пользователя на сессии, где сессия — это серия событий с паузой не более N минут между ними. Через LAG это решается элегантно.
Сессионизация: новая сессия начинается, если пауза с предыдущим событием > 30 минут:
Дальше эту метку is_new_session обычно заворачивают в SUM(...) OVER (PARTITION BY customer_id ORDER BY ...) — running sum даст уникальный session_id для каждой сессии. Это паттерн gap-and-island, к которому мы вернёмся в уроке 7.
FIRST_VALUE и LAST_VALUE
FIRST_VALUE(expr) и LAST_VALUE(expr) достают значение из первой и последней строки окна (по порядку ORDER BY). Это функция от окна, а не от соседа — то есть результат зависит от frame.
Для каждого клиента — его первый и последний платёж:
Очень важный нюанс: для LAST_VALUE нужно явно прописать frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Иначе сработает дефолтный frame (UNBOUNDED PRECEDING AND CURRENT ROW), и LAST_VALUE для каждой строки вернёт саму эту строку — потому что она и есть «последняя в окне до сейчас».
Это одна из самых классических ловушек window functions. У большинства начинающих первый запрос с LAST_VALUE ломается ровно поэтому.
Та же ловушка наглядно: LAST_VALUE без явного frame даёт текущую строку:
Видишь: last_value_bug равен amount_cents каждой строки — это и есть последняя строка в дефолтном frame. last_value_correct правильно тянет последний платёж клиента.
NTH_VALUE — N-я строка окна
NTH_VALUE(expr, n) — обобщение: возвращает значение N-й строки окна. Реже используется, но удобно для медиан и квартилей вручную.
Сумма второго платежа клиента — рядом с каждым его платежом:
У клиентов с одним платежом second_payment будет NULL — нет второй строки.
Дельта в процентах — частая аналитика
Объединяем LAG с арифметикой — получаем «процент роста».
Процент роста выручки день-к-дню:
NULLIF(..., 0) защищает от деления на ноль, если предыдущий день дал 0 выручки. Без него запрос упал бы с ошибкой.
Когда LAG не подойдёт
LAG смотрит на предыдущую строку в окне, а не на «предыдущую запись, удовлетворяющую условию». Если ты хочешь найти «предыдущий успешный платёж этого клиента, пропустив cancelled» — LAG не справится напрямую.
Решение: либо отфильтровать таблицу до окна (через CTE), либо использовать LAG с FILTER (WHERE ...)… — но FILTER для LAG/LEAD не поддерживается в PostgreSQL. На практике делают CTE с предварительной фильтрацией.
Дельта между подряд идущими успешными заказами (refunded и cancelled выкинуты):
Чек-лист
LAG(expr, offset, default)— значение из строки наoffsetназад. По умолчаниюoffset=1,default=NULL.LEAD(expr, offset, default)— то же, но вперёд.- Главные кейсы: дельты, время до следующего события, сессионизация, процент роста.
FIRST_VALUEработает с дефолтным frame корректно.LAST_VALUE— только с явным frameUNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, иначе всегда возвращает текущую строку.NTH_VALUE(expr, n)— N-я строка окна; с тем же требованием явного frame.- При делении (процент роста) защищай знаменатель через
NULLIF(..., 0). - LAG/LEAD не имеют FILTER — фильтруй заранее через CTE.