SQL-рецепт: сравнение с соседями

Это заметка из серии «SQL-рецепты», в которой я описываю решения распространенных задач анализа данных в SQL.

Предположим, мы хотим сравнить каждую запись с соседними, ориентируясь на значение конкретного столбца:

• Сравнить продажи за месяц с предыдущим месяцем (MoM-изменения) или с тем же месяцем в прошлом году (YoY-изменения).
• Сравнить финансовые результаты за период с аналогичным периодом в прошлом году (LFL-анализ).
• Наблюдать изменения в цене акций день ко дню, чтобы понимать тренды рынка.
• Рассчитать рост трафика в зависимости от дня недели, чтобы запланировать увеличение мощностей.

Решение — использовать функцию lag() поверх SQL-окна, упорядоченного по целевым столбцам.

Пример

Сравним расходы компании (таблица expenses) от месяца к месяцу в абсолютном выражении:

select
  year, month,
  expense,
  expense - lag(expense) over w as diff
from expenses
window w as (order by year, month)
order by year, month;

lag(value, offset) возвращает значение value из строки, отстоящей на offset строк назад от текущей. По умолчанию offset равно 1, указывать его не обязательно.

Теперь посчитаем разницу каждого месяца с предыдущим в процентах:

select
  year, month, expense,
  round(
    (expense - lag(expense) over w)*100.0 / lag(expense) over w
  ) as "diff %"
from expenses
window w as (order by year, month)
order by year, month;

Альтернативы

Предположим, мы хотим сравнить квартальные продажи (таблица sales) с предыдущим годом. Здесь пригодится параметр offset:

with data as (
  select
    year, quarter,
    lag(amount, 4) over w as prev,
    amount as current,
    round(amount*100.0 / lag(amount, 4) over w) as "increase %"
  from sales
  window w as (order by year, quarter)
)
select
  quarter,
  prev as y2019,
  current as y2020,
  "increase %"
from data
where year = 2020
order by quarter;

При сортировке по году и кварталу выражение lag(amount, 4) дает нам тот же квартал, но в прошлом году.

Еще есть функция lead(). Работает в точности как lag(), только смотрит вперед, а не назад.

Совместимость

Все основные СУБД поддерживают оконные функции lag() и lead(). Некоторые из них, такие как MS SQL и Oracle, не поддерживают инструкцию window. В этих случаях можно перенести определение окна в основную часть запроса:

select
  year, month, expense,
  expense - lag(expense) over (
    order by year, month
  ) as diff
from expenses
order by year, month;

Можно переписать запрос вообще без оконных функций:

select
  cur.year, cur.month, cur.expense,
  cur.expense - prev.expense as diff
from expenses cur
left join expenses prev on
  cur.year = prev.year and
  cur.month - 1 = prev.month
order by cur.year, cur.month;

Хотите узнать больше об оконных функциях? Записывайтесь на курс Оконные функции SQL.