[SQL 분석][Ch5] SKU Grade 기획

체계적인 재고 관리를 위해 SKU Grade를 기획

• 창고 계약 기간이 5년 남았다고 가정 (5년 = 60개월)
• SKU Type별로 Segment를 나누고 각 Type별 특성에 맞는 관리 방안 수립

File_Type과 SoldFlag로 만든 3가지 분류에 새로운 기준 수립하여 더 세분화된 SKU Grade라는 분류값 기획

SKU Grade 분류 기준

Active (S / A / B)

1. Active 재고라고 모두 잘 팔리는지
2. 시장 수요를 참고하여 인기 상품을 입고시켰다는 가정하에, 물량을 기준으로 등급 책정

   S : Inven_unit 상위 10% 이내
   A : Inven_unit 상위 10~50%
   B : Inven_unit 상위 50% 초과

Semi-Active (C / D / E)

1. Historical이지만 6개월 이내에 팔린 이력 o
2. 근데 어쩌다 운좋게 1개가 팔렸을 가능성
3. 재고 보유량과 재고 전환율로 등급 책정해보자

   C : Inven_unit 상위 50% 이내 & Month of Coverage가 60개월 미만
   D : Inven_unit 상위 50% 초과 & Month of Coverage가 60개월 미만
   E : C, D 등급 조건에 해당되지 않는 경우
   Month of Coverage = 보유 재고로 몇 달동안 판매가 가능한가 (재고량 / 1달 평균 판매량)

Historical (F)

1. 6개월 간 팔린 이력x. 일단 최하위 등급

   F : Historical 재고 중 6개월 이내 판매 이력이 없는 경우

---

Step1.

SKU_number별 File_Type2, inven_unit, sold_unit 정보 기본 테이블 생성

SELECT SKU_number,
	CASE
		WHEN File_Type = 'Historical' AND SoldFlag = 1 THEN '2. Semi-Active'
		WHEN File_Type = 'Historical' AND SoldFlag = 0 THEN '3. Historical'
		WHEN File_Type = 'Active' THEN '1. Active'
		ELSE 'Others'
	END AS File_Type2
	, sum(ItemCount) AS inven_unit
	, sum(SoldCount) AS sold_unit
FROM inventory i
GROUP BY 1,2
ORDER BY 1,2
;

Step2.

위 (1) 테이블을 with절로 묶고,
inven_unit 상위 n% 컬럼 추가 (percent_rank())
Month of Coverage 컬럼 추가 (inven_unit / Monthly Average sold_unit)

WITH sku_1 AS
(
	SELECT SKU_number,
		CASE
			WHEN File_Type = 'Historical' AND SoldFlag = 1 THEN '2. Semi-Active'
			WHEN File_Type = 'Historical' AND SoldFlag = 0 THEN '3. Historical'
			WHEN File_Type = 'Active' THEN '1. Active'
			ELSE 'Others'
		END AS File_Type2
		, sum(ItemCount) AS inven_unit
		, sum(SoldCount) AS sold_unit
	FROM inventory i
	GROUP BY 1,2
	ORDER BY 1,2
)
SELECT SKU_number, File_Type2, inven_unit, sold_unit
		, inven_unit / (sold_unit/6) AS MOC   -- 재고보유량 / 월평균 판매량 (MOC = Month of Coverage)
		, percent_rank() over(PARTITION BY File_Type2 ORDER BY inven_unit DESC) * 100 AS pct 
FROM sku_1
;

Step3.

CASE WHEN절 사용하여 SKU_Grade 컬럼 조건문 생성

WITH sku_1 AS
(
	SELECT SKU_number,
		CASE
			WHEN File_Type = 'Historical' AND SoldFlag = 1 THEN '2. Semi-Active'
			WHEN File_Type = 'Historical' AND SoldFlag = 0 THEN '3. Historical'
			WHEN File_Type = 'Active' THEN '1. Active'
			ELSE 'Others'
		END AS File_Type2
		, sum(ItemCount) AS inven_unit
		, sum(SoldCount) AS sold_unit
	FROM inventory i
	GROUP BY 1,2
	ORDER BY 1,2
), 
sku_2 AS
(
	SELECT SKU_number, File_Type2, inven_unit, sold_unit
			, inven_unit / if(sold_unit=0, NULL, sold_unit/6) AS MOC  -- nullif(sold_unit/6, 0)
			, percent_rank() over(PARTITION BY File_Type2 ORDER BY inven_unit DESC) * 100 AS pct 
	FROM sku_1
)
SELECT SKU_number
		, File_Type2
		, CASE WHEN File_Type2 = '1. Active' AND pct < 10 THEN 'S'
			   WHEN File_Type2 = '1. Active' AND pct < 50 THEN 'A'
			   WHEN File_Type2 = '1. Active' THEN 'B'
			   WHEN File_Type2 = '2. Semi-Active' AND pct <= 50 AND MOC < 60 THEN 'C'
			   WHEN File_Type2 = '2. Semi-Active' AND MOC < 60 THEN 'D'  -- (AND pct > 50)
			   WHEN File_Type2 = '2. Semi-Active' THEN 'E'
			   ELSE 'F'
			   END AS SKU_Grade
FROM sku_2
;

• Divison by 0 문제 발생 (분모가 0인 데이터 존재) > 분모를 0 대신 null로 치환 >> 최종값은 null
  • step2에서는 출력 결과가 제한되어 있어 보이는 행 결과에서는 문제가 없었으나, with절로 묶어 테이블로 생성하면서 전체 테이블로 계산하게 되어 에러 발생
  • inven_unit / if(sold_unit=0, NULL, sold_unit/6) 결과와 nullif(sold_unit/6, 0) 결과는 동일
  • nullif(표현식1, 표현식2) : 표현식1 = 표현식2 → null / 표현식1 ≠ 표현식2 → 표현식1 리턴
    
    

Step4.

'sku_grade' 신규 테이블 CREATE 및 INSERT
→ 생성 후 컬럼 개수 확인

CREATE TABLE inventory.sku_grade AS
(
	WITH sku_1 AS
		(SELECT SKU_number,
			CASE
				WHEN File_Type = 'Historical' AND SoldFlag = 1 THEN '2. Semi-Active'
				WHEN File_Type = 'Historical' AND SoldFlag = 0 THEN '3. Historical'
				WHEN File_Type = 'Active' THEN '1. Active'
				ELSE 'Others'
			END AS File_Type2
			, sum(ItemCount) AS inven_unit
			, sum(SoldCount) AS sold_unit
		FROM inventory i
		GROUP BY 1,2
		ORDER BY 1,2
		), 
	sku_2 AS
		(SELECT SKU_number, File_Type2, inven_unit, sold_unit
				, inven_unit / if(sold_unit=0, NULL, sold_unit/6) AS MOC
				, percent_rank() over(PARTITION BY File_Type2 ORDER BY inven_unit DESC) * 100 AS pct 
		FROM sku_1
		)
	SELECT SKU_number
			, File_Type2
			, CASE WHEN File_Type2 = '1. Active' AND pct < 10 THEN 'S'
				   WHEN File_Type2 = '1. Active' AND pct < 50 THEN 'A'
				   WHEN File_Type2 = '1. Active' THEN 'B'
				   WHEN File_Type2 = '2. Semi-Active' AND pct <= 50 AND MOC < 60 THEN 'C'
				   WHEN File_Type2 = '2. Semi-Active' AND MOC < 60 THEN 'D'  -- (AND pct > 50)
				   WHEN File_Type2 = '2. Semi-Active' THEN 'E'
				   ELSE 'F'
				   END AS SKU_Grade
	FROM sku_2
)
;

생성한 테이블 확인

SELECT File_Type2, sku_grade, count(DISTINCT SKU_number) AS sku_cnt 
FROM sku_grade sg
GROUP BY 1,2
;