Смещение: LAG и LEAD

LAG возвращает предыдущее значение

LAG смотрит на предыдущую строку в порядке, заданном внутри OVER. Воспроизводимый пример для SQLite:

WITH sales(month, amount) AS (VALUES (1, 100), (2, 140), (3, 120))
SELECT month, amount,
       LAG(amount) OVER (ORDER BY month) AS previous,
       amount - LAG(amount) OVER (ORDER BY month) AS delta
FROM sales
ORDER BY month;

В previous получим NULL, 100, 140; в delta — NULL, 40, -20. У первой строки нет предшественника. LEAD смотрит вперёд, а PARTITION BY позволяет считать соседей отдельно для каждого магазина или категории.

Ранжирующие функции из прошлого урока — ROW_NUMBER, RANK, DENSE_RANK — присваивали каждой строке номер, опираясь на её позицию в окне. LAG и LEAD делают следующий шаг: вместо номера они возвращают само значение из соседней строки. Синтаксис OVER (PARTITION BY … ORDER BY …) тот же, но смысл принципиально другой — теперь можно напрямую сравнить текущую строку с предыдущей или следующей.

Синтаксис LAG и LEAD: аргументы column, n, default и поведение на границах раздела

Полная форма вызова:

LAG(column, n, default)  OVER (PARTITION BY ... ORDER BY ...)
LEAD(column, n, default) OVER (PARTITION BY ... ORDER BY ...)

Три аргумента делают ровно то, что написано на этикетке.

• column — столбец, значение которого нужно взять из смещённой строки.
• n — на сколько строк сместиться. По умолчанию 1, то есть LAG(amount) и LAG(amount, 1) эквивалентны.
• default — что вернуть, если смещённой строки не существует.

Если аргумент не передан, используется NULL.

Где именно «не существует»? LAG смотрит назад: для первой строки раздела предшественника нет. LEAD смотрит вперёд: для последней строки раздела преемника нет. При n = 2 — для двух первых (или двух последних) строк.

Покажем разницу между default = 0 и отсутствующим default на коротком примере. Предположим, в разделе три строки с суммами 500, 700, 600.

-- Без default: первая строка получает NULL
SELECT amount,
       LAG(amount, 1)    OVER (ORDER BY month) AS lag_null,
       LAG(amount, 1, 0) OVER (ORDER BY month) AS lag_zero
FROM monthly_sales
WHERE category = 'Книги'
ORDER BY month;

Результат:

amount	lag_null	lag_zero
500	NULL	0
700	500	500
600	700	700

Видно: lag_null на первой строке — NULL, lag_zero — 0. Для второй и третьей строк оба столбца дают одинаковый результат, потому что предшественник существует.

Какой вариант выбрать на практике? Для межпериодной разницы обычно оставляйте NULL: у первой строки нет предыдущего наблюдения, и разность не определена. Передавайте default = 0 только когда предметная область прямо трактует отсутствие предшественника как нулевое значение. Это меняет смысл результата: 500 - 0 = 500 выглядит как рост от нуля, а 500 - NULL = NULL честно сообщает, что сравнивать не с чем.

LEAD работает симметрично: LEAD(amount, 1, NULL) вернёт сумму следующего месяца, а для последней строки раздела — NULL. Количество строк в результате не меняется ни у LAG, ни у LEAD — это оконные функции, они лишь добавляют новый столбец.