rows between

Когда я в декабре решала 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)