🔒Открыть пост
и посмотреть медиа
и посмотреть медиа
Как быстро и просто рассчитать динамику в SQL
Предположим, имеется таблица с банковскими транзакциями, в которой нас интересуют лишь 2 поля:
▶️Дата транзакции
▶️Сумма транзакции
И стоит задача: нужно понять, как из месяца в месяц менялся общий оборот по всем счетам (в процентах).
Сделать это можно одним небольшим запросом, даже без использования СТЕ:
SELECT
LAST_DAY(date),
sum(amount),
LAG(sum(amount))
OVER (ORDER BY LAST_DAY(date)) AS prev,
(sum(amount) - LAG(sum(amount))
OVER (ORDER BY LAST_DAY(date)))
/ NULLIF(LAG(sum(amount))
OVER (ORDER BY LAST_DAY(date)), 0)
AS pct_change
FROM transactions
group by LAST_DAY(date)
Запрос выведет 4 столбца:
1️⃣последний день месяца (стандартная практика, подразумеваются данные за весь месяц)
2️⃣общую сумму транзакций за месяц
3️⃣общую сумму транзакций за предыдущий месяц
4️⃣процентное изменение общей суммы транзакций к предыдущему месяцу
🟠Работает это следующим образом. Фактически 1 и 2 столбцы - это все транзакции, просто агрегированные и сгруппированные по last_day (по месяцам).
Но к ним добавляются с помощью оконных функций LAG дополнительные поля 3 и 4. Столбец 3 (prev), который имеет вид
LAG(sum(amount))
OVER (ORDER BY LAST_DAY(date)) AS prev
рассчитывается просто как обычная функция LAG, только с оговоркой на группировку, поэтому в нем присутствует и SUM, и LAST_DAY. И фактически он является вспомогательным для расчета столбца 4. И если в расчете столбца 4 для наглядности убрать все синтаксические нагромождения, то получится это:
(sum(amount) - prev)
/ NULLIF (prev, 0)
AS pct_change
То есть типичный расчет динамики: из суммы текущего месяца вычитается сумма за прошлый месяц, и эта разница делится на сумму за прошлый месяц (с проверкой на деление на 0).