Нарастающий итог и разница соседних строк

Running total и разница между соседними строками — две частые «оконные» задачи.

Нарастающий итог и сравнение с соседней строкой решаются оконными функциями SUM OVER и LAG — без громоздких self-join.

Нарастающий итог (running total)

Классическая просьба: «покажи баланс после каждой операции». Это накопительная сумма по порядку дат:

CREATE TABLE ops (
    id     INTEGER PRIMARY KEY,
    day    TEXT,
    delta  INTEGER     -- приход (+) или расход (-)
);
INSERT INTO ops (day, delta) VALUES
    ('01', 100), ('02', -30), ('03', 50), ('04', -20);

SELECT day, delta,
       SUM(delta) OVER (ORDER BY id) AS balans
FROM ops
ORDER BY id;

Вывод:

01|100|100
02|-30|70
03|50|120
04|-20|100

Баланс накапливается: 100, потом 70, потом 120, потом 100. Каждая строка показывает состояние счёта на этот день. До оконных функций это считали коррелированным подзапросом — медленно и громоздко.

Running total по группам

Добавив PARTITION BY, считаем накопление отдельно для каждого счёта — итог сбрасывается на новой группе:

CREATE TABLE ops (id INTEGER PRIMARY KEY, account TEXT, delta INTEGER);
INSERT INTO ops (account, delta) VALUES
    ('A',100), ('A',-30), ('B',200), ('B',50), ('A',10);

SELECT account, delta,
       SUM(delta) OVER (PARTITION BY account ORDER BY id) AS balans
FROM ops
ORDER BY account, id;

Вывод:

A|100|100
A|-30|70
A|10|80
B|200|200
B|50|250

Счёт A: 100 → 70 → 80. Счёт B: 200 → 250. Накопление идёт независимо внутри каждого account.

Разница между соседними строками

«На сколько изменилась метрика по сравнению с предыдущим днём» — это LAG. Вычитаем из текущего значения предыдущее:

CREATE TABLE metrics (id INTEGER PRIMARY KEY, day TEXT, users INTEGER);
INSERT INTO metrics (day, users) VALUES
    ('Пн', 100), ('Вт', 130), ('Ср', 120), ('Чт', 160);

SELECT day, users,
       LAG(users) OVER (ORDER BY id)            AS vchera,
       users - LAG(users) OVER (ORDER BY id)    AS prirost
FROM metrics
ORDER BY id;

Вывод:

Пн|100||
Вт|130|100|30
Ср|120|130|-10
Чт|160|120|40

У понедельника предыдущего дня нет — vchera и prirost равны NULL. Дальше прирост: Вт +30, Ср −10, Чт +40. Один LAG заменяет целый self-join «таблица с собой по соседнему дню».

Итог

Running total — SUM(...) OVER (ORDER BY ...); по группам — добавьте PARTITION BY.
Разница с соседней строкой — значение - LAG(значение) OVER (ORDER BY ...).
Оба приёма заменяют коррелированные подзапросы и self-join — короче и быстрее.