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

10강 다면적인 축을 사용해 데이터 집약하기

카테고리별 매출과 소계 계산하기

코드 10-1

WITH sub_category_amount AS (
	-- 소 카테고리 매출 집계
	SELECT category
		 , sub_category
		 , SUM(price) AS amount
	FROM purchase_detail_log
	GROUP BY category, sub_category
)
, category_amount AS (
	-- 대 카테고리 매출 집계
	SELECT category
		 , 'all' AS sub_category
		 , SUM(price) AS amount
	FROM purchase_detail_log
	GROUP BY category
)
, total_amount AS (
	-- 전체 매출 집계
	SELECT 'all' AS category
		 , 'all' AS sub_category
		 , SUM(price) AS amount
	FROM purchase_detail_log
)
SELECT category, sub_category, amount
FROM sub_category_amount
UNION ALL
SELECT category, sub_category, amount
FROM category_amount
UNION ALL
SELECT category, sub_category, amount
FROM total_amount

그런데 UNION ALL을 사용한 결합방법은 성능이 좋지 않다.
ROLLUP을 사용해 쉽고 성능 좋은 쿼리 만들 수 있다.

코드 10-2

SELECT COALESCE(category, 'all') AS category
	 , COALESCE(sub_category, 'all') AS sub_category
	 , SUM(price) AS amount
FROM purchase_detail_log
GROUP BY ROLLUP(category, sub_category)

category	sub_category	amount
all	all	861100
ladys_fashion	bag	127900
food	fish	32000
food	meats	48700
dvd	documentary	32800
mens_fashion	jacket	116300
book	business	53500
outdoor	camp	28600
game	accessories	26000
ladys_fashion	jacket	369500
cd	classic	25800
game	all	26000
book	all	53500
ladys_fashion	all	497400
outdoor	all	28600
mens_fashion	all	116300
cd	all	25800
dvd	all	32800
food	all	80700

ROLLUP : GROUP BY 절에 사용되는 집계 함수 (링크)
COALESCE : null이 아닌 첫 번째 인수를 반환 (링크)

ABC 분석으로 잘 팔리는 상품 판별하기

코드 10-3

WITH monthly_sales AS (
	 SELECT category
	 		-- 항목별 매출 계산
	 	  , SUM(price) AS amount
	 FROM purchase_detail_log
	 -- 대상 1개월 동안의 로그를 조건으로 걸기
	 -- WHERE dt BETWEEN '2015-12-01' AND '2015-12-31'
	 GROUP BY category
)
, sales_composition_ratio AS (
	SELECT category
		 , amount
		 -- 구성비
		 , 100.0 * amount / SUM(amount) OVER() AS composition_ratio
		 -- 구성비 누계
		 , 100.0 * SUM(amount) OVER(ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
           / SUM(amount) OVER() AS cumulative_ratio
	FROM monthly_sales
)
SELECT *
       -- 구성비누계 범위에 따라 순위 붙이기 
     , CASE
     	WHEN cumulative_ratio BETWEEN 0 AND 70 THEN 'A'
     	WHEN cumulative_ratio BETWEEN 70 AND 90 THEN 'B'
     	WHEN cumulative_ratio BETWEEN 90 AND 100 THEN 'C'
     END AS abc_rank
FROM sales_composition_ratio
ORDER BY amount DESC

category	amount	composition_ratio	cumulative_ratio	abc_rank
ladys_fashion	994800	57.7633259783997213	57.7633259783997213	A
mens_fashion	232600	13.5059807223319011	71.2693067007316223	B
food	161400	9.3717338288235977	80.6410405295552201	B
book	107000	6.2129833933341075	86.8540239228893276	B
dvd	65600	3.8090814075020323	90.6631053303913599	C
outdoor	57200	3.3213331784926257	93.9844385088839856	C
game	52000	3.0193937986296597	97.0038323075136453	C
cd	51600	2.9961676924863547	100.0000000000000000	C

팬 차트로 상품의 매출 증가율 확인하기