LAG: значение предыдущей строки

Задача: рост транзакций по счёту

Аналитическая задача: «Насколько изменилась сумма транзакции по сравнению с предыдущей на том же счёте?»

Это невозможно сделать простым GROUP BY или JOIN. Нужно «заглянуть» в предыдущую строку того же раздела.

Синтаксис LAG

sql

LAG(колонка, смещение, значение_по_умолчанию)
    OVER (PARTITION BY ... ORDER BY ...)

колонка — значение какой колонки брать из предыдущей строки
смещение — на сколько строк назад (по умолчанию 1)
значение_по_умолчанию — что вернуть для первой строки (когда предыдущей нет)

Простой пример

sql

SELECT id, account_id, created_at, amount,
       LAG(amount) OVER (
           PARTITION BY account_id
           ORDER BY created_at
       ) AS prev_amount
FROM transactions
WHERE account_id = 3
ORDER BY created_at;

код

 id │ account_id │ created_at │ amount │ prev_amount
────┼────────────┼────────────┼────────┼─────────────
  8 │     3      │ 2024-01-08 │  4 200 │    NULL     ← первая строка, нет предыдущей
 15 │     3      │ 2024-01-25 │  9 800 │    4 200
 23 │     3      │ 2024-02-12 │  6 500 │    9 800
 35 │     3      │ 2024-03-05 │  3 100 │    6 500
 48 │     3      │ 2024-03-28 │ 11 200 │    3 100
 61 │     3      │ 2024-04-10 │  7 600 │   11 200

Вычисляем изменение

sql

SELECT id, account_id, created_at, amount,
       LAG(amount) OVER (PARTITION BY account_id ORDER BY created_at) AS prev_amount,
       amount - LAG(amount) OVER (PARTITION BY account_id ORDER BY created_at) AS delta
FROM transactions
WHERE account_id = 3
ORDER BY created_at;

код

 id │ amount │ prev_amount │ delta
────┼────────┼─────────────┼───────
  8 │  4 200 │    NULL     │  NULL
 15 │  9 800 │    4 200    │ +5 600
 23 │  6 500 │    9 800    │ -3 300
 35 │  3 100 │    6 500    │ -3 400
 48 │ 11 200 │    3 100    │ +8 100
 61 │  7 600 │   11 200    │ -3 600

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

Чтобы первая строка не давала NULL:

sql

LAG(amount, 1, 0) OVER (PARTITION BY account_id ORDER BY created_at)

Для первой транзакции вернёт 0 вместо NULL.

Частые ошибки

Нет ORDER BY в OVER — «предыдущая строка» теряет смысл без порядка. Всегда указывай ORDER BY в LAG.
Смешивают ORDER BY в OVER и в SELECT — это разные конструкции. ORDER BY внутри OVER только для окна.

Запомни

LAG(col) OVER (PARTITION BY g ORDER BY t) = значение col из предыдущей строки в той же группе g, отсортированной по t. Используй для вычисления дельты (прироста/убыли).