[지표] 이동평균

[Input: sakila.payment]

[Problem]

• 매출 일자를 기준으로 월별 누적 매출과 과거 7일간 매출의 이동평균을 구하시오.

[Output]

---

[Answer]

with base as (
	select date(payment_date) as dt
		  ,sum(amount) as daily_amount
	from payment
	where 1=1
	and date_format(payment_date, "%Y%m") in ('200505', '200506')
	group by date(payment_date)
)
select dt
	  ,daily_amount
	  ,sum(daily_amount) over (partition by month(dt) order by dt asc rows unbounded preceding) as accu_daily_amount
      ,case when count(1) over (partition by month(dt) order by dt asc rows between 6 preceding and current row) >= 7 
			then avg(daily_amount) over (partition by month(dt) order by dt asc rows between 6 preceding and current row) 
            else null end as seven_days_moving_avg
from base
order by dt asc;

[누적]

• sum(monthly_amount)
  • 합계를 구하겠다.
• over (partition by month(t.dt) order by t.dt asc
  • 월별로
• rows unbounded preceding)
  • 제한 없이

[이동평균]

• sum(monthly_amount)
  • 합계를 구하겠다.
• over (partition by month(t.dt) order by t.dt asc
  • 월별로
• rows between 6 preceding and current row)
  • 이전 6개행부터 현재 행까지 데이터에 한해서