[Sql분석] SKU Grade

Sku Grade 분류기준

---

기존 분류

File_Type2	File_Type	SoldFlag
1. Active	Active	-
2. Semi-Active	Historical	6개월 이내 판매 O
3. Historical	Historical	6개월 이내 판매 X

더 드릴다운한 분류

File_Type2	SKU Grade
1. Active	S / A / B
2. Semi-Active	C / D / E
3. Historical	F

Active ( S / A / B )

---

1. 활성재고는 모두 잘팔리는가?
2. 시장 수요를 참고해 인기 상품을 입고시켰다는 가정하에, 물량을 기준으로 등급을 나누자

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

Semi-Active ( C / D / E )

---

1. Historical이지만 6개월이내에 팔린 이력이 있다
2. BUT! 어쩌다가 1개가 팔린걸 수도 있음
3. 그러니 재고보유량과 재고전환율을 고려해 등급을 매기자

C : Inven_unit 상위 50%이내 & Month of coverage가 60개월 미만
D : Inven_unit 상위 50% 초과 & Month of coverage가 60개월 미만
E : C, D 등급 조건에 해당 되지 않는 경우

Historical ( F )

---

1. 6개월동안 팔린 이력이 없다 -> 최하위등급

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

👉 STEP 1

---

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

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

• case when절 활용
• 우선 File_Type, SoldFlag 조건을 이용해 File_Type_2 컬럼이 있는 정보테이블 생성

<결과>

👉 STEP 2

---

ㄴ (1)번 테이블을 WITH절에 넣고 ,
inven_unit 상위 N% 컬럼 추가 (percent_rank())
month of coverage 컬럼 추가 (inven_unit / Monthly Average sold_unit)
(Monthly Average sold_unit은 sold_unit이6개월이내에 팔린 내역이니 6으로 나누면 된다)

with sku_1 as
(
	SELECT SKU_number,
		 CASE
			WHEN File_Type = 'Active' THEN '1. Active'
			WHEN File_Type = 'Historical' AND SoldFlag = 1 THEN '2. Semi-Active'
			WHEN File_Type = 'Historical' AND SoldFlag = 0 THEN '3. Historical'
			ELSE 'Others'
		  END AS File_Type_2
		, sum(ItemCount) AS inven_unit
		, sum(SoldCount) AS sold_unit
	FROM inventory.inventory i 
	GROUP BY 1,2
	ORDER BY 1,2
)
SELECT SKU_number, File_Type_2, inven_unit, sold_unit
	, inven_unit / (sold_unit/6) AS MOC
	, percent_rank() OVER (PARTITION BY File_Type_2 ORDER BY inven_unit DESC) * 100 AS pct 
FROM sku_1
;

• with절에 위에 만들었던 테이블을 넣어준다
• 월별로 얼마나 팔리는지를 보기위한 MOC를 만들어주기위해 재고량을 1개월 판매량으로 나눠주면 된다
• percent_rank :
  partition by로 나눠준 범위를 나누고 order by로 순서를 정해 랭크를 정해줌
  Window Function 함수
  (재고량(inven_unit)의 상위 N%를 나타내기 위해 필요함)
  <결과>

👉 STEP 3

---

ㄴ CASE WHEN절을 사용하여 SKU_Grade 컬럼 조건문 만들기

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

• 방금 만들어준 테이블도 sku_2라는 이름으로 임시테이블 생성

• Division by 0이라는 문제발생
  Division by 0 : 나누기를 하는데 분모에 0이 존재-> 분모를 0대신에 Null로 치환

• 앞서서는 문장 전체가 아니라 화면에 제한적으로 표시되도록 해놓은 설정때문에 error가 없었지만 with절을 사용해 테이블로 사용하면서 전체 테이블로 계산이 되니 오류가 생긴 것

• Nullif (표현식 1, 표현식2)
  : 표현식1이 표현식2와 같은면 Null, 같지 않으면 표현식1을 리턴한다

• inven_unit / if(sold_unit=0, Null, sold_unit / 6) 도 동일한 결과

• A등급 조건에 10 <= pct and pct < 50 해주지 않아도 위에 S등급 조건이 먼저 걸리기 때문에 10 <= pct는 빼줘도 됨

• 나머지 조건들도 동일

<결과>

👉 STEP 4

---

ㄴ 'sku_grade' 신규테이블 create 및 insert

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

• 테이블을 생성할 경우 아래처럼 Statistics창이 나타남.
• 컬럼개수 확인해주면 된다

• 잘 생성됐는지 확인하기
• 꼭 확인해주는게 좋음
• 나도 C, D등급이 설정되지 않은 실수를 했다는걸 확인하고 나서 앎

SELECT File_Type_2 , SKU_Grade , count(DISTINCT SKU_number) AS sku_cnt
FROM inventory.sku_grade sg 
GROUP BY 1,2
ORDER BY 1,2
;

<결과>