스터디 노트 46일차

SKU_Grade 칼럼 만들기

-- SKU_Grade 기획
-- 물류 창고 계약 기간이 5년이 남았다고 가정 = 60개월
-- Month of coverage = 보유 재고로 몇달 동안 판매가 가능한가 = 재고량 / 1달 평균 판매량

-- WINDOW 함수란?
-- 집계된 결과값을 기존 데이터에 추가하여 보여줌
-- 합계,평균,순위 메기기 , 순서 조작등등
-- 결과를 SHOW해주되 , 결과 건수가 줄어들지 않음

/* WINDOW FUCTION

• SELECT WINDOW_FUCTION( )

• 		OVER ( PARTITION BY <Column> 

• 			   ORDER BY             )

• */

-- 1. SKU_number 별로 File_Type_2 , 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 File_Type_2
, sum(ItemCount) inven_unit
, sum(SoldCount) sold_unit
FROM inventory.inventory i
GROUP BY 1,2
ORDER BY 1,2
;

-- 2 . 1번 테이블을 WITH절에 넣고 inven_unit 상위 % 컬럼 추가 (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 File_Type_2
, sum(ItemCount) inven_unit
, sum(SoldCount) 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) month_of_coverage
, percent_rank() over(PARTITION BY File_Type_2 ORDER BY inven_unit DESC) * 100 pct
FROM sku_1
;

-- 3. CASE WHEN절을 사용하여 SKU_Grade 컬럼 조건문 만들기
-- DIVISTION BY 0 : 나누기 하는데 , 분모에 0이 있음 -> 분모를 0 대신에 NULL값으로 치환 -> (최종 나누기한 결과값은 NULL)
-- sold_unit이 0인 경우에 분모가 0이 되기때문이다.
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 File_Type_2
, sum(ItemCount) inven_unit
, sum(SoldCount) sold_unit
FROM inventory.inventory i
GROUP BY 1,2
ORDER BY 1,2
),
sku_2 as
(SELECT SKU_number , File_Type_2 , inven_unit , sold_unit
, inven_unit / IF (sold_unit = 0 , NULL , sold_unit/6) month_of_coverage
, percent_rank() over(PARTITION BY File_Type_2 ORDER BY inven_unit DESC) * 100 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 month_of_coverage < 60 THEN 'C'
WHEN File_Type_2 = '2. Semi-Active' AND month_of_coverage < 60 THEN 'D'
WHEN File_Type_2 = '2. Semi-Active' THEN 'E'
ELSE 'F'
END SKU_Grade
FROM sku_2
;

-- 4 . 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 File_Type_2
, sum(ItemCount) inven_unit
, sum(SoldCount) sold_unit
FROM inventory.inventory i
GROUP BY 1,2
ORDER BY 1,2
),
sku_2 as
(SELECT SKU_number , File_Type_2 , inven_unit , sold_unit
, inven_unit / IF (sold_unit = 0 , NULL , sold_unit/6) month_of_coverage
, percent_rank() over(PARTITION BY File_Type_2 ORDER BY inven_unit DESC) * 100 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 month_of_coverage < 60 THEN 'C'
WHEN File_Type_2 = '2. Semi-Active' AND month_of_coverage < 60 THEN 'D'
WHEN File_Type_2 = '2. Semi-Active' THEN 'E'
ELSE 'F'
END SKU_Grade
FROM sku_2
)
;

-- SKU_Grade 테이블 살펴보기

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

Power BI 'Inventory' Table

SKU Count(SKU 종수) : SKU Unique 한 개수 - SKU_number 의 Distinctcount
Inven Unit : 재고 보유량 - ItemCount의 합계
Unit per SKU : 한 SKU당 Unit 수 -
A:10 + B:4 -> SKU count : 2개 / Unit : 14개
Unit per SKU = 14/2 = 7
Inven Cost : sum(Unit수 PriceReg)
Inven Cost per Unit : sum(Unit 수 PriceReg) / 재고 보유량
Month of Coverage : 우리가 가지고 있는 재고로 몇 달을 커버할 수 있는가?
재고 보유량 / 월 평균 판매량

1. Sku count = DISTINCTCOUNT(Inventory[SKU_number])

2. Inven Unit = SUM(Inventory[ItemCount])

3. Unit per SKU = DIVIDE([2. Inven Unit]
   , [1. Sku count])

4. Inven Cost = SUMX(Inventory, Inventory[ItemCount] * Inventory[PriceReg])

5. Inven Cost per Unit = DIVIDE([4. Inven Cost] , [2. Inven Unit])

6. Month of Coverage = DIVIDE([2. Inven Unit] ,
   DIVIDE( sum(Inventory[SoldCount]) , 6 )
   )

   -Switch , SelectedValue를 사용하여 슬라이서 만들기
   Swithch_Indicator = SWITCH(SELECTEDVALUE(D_indicator[Indicator])
   ,"Sku count",[1. Sku count]
   ,"Inven Unit",[2. Inven Unit]
   ,"Unit per SKU",[3. Unit per SKU]
   ,"Inven Cost",[4. Inven Cost]
   ,"Inven Cost per Unit",[5. Inven Cost per Unit]
   ,"Month of Coverage",[6. Month of Coverage]
   )
   

   

   #SCM 이란?
   :Supply Chain Management - 공급망 관리
   생산자 , 공급자 , 고객에 이르는 물류의 흐름을 하나의 가치사슬 관점에서 파악하고
   공급망의 구성 요소들 간에 이루어지는 전체 프로세스 최적화를 달성 하고자 하는 경영 혁신 기법