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

LAG/LEAD заглядывают в соседние строки, а SUM/AVG OVER считают нарастающие и скользящие итоги.

LAG берёт значение из предыдущей строки окна, LEAD — из следующей; агрегаты с OVER считают по окну, не схлопывая строки.

LAG и LEAD: соседние строки

Чтобы сравнить строку с соседней (например, выручку месяца с предыдущим), раньше делали хитрый self-join. Оконные LAG/LEAD делают это в одну строку:

CREATE TABLE revenue (
    id    INTEGER PRIMARY KEY,
    month TEXT,
    total INTEGER
);
INSERT INTO revenue (month, total) VALUES
    ('Янв', 100), ('Фев', 150), ('Мар', 120), ('Апр', 200);

SELECT month, total,
       LAG(total)  OVER (ORDER BY id) AS prosh_mesyac,
       total - LAG(total) OVER (ORDER BY id) AS izmenenie
FROM revenue
ORDER BY id;

Вывод:

Янв|100||
Фев|150|100|50
Мар|120|150|-30
Апр|200|120|80

У января нет предыдущего месяца — LAG вернул NULL, и разница тоже NULL. Дальше видно изменение: Фев +50, Мар −30, Апр +80. LEAD работает зеркально — смотрит на следующую строку.

Нарастающий итог: SUM OVER

Агрегат с OVER (ORDER BY ...) накапливает сумму строка за строкой — это running total, очень частая задача:

CREATE TABLE revenue (id INTEGER PRIMARY KEY, month TEXT, total INTEGER);
INSERT INTO revenue (month, total) VALUES
    ('Янв', 100), ('Фев', 150), ('Мар', 120);

SELECT month, total,
       SUM(total) OVER (ORDER BY id) AS narastayushiy_itog
FROM revenue
ORDER BY id;

Вывод:

Янв|100|100
Фев|150|250
Мар|120|370

Накопление: 100, потом 100+150=250, потом 250+120=370. Окно ORDER BY id без явных границ берёт все строки от начала до текущей включительно.

Доля от общего: AVG/SUM по всему окну

Если в OVER () не указать ORDER BY, агрегат считается по всему разделу — удобно показать долю строки в общей сумме:

CREATE TABLE sales (id INTEGER PRIMARY KEY, product TEXT, amount INTEGER);
INSERT INTO sales (product, amount) VALUES
    ('A', 200), ('B', 300), ('C', 400);

SELECT product, amount,
       SUM(amount) OVER () AS vsego,
       ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS dolya_proc
FROM sales
ORDER BY product;

Вывод:

A|200|900|22.2
B|300|900|33.3
C|400|900|44.4

SUM(amount) OVER () даёт общий итог 900 в каждой строке, и мы тут же считаем долю: A ≈ 22,2%, B ≈ 33,3%, C ≈ 44,4%. Без оконной функции для этого понадобился бы отдельный подзапрос.

Итог

• LAG/LEAD читают соседние строки — идеально для сравнения с предыдущим/следующим.
• SUM(...) OVER (ORDER BY ...) — нарастающий итог; SUM(...) OVER () — итог по всему окну.
• Оконные агрегаты заменяют громоздкие self-join и подзапросы.