[지표] 누적매출집계

[Input: sakila.payment]

[Problem]

• 날짜 별 매출과 월별 누계 매출를 구하시오.

[Output]

---

[Answer]

select t.dt
	  ,t.monthly_amount
      ,sum(t.monthly_amount) over (partition by month(t.dt) order by t.dt asc rows ㅠbetween unbounded preceding and current row) as accu_monthly_amount
from (
	select date(payment_date) as dt
		  ,sum(amount) as monthly_amount
	from payment
	where date_format(payment_date, "%Y%m") in ('200505', '200506')
	group by date(payment_date)
) t;

• sum(monthly_amount)
  • 합계를 구하겠다.
• over (partition by month(t.dt) order by t.dt asc
  • 월별로
• rows unbounded preceding)
  • 첫 행부터 현재 행까지