Смещение: LAG и LEAD

Вычисление разницы продаж между периодами внутри категории через LAG

Стандартный паттерн анализа динамики — вычесть предыдущее значение из текущего прямо в SELECT:

CREATE TABLE monthly_sales (
    sale_id  INTEGER PRIMARY KEY,
    category TEXT,
    month    TEXT,
    amount   INTEGER
);

INSERT INTO monthly_sales VALUES
    (1, 'Книги',       '2024-01', 500),
    (2, 'Книги',       '2024-02', 700),
    (3, 'Книги',       '2024-03', 600),
    (4, 'Электроника', '2024-01', 1200),
    (5, 'Электроника', '2024-02', 900),
    (6, 'Электроника', '2024-03', 1100);

SELECT
    sale_id,
    category,
    month,
    amount,
    LAG(amount, 1, NULL) OVER (PARTITION BY category ORDER BY month) AS prev_amount,
    amount - LAG(amount, 1, NULL) OVER (PARTITION BY category ORDER BY month) AS diff
FROM monthly_sales
ORDER BY category, month;

Результат:

sale_id	category	month	amount	prev_amount	diff
1	Книги	2024-01	500	NULL	NULL
2	Книги	2024-02	700	500	200
3	Книги	2024-03	600	700	-100
4	Электроника	2024-01	1200	NULL	NULL
5	Электроника	2024-02	900	1200	-300
6	Электроника	2024-03	1100	900	200

Как читать столбец diff: положительное число — рост по сравнению с предыдущим месяцем, отрицательное — падение, NULL — первый месяц данной категории, предшественника нет.

Важная деталь: выражение amount - LAG(...) вычисляется уже после того, как оконная функция определила значение LAG. То есть движок сначала для каждой строки находит смещённое значение, а потом применяет арифметику. Именно поэтому нельзя написать amount - prev_amount напрямую в том же SELECT — псевдоним prev_amount ещё не существует в момент вычисления diff. Оба вызова LAG(...) идут отдельно, либо можно обернуть запрос в CTE:

WITH base AS (
    SELECT
        sale_id, category, month, amount,
        LAG(amount, 1, NULL) OVER (PARTITION BY category ORDER BY month) AS prev_amount
    FROM monthly_sales
)
SELECT *, amount - prev_amount AS diff
FROM base
ORDER BY category, month;

Этот вариант читается чище и избавляет от дублирования оконного выражения.