[지표] 이동평균 매출집계

• purchase_log
  

-- 날짜별 매출과 7일 이동평균 집계, 7일 이동평균 집계(strict)
select dt
	  ,sum(purchase_amount)
      ,avg(sum(purchase_amount)) over (order by dt rows between 6 preceding and current row) as moving_avg
      ,case when count(dt) over (order by dt rows between 6 preceding and current row) >= 7
		    then avg(sum(purchase_amount)) over (order by dt rows between 6 preceding and current row)
	   end as moving_avg_strict
from purchase_log
group by dt;

-- 날짜별 매출과 당연도 누계 매출 집계
select dt
	  ,purchase_amount
      ,sum(sum(purchase_amount)) over (partition by date_format(dt, '%Y') order by dt rows unbounded preceding) as monthly_amount
from purchase_log
group by dt;