SQL: Реляционные базы данных on Telegram: “😵‍💫 Как посчитать дельту к предыдущему дню в S…”

😵‍💫 Как посчитать дельту к предыдущему дню в SQL

Недавно писал важный скрипт на работе, где необходимо было считать дельты внутри колонок (сегодня минус вчера). Подумал, что это очень полезное знание и решил передать его вам, моим подписчикам. Собственно вычисление между текущим и предыдущим значением внутри колонки мы и рассмотрим.

SQL-инструмент для расчёта такой дельты — оконная функция LAG()

💻 Общий синтаксис:

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

➖ LAG(column) возвращает значение из предыдущей строки.
➖ PARTITION BY — разбивка по группам (например, по пользователям или магазинам).
➖ ORDER BY — важный момент: он определяет, по какому порядку "сравнивать".

💻 Пример: считаем дельту выручки по дням.
Допустим, у нас есть таблица sales:

sale_date   amount
2024-06-01  1000
2024-06-02  1200
2024-06-03  800

Запрос:

select
sale_date,
  amount,
  amount - LAG(amount) OVER (ORDER BY sale_date) AS delta
FROM sales;

Результат:

sale_date   amount  delta
2024-06-01  1000    NULL
2024-06-02  1200    200
2024-06-03  800    -400

Что происходит в запросе?
➖ LAG(amount) OVER (ORDER BY sale_date) берёт значение за предыдущий день.
➖ amount - LAG(...) — рассчитывает дельту
➖ В первый день delta = NULL, ибо в первом ряду нет предыдущего значения

💻 Пример с группировкой по магазину.
Если у нас несколько магазинов в таблице store_sales:

store_id  sale_date    amount
1         2024-06-01   1000
1         2024-06-02   1200
2         2024-06-01   700
2         2024-06-02   900

Запрос:

select
store_id,
  sale_date,
  amount,
  amount - LAG(amount) OVER (PARTITION BY store_id ORDER BY sale_date) AS delta
FROM store_sales;

➖ PARTITION BY store_id — Разбивает данные на группы по магазину. То есть, LAG() сравнивает значения только внутри одного store_id.
➖ ORDER BY sale_date — Определяет порядок, в котором будет вычисляться «предыдущее значение» — по дате продаж.
➖ LAG(amount) — Возвращает значение amount из предыдущей строки (внутри своей группы и в порядке дат).
➖ amount - LAG(amount) — Вычисляет дельту: текущий объём продаж минус предыдущий.

Что получится в результате:

store_id  sale_date   amount  delta
1         2024-06-01  1000    NULL
1         2024-06-02  1200    200
2         2024-06-01  700     NULL
2         2024-06-02  900     200

Доп.ситуация:
Если значения amount могут быть NULL, то delta будет NULL даже при корректном LAG(). Можно использовать COALESCE():

amount - COALESCE(LAG(amount) OVER (...), 0) AS delta

❗️Частые ошибки:
➖ Нет ORDER BY в OVER — дельта будет некорректной или всегда NULL
➖ Если даты перепутаны или дубликаты — ORDER BY может вести себя нестабильно. Лучше явно указать второй ключ (при этом в больших таблицах обязательно, читай принудительно, надо прописывать сорировку по нескольким ключевым столбцам иначе отработка запроса может давать нестабильные результаты, у вас будут буквально разные цифры при каждом перезапуске)
➖ Нет PARTITION BY, когда нужно сравнивать внутри группы, без оператора сравнение будет идти "вперемешку", не по логике.
➖ Сравнение разных типов данных — приводит к ошибкам или некорректным значениям.
➖ NULL-значения — дельта может стать NULL, если предыдущее значение отсутствует.

💻 Советы:
➖ Если нет LAG() в вашей СУБД — можно сделать JOIN таблицы самой на себя, но это медленнее.
➖ Дельту можно не только считать, но и фильтровать по ней (например: WHERE delta > 100).
➖ Вместо LAG() можно использовать LEAD(), чтобы сравнить с будущим значением.

#LAG #SQL

📱 Подписаться на канал
📱Мы в VK
💻 Курс автора по SQL DDL