SQLite на практике: “SQL-рецепт: Сравнение с соседями Предположим, мы хотим сравнить каждую…”

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

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

Решение — использовать функцию lag() поверх SQL-окна.

Пример

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

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

year  mon exp  dif
2020  1    82  
2020  2    75   -7
2020  3   104   29
2020  4    94  -10
2020  5    99    5
2020  6   105    6
2020  7    95  -10
2020  8   110   15
2020  9   104   -6

lag(value, offset) возвращает значение value из строки, отстоящей на offset строк назад от текущей.

По умолчанию offset равно 1, указывать его не обязательно.

Нюансы

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

— Если СУБД не поддерживает инструкцию window, можно встроить определение окна: lag(expense) over (order by ...)

→ Оконные функции SQL