Оконные функции: LAG, LEAD и SUM OVER

В прошлом уроке оконные функции ставили строкам места в рейтинге. Здесь вторая половина темы: функции, которые заглядывают в соседнюю строку, и агрегаты, которые считаются по окну, не схлопывая строки. На собеседовании это вопросы вида «как изменилась сумма по сравнению с прошлым заказом» и «какую долю от итога клиента составляет каждый заказ». Примеры на учебной базе блока, в основном на трёх заказах Ольги.

Как спрашивают

Для каждого заказа клиента покажи, на сколько он больше или меньше предыдущего. И рядом с каждой строкой выведи общую сумму клиента, не теряя строк.

Короткий ответ

LAG(x) берёт значение x из предыдущей строки окна, LEAD(x) из следующей. Порядок строк задаёт ORDER BY внутри OVER. У первой строки предыдущей нет, поэтому LAG вернёт NULL, если не указать значение по умолчанию третьим аргументом. Обычный агрегат с OVER, например SUM(amount) OVER (PARTITION BY customer_id), считает сумму по группе и пишет её в каждую строку группы, без GROUP BY.

Разбор: дневник веса

Человек каждое утро записывает вес и рядом пишет, на сколько он изменился со вчера. Чтобы заполнить вторую колонку, он смотрит на строку выше. LAG делает то же: «возьми значение из строки выше». LEAD смотрит на строку ниже, например «когда следующее взвешивание».

LAG и LEAD не меняют число строк: каждая строка остаётся, к ней добавляется значение соседа.

Разница с предыдущим заказом

SELECT id, created_at, amount,
       LAG(amount) OVER (ORDER BY created_at)          AS prev_amount,
       amount - LAG(amount) OVER (ORDER BY created_at) AS diff
FROM orders
WHERE customer_id = 1
ORDER BY created_at;

id | created_at | amount | prev_amount | diff
---+------------+--------+-------------+------
 1 | 2026-01-05 |   5000 |        NULL |  NULL
 2 | 2026-01-20 |   3000 |        5000 | -2000
 4 | 2026-02-15 |   3000 |        3000 |     0

У первого заказа предыдущего нет, поэтому prev_amount и diff пустые. Если нужен ноль, задай умолчание: LAG(amount, 1, 0). Второй аргумент это смещение, на сколько строк назад смотреть.

Дней до следующего заказа

SELECT id, created_at,
       LEAD(created_at) OVER (ORDER BY created_at)              AS next_order,
       LEAD(created_at) OVER (ORDER BY created_at) - created_at AS days_to_next
FROM orders
WHERE customer_id = 1
ORDER BY created_at;

id | created_at | next_order | days_to_next
---+------------+------------+-------------
 1 | 2026-01-05 | 2026-01-20 |           15
 2 | 2026-01-20 | 2026-02-15 |           26
 4 | 2026-02-15 |       NULL |         NULL

В PostgreSQL разность двух дат это целое число дней.

Для всех клиентов сразу

Выше мы отфильтровали одну Ольгу. Если убрать WHERE, окно должно сбрасываться на каждом клиенте, иначе LAG у первого заказа Павла возьмёт последний заказ Ольги. Для этого нужен PARTITION BY customer_id, как в ранжировании.