Доступ к соседним строкам: LAG и LEAD

LAG и LEAD позволяют заглянуть в предыдущую и следующую строку прямо в SELECT.

LAG(колонка) возвращает значение из предыдущей строки окна, LEAD(колонка) — из следующей. Это убирает необходимость в self-join для сравнения «строка с соседом».

Прирост выручки месяц к месяцу

Классическая задача: посчитать изменение относительно прошлого периода. Раньше для этого делали хитрый self-join, теперь — одна функция.

CREATE TABLE sales(month INTEGER, revenue INTEGER);
INSERT INTO sales VALUES (1,100),(2,130),(3,120),(4,160);

SELECT month, revenue,
       LAG(revenue)  OVER (ORDER BY month)            AS prev,
       revenue - LAG(revenue) OVER (ORDER BY month)   AS diff,
       LEAD(revenue) OVER (ORDER BY month)            AS next
FROM sales;

Вывод:

month  revenue  prev  diff  next
-----  -------  ----  ----  ----
1      100                  130
2      130      100   30    120
3      120      130   -10   160
4      160      120   40

Разбор

prev — выручка прошлого месяца. У первой строки её нет → NULL (в выводе пусто). Это нормально: до января «предыдущего» месяца не существует.
diff = текущая − предыдущая. У месяца 2 это 130−100 = 30 (рост), у месяца 3 → −10 (спад). У первой строки diff тоже NULL, потому что вычитание с NULL даёт NULL.
next — выручка следующего месяца. У последней строки NULL: «следующего» нет.

Значение по умолчанию вместо NULL

У LAG/LEAD есть необязательные аргументы: LAG(колонка, смещение, по_умолчанию). Например, LAG(revenue, 1, 0) вернёт 0 вместо NULL для самой первой строки, а LAG(revenue, 2) заглянет на две строки назад. Это удобно, чтобы не плодить COALESCE.

Где это нужно на практике

Прирост/падение метрики период к периоду (revenue, число пользователей).
Время между событиями: timestamp - LAG(timestamp) — длительность паузы между действиями пользователя.
Поиск «скачков»: строки, где revenue отличается от соседнего больше чем на порог.

Важно: ORDER BY внутри окна обязателен — без заданного порядка понятия «предыдущая строка» не существует.

Итог

LAG смотрит назад, LEAD — вперёд по порядку окна.
На краях окна нет соседа → результат NULL (можно задать значение по умолчанию третьим аргументом).
Главное применение — сравнение строки с соседней без self-join (приросты, паузы, скачки).