4장 매출을 파악하기 위한 데이터 추출

9강 시계열 기반으로 데이터 집계하기

2. 이동평균을 사용한 날짜별 추이 보기

코드 9-2

SELECT dt
	 , SUM(purchase_amount) AS total_amount
	 -- 최근 최대 7일 동안의 평균
	 , AVG(SUM(purchase_amount)) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS seven_day_avg
	 -- 최근 7일 동안의 평균 확실하게 계산
	 , CASE
		 WHEN 7 = COUNT(*) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
		 THEN AVG(SUM(purchase_amount)) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 
	   END AS seven_day_avg_strict
FROM purchase_log
GROUP BY dt
ORDER BY dt

dt	total_amount	seven_day_avg	seven_day_avg_strict
2014-01-01	24516	24516.000000000000
2014-01-02	36049	30282.500000000000
2014-01-03	53029	37864.666666666667
2014-01-04	29299	35723.250000000000
2014-01-05	48256	38229.800000000000
2014-01-06	29440	36764.833333333333
2014-01-07	47679	38324.000000000000	38324.000000000000
2014-01-08	19760	37644.571428571429	37644.571428571429
2014-01-09	22944	35772.428571428571	35772.428571428571
2014-01-10	27923	32185.857142857143	32185.857142857143

날짜별로 추이와 이동평균을 함께 표현해 리포트를 만드는 것이 좋다.

3. 당월 매출 누계 구하기

코드 9-5

-- 날짜별 매출 집계 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 date
	 	 , SUM(purchase_amount) AS purchase_amount
	FROM purchase_log
	GROUP BY dt
	ORDER BY dt
)
-- 당월 누계 매출 집계
SELECT dt
	 , CONCAT(year, '-', month) AS year_month
	 , purchase_amount 
	 , SUM(purchase_amount) OVER (PARTITION BY year, month ORDER BY dt ROWS UNBOUNDED PRECEDING) AS agg_aumount
FROM daily_purchase
ORDER BY dt

dt	year_month	purchase_amount	agg_aumount
2014-01-01	2014-01	24516	24516
2014-01-02	2014-01	36049	60565
2014-01-03	2014-01	53029	113594
2014-01-04	2014-01	29299	142893
2014-01-05	2014-01	48256	191149
2014-01-06	2014-01	29440	220589
2014-01-07	2014-01	47679	268268
2014-01-08	2014-01	19760	288028
2014-01-09	2014-01	22944	310972
2014-01-10	2014-01	27923	338895

4. 월별 매출의 작대비(작년대비 비율) 구하기

코드 9-6

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
	FROM purchase_log
	GROUP BY dt
	ORDER BY dt
)
SELECT month
	 , SUM(CASE year WHEN '2014' THEN purchase_amount END) AS amount_2014
	 , SUM(CASE year WHEN '2015' THEN purchase_amount END) AS amount_2015
	 , 100.0 * SUM(CASE year WHEN '2015' THEN purchase_amount END) / SUM(CASE year WHEN '2014' THEN purchase_amount END)
	   AS rate
FROM daily_purchase
GROUP BY month
ORDER BY month

매출이 증감이 있어도 계절의 영향인지, 이벤트 등으로 인한 것인지 판단할 수 없다.

5. Z차트로 업적의 추이 확인하기

• 계절에 따라 매출이 변동하는 경우가 있다.
  Z차트는 월차매출, 매출누계, 이동년계 3개의 지표로 구성되어 계절 변동의 영향을 배제하고 트렌드를 분석하는 방법이다.

Z차트를 작성하기 위한 지표 집계하기

코드 9-7

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 day_amount
	FROM purchase_log
	GROUP BY dt
	ORDER BY dt
)
-- 월별 매출 집계
, monthly_purchase AS (
	SELECT year
		 , month
		 , SUM(day_amount) AS month_amonut
	FROM daily_purchase
	GROUP BY year, month
)
, calc_index AS (
	SELECT year
		 , month
		 , month_amonut
		 -- 2015년 누계 매출 집계
		 , SUM(CASE WHEN year = '2015' THEN month_amonut END) OVER (ORDER BY year, month ROWS UNBOUNDED PRECEDING) AS agg_amount
		 -- 당월부터 11개월 이전까지 매출 합계(이동년계) 집계
		 , SUM(month_amonut) OVER (ORDER BY year, month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS year_agg_amount
	FROM monthly_purchase
	ORDER BY year, month
)
-- 2015년 데이터만 압축
SELECT CONCAT(year, '-', month) AS year_month
       -- 월차매출
	 , month_amonut
       -- 매출누계
	 , agg_amount
       -- 이동년계
	 , year_agg_amount
FROM calc_index
WHERE year = '2015'
ORDER BY year_month

year_month	month_amonut	agg_amount	year_agg_amount
2015-01	22111	22111	160796
2015-02	11965	34076	144292
2015-03	20215	54291	145608
2015-04	11792	66083	145006
2015-05	18087	84170	160811
2015-06	18859	103029	169490
2015-07	14919	117948	180382
2015-08	12906	130854	187045
2015-09	5696	136550	188909
2015-10	13398	149948	195591
2015-11	6213	156161	185360
2015-12	26024	182185	182185

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

6. 매출을 파악할 때 중요 포인트

• 매출 집계만으로는 매출의 상승/하락에 관한 이유를 알 수 없다.
  매출이라는 결과의 원인이라고 할 수 있는 구매 횟수, 구매 단가 등의 주변 데이터를 고려해야 '왜'라는 이유를 알 수 있다.

코드 9-8

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 day_amount
	FROM purchase_log
	GROUP BY dt
	ORDER BY dt
)
, monthly_purchase AS (
	SELECT year
		 , MONTH
--		 , SUM(orders) AS orders
-- 실습 데이터에 orders 컬럼 없음 ???
		 , AVG(day_amount) AS avg_amount
		 , SUM(day_amount) AS monthly
	FROM daily_purchase
	GROUP BY year, month
)
SELECT CONCAT(year, '-', month) AS year_month
--     , orders
     , avg_amount
     , monthly
     -- 연도별 누계 매출 집계
     , SUM(monthly) OVER (PARTITION BY year ORDER BY month ROWS UNBOUNDED PRECEDING) AS agg_amount
	 -- 12개월 전의 매출 구하기
     , LAG(monthly, 12) OVER (ORDER BY year, month) AS last_year
     -- 12개월 전의 매출과 비교해서 비율 구하기
     , 100.0 * monthly / LAG(monthly, 12) OVER (ORDER BY year, month) AS rate
FROM monthly_purchase
ORDER BY year_month