[PostgreSQL] 카테고리별 매출 총계와 소계 / ABC 분석

매출에 시계열뿐만 아니라 상품의 카테고리, 가격 등을 조합해서 데이터의 특징을 추출해 리포팅한다.

purchase_detail_log

---

1. 카테고리별 매출(총계)과 소계 계산하기

🎯 대분류(카테고리) > 소분류(하위 카테고리) > 매출 순으로 계산되게 한다.

🎯 작은 단위로 집계한 후 >> UNION ALL 구문을 사용

WITH
sub_category_amount AS (
-- 소 카테고리 매출 집계 
	SELECT category AS category
		, sub_category AS 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
ORDER BY category
;

 

👉 UNION ALL을 사용해 테이블을 결합하는 방법은 테이블을 여러번 불러오고, 데이터를 결합하는 비용이 발생한다 (성능이 안좋음!)

👉 ROLLUP 을 구현하여 조금 더 쉽고 성능 좋은 쿼리를 만들 수 있다.

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)
ORDER BY 1
;

 

---

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

🎯 ABC 분석은 재고 관리 등에서 사용하는 분석 방법으로, 매출 중요도에 따라 상품을 나누고 그에 맞게 전략을 만들 때 사용한다.

 

1. 매출이 높은 순으로 데이터 정렬
2. 매출 합계를 집계
3. 매출 합계를 기반으로, 각 데이터가 차지하는 비율을 계산하여 구성비를 구함
4. 계산한 카테고리의 구성비를 기반으로 구성비 누계를 구함

WITH 
monthly_sales AS (
	SELECT category
		, sum(price) AS amount --항목별 매출 계
	FROM purchase_detail_log
	WHERE dt BETWEEN '2017-01-01' AND '2017-01-31' -- 대상 1개월 동안의 로그 조건 
	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 
;