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

Ранжирующие функции из прошлого урока — 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 — это оконные функции, они лишь добавляют новый столбец.

Вычисление разницы продаж между периодами внутри категории через LAG

Стандартный паттерн анализа динамики — вычесть предыдущее значение из текущего прямо в SELECT:

CREATE TABLE monthly_sales (
    sale_id  INTEGER PRIMARY KEY,
    category TEXT,
    month    TEXT,
    amount   INTEGER
);

INSERT INTO monthly_sales VALUES
    (1, 'Книги',       '2024-01', 500),
    (2, 'Книги',       '2024-02', 700),
    (3, 'Книги',       '2024-03', 600),
    (4, 'Электроника', '2024-01', 1200),
    (5, 'Электроника', '2024-02', 900),
    (6, 'Электроника', '2024-03', 1100);

SELECT
    sale_id,
    category,
    month,
    amount,
    LAG(amount, 1, NULL) OVER (PARTITION BY category ORDER BY month) AS prev_amount,
    amount - LAG(amount, 1, NULL) OVER (PARTITION BY category ORDER BY month) AS diff
FROM monthly_sales
ORDER BY category, month;

Результат:

sale_id	category	month	amount	prev_amount	diff
1	Книги	2024-01	500	NULL	NULL
2	Книги	2024-02	700	500	200
3	Книги	2024-03	600	700	-100
4	Электроника	2024-01	1200	NULL	NULL
5	Электроника	2024-02	900	1200	-300
6	Электроника	2024-03	1100	900	200

Как читать столбец diff: положительное число — рост по сравнению с предыдущим месяцем, отрицательное — падение, NULL — первый месяц данной категории, предшественника нет.

Важная деталь: выражение amount - LAG(...) вычисляется уже после того, как оконная функция определила значение LAG. То есть движок сначала для каждой строки находит смещённое значение, а потом применяет арифметику. Именно поэтому нельзя написать amount - prev_amount напрямую в том же SELECT — псевдоним prev_amount ещё не существует в момент вычисления diff. Оба вызова LAG(...) идут отдельно, либо можно обернуть запрос в CTE:

WITH base AS (
    SELECT
        sale_id, category, month, amount,
        LAG(amount, 1, NULL) OVER (PARTITION BY category ORDER BY month) AS prev_amount
    FROM monthly_sales
)
SELECT *, amount - prev_amount AS diff
FROM base
ORDER BY category, month;

Этот вариант читается чище и избавляет от дублирования оконного выражения.

Ловушка: отсутствие PARTITION BY смешивает строки разных категорий

Самая распространённая ошибка с LAG — забыть PARTITION BY, когда данные содержат несколько смысловых групп.

Предположим, запрос написан так:

SELECT
    category,
    month,
    amount,
    LAG(amount, 1, NULL) OVER (ORDER BY category, month) AS prev_amount
FROM monthly_sales
ORDER BY category, month;

Без PARTITION BY category окно охватывает все шесть строк сразу. После сортировки по category, month они выстраиваются в порядке: Книги-01, Книги-02, Книги-03, Электроника-01, Электроника-02, Электроника-03. LAG для строки «Электроника-01» смотрит на строку непосредственно перед ней в этом глобальном порядке — то есть на «Книги-03» со значением 600.

Результат будет таким:

category	month	amount	prev_amount
Книги	2024-01	500	NULL
Книги	2024-02	700	500
Книги	2024-03	600	700
Электроника	2024-01	1200	600
Электроника	2024-02	900	1200
Электроника	2024-03	1100	900

Строка «Электроника-01» получила prev_amount = 600 — это последнее значение из категории «Книги». Никакой ошибки выполнения не будет: запрос отработает тихо и вернёт правдоподобно выглядящие числа. Обнаружить проблему можно только если знать, что ожидать NULL для первого периода каждой категории.

Исправление одно — добавить PARTITION BY category:

LAG(amount, 1, NULL) OVER (PARTITION BY category ORDER BY month)

Теперь для строки «Электроника-01» окно начинается заново внутри раздела «Электроника», предшественника нет, и LAG честно возвращает NULL.

Практическое правило: если в данных есть смысловые группы (категория, пользователь, регион) и вы хотите смотреть на «предыдущий» элемент внутри группы — PARTITION BY обязателен.