[PostgreSQL] 매출을 파악하기 위한 데이터 추출2 (종합 쿼리)

▶︎ 연/월별로 다음 데이터를 뽑는 종합 쿼리

• 판매 횟수
• 평균 구매액
• 매출액
• 누계 매출액
• 작년 (동월) 매출액
• 작년 (동월) 비율

with
daily_purchase as (
	select dt
		, substring(dt, 1, 4) as year
		, substring(dt, 6, 2) as month
		, substring(dt, 9, 2) as date
		, sum(purchase_amount) as purchase_amount
		, count(order_id) as orders
	from purchase_log
	group by 1	
),
monthly_purchase as(
	select year
		, month 
		, sum(orders) as orders
		, avg(purchase_amount) as avg_amount
		, sum(purchase_amount) as monthly_amount
	from daily_purchase
	group by 1, 2
)
select concat(year, '-', month) as year_month
	, orders
	, avg_amount
	, monthly_amount
	--연도별로 월매출 누적 합계 
	, sum(monthly_amount) over (partition by year order by month rows unbounded preceding) as agg_amount
	-- 12개월 전의 매출 합계
	, lag(monthly_amount, 12) over (order by year, month) as last_year
	-- 12개월 전의 매출과 비교해서 비율 구하기
	, 100.0 * monthly_amount / lag(monthly_amount, 12) over (order by year, month) as rate
from monthly_purchase
order by year_month
;