Что такое LAG и LEAD в SQL? Соседние строки в окне для начинающих

LAG и LEAD — это оконные функции, которые смотрят на соседние строки. LAG — на предыдущую (по сортировке окна), LEAD — на следующую.

В обычном SELECT каждая строка «знает» только себя. С LAG строка знает «сумму предыдущего заказа», «дату прошлого визита», «цену вчера». Это позволяет в одном запросе считать разности между соседними событиями — то, что без оконных функций было адом из self-join'ов.

Зачем нужны LAG и LEAD

Главные сценарии:

• Day-over-day: «насколько выручка выросла по сравнению с предыдущим днём».
• Time-to-next-event: «сколько секунд между двумя кликами одного юзера».
• Изменение цены: «выросла или упала цена товара по сравнению с прошлой записью».
• Сравнение с прошлым месяцем: «MoM growth».

До оконных функций такое решалось через self-join по rownumber - 1 или коррелированные подзапросы. LAG/LEAD свели всё к одной строке.

Базовый синтаксис

LAG(column [, offset [, default]]) OVER (
  PARTITION BY ...
  ORDER BY ...
)

LEAD(column [, offset [, default]]) OVER (...)

• column — какое значение взять.
• offset — на сколько строк назад/вперёд (по умолчанию 1).
• default — что вернуть, если строки нет (по умолчанию NULL).

SELECT
  date, visits,
  LAG(visits)  OVER (ORDER BY date) AS prev_visits,
  LEAD(visits) OVER (ORDER BY date) AS next_visits
FROM daily_stats;

Для первой строки LAG будет NULL (нет предыдущей). Для последней — LEAD будет NULL.

Day-over-day delta

SELECT
  date, visits,
  LAG(visits) OVER (ORDER BY date) AS prev_visits,
  visits - LAG(visits) OVER (ORDER BY date) AS delta
FROM daily_stats
ORDER BY date;

Для каждого дня — насколько посещения отличались от вчерашнего. Если delta > 0 — выросло, если < 0 — упало. Без LAG пришлось бы делать self-join по date - 1.

Пример с таблицей

daily_revenue:

date	revenue
2026-01-01	1000
2026-01-02	1200
2026-01-03	1100
2026-01-04	1500

SELECT
  date, revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS delta
FROM daily_revenue
ORDER BY date;

Результат:

date	revenue	prev_revenue	delta
2026-01-01	1000	NULL	NULL
2026-01-02	1200	1000	200
2026-01-03	1100	1200	-100
2026-01-04	1500	1100	400

Видно: 2 января выросло на 200, 3 января упало на 100, 4 января снова выросло на 400.

Первая строка не имеет «предыдущего» — поэтому prev_revenue и delta равны NULL. Если хочешь 0 вместо NULL — LAG(revenue, 1, 0):

Default для отсутствующей строки

SELECT
  date, visits,
  LAG(visits, 1, 0) OVER (ORDER BY date) AS prev_visits  -- 0 если нет предыдущей
FROM daily_stats;

Третий аргумент — что подставить, если строки нет. Удобно для первой строки в окне или для рекурсивных расчётов.

Time-to-next-event

SELECT
  user_id, event_at,
  LEAD(event_at) OVER (PARTITION BY user_id ORDER BY event_at) AS next_event_at,
  LEAD(event_at) OVER (PARTITION BY user_id ORDER BY event_at) - event_at AS gap
FROM events;

Каждый ивент знает, через сколько случится следующий для того же юзера. Полезно для расчёта «средний промежуток между визитами», «через сколько юзер возвращается».

PARTITION BY user_id — критично. Без него LEAD смешает события разных юзеров.

Изменение цены или статуса

SELECT
  product_id, date, price,
  LAG(price) OVER (PARTITION BY product_id ORDER BY date) AS prev_price,
  CASE
    WHEN price > LAG(price) OVER (PARTITION BY product_id ORDER BY date) THEN 'up'
    WHEN price < LAG(price) OVER (PARTITION BY product_id ORDER BY date) THEN 'down'
    ELSE 'same'
  END AS direction
FROM price_history;

Каждой записи о цене присвоено направление: цена выросла, упала или осталась.

Offset > 1 — N строк назад

-- Ровно неделю назад
SELECT
  date, visits,
  LAG(visits, 7, 0) OVER (ORDER BY date) AS visits_week_ago
FROM daily_stats;

LAG(visits, 7, 0) — значение 7 строк назад. Удобно для week-over-week.

PARTITION BY и границы

LAG/LEAD не пересекают границы партиции. У первой строки каждой партиции LAG = NULL, у последней LEAD = NULL.

-- Каждый клиент — своя партиция, у первого заказа prev_amount = NULL
SELECT
  customer_id, id, amount,
  LAG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY created_at
  ) AS prev_amount_same_customer
FROM orders;

Это правильное поведение: «предыдущий заказ другого клиента» — бессмысленное сравнение.

FIRST_VALUE и LAST_VALUE — родственники

SELECT
  customer_id, id, amount, created_at,
  FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY created_at) AS first_amount,
  LAST_VALUE(amount) OVER (
    PARTITION BY customer_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_amount
FROM orders;

Внимание: LAST_VALUE без явного frame clause даёт «текущую строку», не «реально последнюю в группе». Это потому что default frame — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Чтобы получить настоящий last — ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Это известный сюрприз.

Частые ошибки новичков

1. LAG без ORDER BY в OVER. Без сортировки понятие «предыдущей строки» теряет смысл. Postgres не упадёт, но результат недетерминированный. Всегда указывай ORDER BY.

2. PARTITION BY забыт. Хочешь «предыдущий заказ того же клиента» — нужен PARTITION BY customer_id. Без него LAG смешает разных клиентов и даст бессмысленный «предыдущий».

3. Default ≠ NULL для дат. LAG(date_col) без default возвращает NULL для первой строки. Если хочешь конкретный default — пропиши явно: LAG(date_col, 1, '1970-01-01'::date).

4. LAST_VALUE без явного frame. Возвращает «до текущей включительно», не «весь окно». См. выше.

5. LAG/LEAD в WHERE. Нельзя — окно вычисляется после WHERE. Оборачивай в подзапрос:

WITH t AS (
  SELECT *, LAG(amount) OVER (ORDER BY created_at) AS prev FROM orders
)
SELECT * FROM t WHERE prev IS NOT NULL;

6. Производительность. LAG/LEAD требуют сортировки окна. На таблицах 100M+ строк это полная сортировка. Если есть индекс на (partition_cols, order_col) — Postgres может избежать сортировки.

Мини-резюме

• LAG(col) — значение предыдущей строки в окне.
• LEAD(col) — значение следующей строки в окне.
• Третий аргумент — default, если соседа нет (по умолчанию NULL).
• Обязательно ORDER BY внутри OVER. Обычно нужен PARTITION BY для разделения по группам (юзеры, продукты, регионы).
• Не пересекают границы партиции — у крайних строк LAG/LEAD = NULL.
• LAST_VALUE без явного ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING даёт «до текущей», не последнюю.
• Главные применения: day-over-day delta, time-to-next-event, изменение состояния.