10_02. [mysql] moving average & rolling sum

 

mysql을 이용한 moving average 와 rolling sum을 구해보자

위와 같은 형태의 데이터를 이용하여, amount에 대한 moving average window=7 을 구해보자!

 

 

select visited_on , amount, average_amount
from (
select a.visited_on
     ,row_number()over(order by a.visited_on) as rn
     ,sum(b.amount) as amount
     ,round(avg(b.amount),2) as average_amount
from (
      select visited_on
            ,sum(amount) as amount
      from customer
      group by visited_on) a
left join (select visited_on
            ,sum(amount) as amount
      from customer
      group by visited_on) b 
on datediff(a.visited_on , b.visited_on) between 0 and 6
group by a.visited_on
) b1
where rn>=7
;

select visited_on
     ,sum(amount)over(order by visited_on rows between 7 preceding and current row) as amount
     ,round(avg(amount)over(order by visited_on rows between 7 preceding and current row),2) as average_amount
     
from customer

;

 

 

 

 

 

https://joshuaotwell.com/rolling-sum-and-average-with-window-functions-in-mysql/