Когда я в декабре решала Advent of SQL, на 11 день было интересное задание - найти скользящее среднее по текущей строке с двумя предыдущими. Еще похожая штука применяется, когда нужно найти кумулятивную сумму
🚃 Выход - оконка! (но только в целях SQL)
Мы можем написать так:
avg(amount) over(order by column)
Но под капотом это так:
avg(amount) over(order by column rows between unbounded preceding and current row)
т.е. мы стартуем с первой строки и берем все до текущей
Всего есть несколько способов тюнинга:
--от первой до текущей
rows between unbounded preceding and current row
--от текущей до последней
rows between current row and unbounded following
--с конкретикой - сколько до и сколько после
--от предыдущей до текущей (2 строки)
rows between 1 preceding and current row
--от текущей до 1 следующей (2 строки)
rows between current row and 1 following
--от предыдущей до следующей (3 строки, еще текущая есть)
rows between 1 preceding and 1 following
В контексте задачи со скользящим средним просто нужно взять не 1, а 2 строки:
avg(amount) over(order by rows between 2 preceding and current row)
А для кумулятивной суммы нужно просто сделать так, потому что по дефолту она и будет кумулятивной✨
sum(amount) over(order by column)