Z차트를 작성하는 SQL 쿼리

'데이터 분석을 위한 SQL 레시피'

 

z차트

• '월차매출', '매출누계', '이동년계'라는 3개의 지표로 구성되어, 계절 변동의 영향을 배제하고 트렌드를 분석하는 방법
  • '월차매출' : 매출 합계를 월별로 집계
  • '매출누계' : 해당 월의 매출에 이전월까지의 매출 누계를 합한 값
    • 매출 일정 : 직선
    • 매출 상승 : 오른쪽으로 갈수록 기울기가 커지는 곡선
    • 매출 하락 : 오른쪽으로 갈수록 기울기가 완만한 곡선
  • '이동년계' : 해당 월의 매출에 과거 11개월의 매출을 합한 것
• 일별 매출을 집계하는 테이블(daily_purchase)

-- 날짜별 매출 임시 테이블
with daily_purchase as (
	select dt
		-- 연, 월, 일 각각 추출
		, substring(dt, 1, 4) as year
		, substring(dt, 6, 2) as month
		, substring(dt, 9, 2) as day
		-- 이 날 매출
		, sum(purchase_amount) as purchase_amount
		-- 이 날 주문 수
		, count(order_id) as orders
	from purchase_log 
	group by dt
)

• 월별 구매 총액을 집계하는 임시테이블(monthly_amount)
  • daily_purchase에서 얻은 일별 구매 데이터를 기반으로 연도(year)와 월(month)로 그룹화하여 월별 총 구매액(amount)을 집계

-- 월별 매출 집계
, monthly_amount as (
	select year 
		, month
		, sum(purchase_amount) as amount
	from dailyy_purchase
	order by year, month
)

• 누적 총액 및 연간 평균 총액을 집계하는 임시테이블(calc_index)
  • 연도(year), 월(month), 월차매출(amount), 매출누계(agg_amount), 이동년계(year_avg_amount)
  • '매출누계'
    • CASE문을 사용하였고, 각각 월별마다 시행해야 하므로 OVER절을 사용하여 년도별로 정렬하고, ROWS UNBOUNDED PRECENDING)을 활용해 당월까지의 매출 합을 구함
  • '이동년계'
    • 당월 포함 1년 동안의 매출 누계를 구하기 위해 ROWS BETWEEN 11 PRECENDING AND CURRENT ROW) 를 사용

-- 누적 총액 및 연간 평균 총액을 집계
, calc_index as (
	select year 
		, month
		, amount
		-- 2015년의 누계 매출 집계
		, sum(case when year = '2015' then amount end)
			over(order by year, month rows unbounded preceding) as agg_amount
		-- 이동년계 : 당월부터 11개월 이전까지의 매출 합계
		, sum(amount)
			over(order by year, month rows between 11 preceding and current row) as year_avg_amount
	from monthly_purchase
	order by year, month
)

• 최종(Z 차트)

-- 2015년 매출에 대한 z차트 작성
select concat(year, '-', month) as year_month
	, amount
	, agg_amount
	, year_avg_amount
from calc_index
where year = '2015'
order by year_month;

• 계절 트렌드 영향을 제외하고, 매출의 성장 또는 쇠퇴를 다양한 각도에서 살펴볼 때는 Z차트를 사용하는 것이 좋다.