23/MAR/2024

---

Attrition Dashboard 6 - Top N Filter

• 테이블 오름차순 내림차순 하려면 > 테이블에서 열 제목 부분을 클릭하면 됨.

• 여러개의 열을 하나로 묶어서 필터링하고 상대적인 값을 기준으로 필터하고싶다면 새로운 컬럼을 만들어줘야 합니다.

나는 Retired Rate Top3만 보고싶어요. 이때 참조되는 열은 Department. JobRole, JobLevel 이에요.

1. HR 테이블에 새 열을 입력합니다.

• 테이블뷰 > 새 열

  • 열 수식

  Dept_role_lv = HR[Department]&"_"&HR[JobRole]&"_"&HR[JobLevel]
  

2. 필터탭에 아까 만들어준 수식열을 넣어줍니다.

여기에 필터추가 필드>’이시각적개체의필터’> ‘Dept_role_lv’열을 추가합니다.

• 필터형식 > 상위 N
• 값 > Retired Rate
• 항목표시 > 위쪽 3

Attrition Dashboard 7 - Multi Row Card

JobRole별로 여러개의 행을 하나의 카드에서 필터된 값을 보고싶어요.

1. 여러행 카드 추가

2. 여기에 필터추가 필드>’이시각적개체의필터’> ‘JobRole’

• 필터형식 > 상위 N
• 값 > Retired Rate
• 항목표시 > 위쪽 3

Attrition Dashboard 8 - 레이아웃 꾸미기

Chapter 5

물류 센터 재고 관리 담당자가 된 나!

프로젝트 <악성 재고 파악과 관리 방안 플랜 수립>

Kaggle Data

• 과거 판매 데이터와 현재 잘 팔리는 활성 재고가 포함.
• 재고에는 많은 상품들이 있지만, 실제로는 판매되는 상품은 많지 않으며 심지어는 1년에 1번 가량씩 판매되기도 함.

Dataset Column Definition

• Order~ 주문번호
• File Type - Historical / Active
• SKU number- 상품 고유번호
• SoldFlag- 지난 6개월 간 판매된 적이 있으면 1, 없으면 0
• SoldCount- 판매량 (지난 6개월 동안의 판매량이라고 가정)
• ItemCount- 재고량
• New_Release_Flag- 미래에 동일한 SKu가 재입고된 적이 있으면 1, 없으면 0
• ReleaseYear- 출시년도 (입고년도라고 가정)
• PriceReg - 정상 판매가 / LowNetPrice - 고객 최종 실 구매가

분석 목표: 악성 재고 현황 파악 및 최소화 플랜 수립

우리가 고민해볼 수 있는 포인트

• Historical / Active 재고가 차지하는 비중이 얼마나 되는가?
• Historical 재고의 추산 가치(cost)가 얼마나 되는가?
• 이 재고는 얼마나 오랫동안 창고에 보관되어있었나?(Aging)
• 이 재고들을 다 판다고 가정하면 얼마나 시간이 더 필요한가?
• Historical 재고들에도 등급을 세워 플랜 수립해볼 수 있지 않을까?

재고관리의 주요 지표 1

Inventory Workflow

입고(Inbound) ► 재고 = 입고량-판매량 ► 출고(Outbound) 판매량

Keywords

• 입고 Inbound
• 출고 outbound
• 재고 Inventory
• SKU = Stock Keeping Unit = 재고 관리를 위한 최소 단위 코드
• Unit Quantity = 상품의 개수
• Unit per SKU = SKU 1종 당 상품의 개수
  • SKUA = 2units
  • SKUB = 3units
  • 2SKUs have 5units

재고관리의 주요 지표 2

Keywords

• 재고량 / 하루 판매량 = 우리의 재고로 며칠동안 판매가 가능한가??
• DOC = Day of Coverage

DOC가 낮을수록 재고 전환율이 높아 빨리빨리 주문해야합니다.

데이터 살펴보기

SELECT *
FROM Inventory i 
;

-- ROW 수: 198,917개 (중복x)
SELECT COUNT(*) 
FROM Inventory i 
;

SELECT COUNT(*)
FROM (
	 SELECT DISTINCT *
	 FROM Inventory i) i2
;

SELECT DISTINCT File_Type, SoldFlag
FROM Inventory i 
;

SELECT DISTINCT MarketingType, New_Release_Flag
FROM Inventory i 
;

SELECT DISTINCT ReleaseYear
FROM Inventory i 
ORDER BY 1 DESC
;

-- 위에 ReleaseYear 가 0 인 것이 있다 : 다행이 1
SELECT *
FROM Inventory i 
WHERE ReleaseYear  = 0
;

SELECT COUNT(DISTINCT SKU_number) AS sku_count 
FROM Inventory i 
;

SELECT min(PriceReg), max(PriceReg), AVG(PriceReg)
FROM Inventory i 
;

-- PriceReg가 0 : 2,549건 (LowNetPrice 참)
SELECT *
FROM Inventory i 
WHERE PriceReg = 0
;

-- LowNetPrice 가 0인 데이터도 꽤 많다.
SELECT min(LowNetPrice), MAX(LowNetPrice), AVG(LowNetPrice)
FROM Inventory i 
;

재고 수량 파악

SELECT File_Type, SoldFlag
	 , sum(ItemCount) AS inven_unit
	 , sum(SoldCount) AS sold_unit
FROM Inventory i 
GROUP BY 1,2
ORDER BY 1,2
;

-- Historical 리스트를 볼건데 1번이라도 팔린적이 있는 리스트 
SELECT File_Type, SoldFlag 
	, sum(ItemCount) AS inven_unit
	, sum(SoldCount) AS sold_unit
	, sum(ItemCount) / (sum(SoldCount)/6) AS month_of_coverage
FROM Inventory i 
WHERE SoldFlag = 1
AND File_Type = 'Historical'
;

-- 재고 Cost 파악
SELECT File_Type, SoldFlag
	, sum(ItemCount) AS inven_unit
	, round(AVG(PriceReg), 1) AS avg_price
	, round(sum(ItemCount * PriceReg), 0) AS inven_cost
FROM Inventory i 
WHERE 1=1
AND PriceReg > 0 -- PriceReg 가 0이하로 오기된 값은 제외한다.
GROUP BY 1,2
;

악성 재고 원인 파악 1 - 가격

SELECT File_Type, SoldFlag
	, SUM(ItemCount) AS inven_unit
	, ROUND(AVG(PriceReg), 0) AS avg_list_price
	, ROUND(AVG(LowNetPrice), 0) AS avg_net_price 
FROM Inventory i 
WHERE PriceReg > 0
GROUP BY 1,2
;

-- 100 --> 70 : (100-70) / 100 = 30%할인!

SELECT File_Type, SoldFlag
	, SUM(ItemCount) AS inven_unit
	, ROUND(AVG(PriceReg), 0) AS avg_list_price
	, ROUND(AVG(LowNetPrice), 0) AS avg_net_price 
	, ROUND(AVG((PriceReg - LowNetPrice) / PriceReg)*100, 1) AS discount
FROM Inventory i 
WHERE PriceReg > 0
GROUP BY 1,2
;

악성 재고 원인 파악 2 - 신상품 출시

-- NewRelaseFlag: 1 > 최신 신규 SKU 출시 / 0 > 신규 SIK 더이상 X (가장 최신 SKU)
-- ReleaseYear: 입고년도

SELECT SKU_number, count(DISTINCT New_Release_Flag) AS cnt
FROM Inventory i 
GROUP BY SKU_number 
HAVING cnt > 1
ORDER BY SKU_number 
;

-- cnt 가 2 인것은 New_Relase_Flag 가 0과 1 둘다 존재하는, 즉, 한번 입고된 다음에 신상품이 새롭게 또 릴리즈 된적이 있는 리스트

-- 위의 추측 검증.
SELECT *
FROM Inventory i 
WHERE SKU_number = 58313
;

악성 재고 원인 파악 3 - Aging

SELECT SKU_number, ReleaseYear 
FROM Inventory i 
;

SELECT min(ReleaseYear), max(RELEASEYear)
FROM Inventory i 
WHERE ReleaseYear > 0
ORDER BY 1
;

SELECT DISTINCT ReleaseYear
FROM Inventory i 
WHERE ReleaseYear > 0
ORDER BY 1
;

-- 현재가 2019년이라고 가정, 평균 몇 년의 재고 Aging
SELECT SKU_number, ReleaseYear, 2019 - ReleaseYear AS aging
FROM Inventory i 
WHERE ReleaseYear > 0
ORDER BY 1
;

SELECT File_Type, SoldFlag  
	  , round(avg(ReleaseYear), 0) AS avg_year
	  , round(avg(2019 - ReleaseYear), 1) AS avg_aging
FROM Inventory i 
WHERE ReleaseYear > 0
AND New_Release_Flag = 0
GROUP BY 1,2
ORDER BY avg_aging DESC
;

Appendix. CASE WHEN

CASE WHEN

• 조건문을 단일/다중으로 활용하여 조건에 따른 결과값을 새로운 컬럼 생성

사례1

• 조건 대상 컬림이 1개 일때
• 조건값(when_value)이 명확할 때

CASE case value -- 조건 대상 컬럼 1개
		WHEN value THEN statement_list -- 조건 1 (필수)
		[WHEN value THEN statement_list] .. -- 조건 2..(선택)
		[ELSE statement list] -- 예외케이스 (선택)
END as case_colume

사례2

• 조건 대상 컬럼이 복수일 때
• 조건값(when_value)이 명확하지 않을 때
• 여러 복합적인 조건을 걸고 싶을 때

CASE 조건 대상 컬럼 특정 X
		WHEN search condition THEN statement list -- 조건 1 (필수)
		[WHEN search condition THEN statement list] -- 조건 2.. (선택)
		[ELSE statement list] -- 예외케이스 (선택)
END case colume

Appendix. WITH절 vs. FROM절 Subquery

FROM 절 SUBQUERY

• ORIGINAL_TABLE에 사용하지 않을 컬럼이 너무 많을 경우 연산량을 줄이기 위하여
• SUBQUERY 내 중간 연산이 필요한 경우

SELECT Col 3, SUM (Col 4) as SUM 
FROM (
		SELECT Col 1, Col 2, Col 3, AVG (Col 4) as Col 4
		FROM ORIGINAL_TABLE
		WHERE Col 1 = 'Y'.  
		GROUP BY 1, 2, 3
		...
		) sub -- FROM 절 - Table 위치에 Query를 넣어줌
GROUP BY 1

WITH 절

• 해당 Query 안에서만 사용할 수 있는 임시 테이블을 만드는 개념
• WITH 절의 임시 테이블은 메모리를 차지하기 때문에, 리소스를 초과하는 경우 에러 발생할 수 있음
• 임시 테이블을 구성해서 여러 Query를 Join, Union 시킬 경우 유용

WITH [TEMP TABLE NAME] AS
			(SELECT ~~
				FROM Table
				......
			) -- 임시 테이블용 쿼리
SELECT ~~
FROM [TEMP TABLE NAME] -- 임시 테이블을 활용하여 새로 구성하는 쿼리

Query 구조화 1 - CASE WHEN

-- case when(1)
SELECT 
	CASE File_Type 
		WHEN 'Historical' THEN '악성재고'
		WHEN 'Active' THEN '활성재고'
		ELSE '기타' -- 기타가 발생할 여지X 하지만 현업에서는 Else를 활용하
	END AS File_type2 -- CASE WHEN 은 무조건 END 해줘야 한다.
	, sum(ItemCount) AS inven_unit
	, sum(soldCount) AS sold_unit
FROM Inventory i 
GROUP BY 1
ORDER BY 1
;

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

Query 구조화 2 - Subquery

-- 각각 타입의 재고가 총 재고에서 몇%를 차지하는지 알고 싶다.
-- Group by: 기준 컬럼으로 집계하여 row수가 줄어 듦.
-- Window Function :row 수가 그대로!! + 새로운 컬럼에 집계값을 추가해서 보여
SELECT File_Type_2
	, inven_unit
	, round(inven_unit / sum(inven_unit) over() * 100, 1) AS pct
--	, sum(inven_unit) over() AS total_inven_unit
FROM
(
	SELECT 
		CASE 
			WHEN File_Type = 'Historical' AND SoldFlag = 0 THEN '3. Historical'
			WHEN File_Type = 'Historical' AND SoldFlag = 1 THEN '2. Semi-Active'
			WHEN File_Type = 'Active' THEN '1. Active'
			ELSE 'Others'
		END AS File_Type_2
		, sum(ItemCount) AS inven_unit
		, sum(SoldCount) AS sold_unit
	FROM Inventory i 
	GROUP BY 1
	ORDER BY 1
) sub
;

Query 구조화 3 - WITH절

-- 총 합계도 추가해주고 싶다!
-- WITH절 * UNION ALL 활용

WITH sub AS
(
	SELECT 
		CASE 
			WHEN File_Type = 'Historical' AND SoldFlag = 0 THEN '3. Historical'
			WHEN File_Type = 'Historical' AND SoldFlag = 1 THEN '2. Semi-Active'
			WHEN File_Type = 'Active' THEN '1. Active'
			ELSE 'Others'
		END AS File_Type_2
		, sum(ItemCount) AS inven_unit
		, sum(SoldCount) AS sold_unit
	FROM Inventory i 
	GROUP BY 1
	ORDER BY 1
)
SELECT File_Type_2, inven_unit
	, round(inven_unit / sum(inven_unit) OVER () * 100, 1) AS pct
FROM sub
UNION ALL
SELECT 'Total' AS File_Type_2
	, sum(inven_unit) AS inven_unit
	, sum(inven_unit) / sum(inven_unit) * 100 AS pct
FROM sub
;

재고 관리를 위한 SKU Grade 기획 프로젝트

Inventory Management SKU Grade 기획

체계적인 재고 관리를 위해 SKU Grade를 기획해보고자 합니다

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

체계적인 재고 관리를 위해 SKU Grade 를 기획해보고자 합니다

Mission! 세분화된 신규 분류값인 SKU_Grade 기획

(물류 창고 계약 기간이 5년이 남았다고 가정해봅시다. 5년 = 60개월)

(Month of Coverae = 보유 재고로 몇 달동안 판매가 가능한가? = 재고량 / 1달 평균 판매량

1. Active
   • Active 재고라고 모두 잘 팔릴까?
   • 시장 수요를 참고해 인기 상품을 입고시켰다는 가정하에, 넉넉한 물량을 기준으로 등급을 매겨보자!
2. Semi_Active
   • Historical 이지만, 6개월 이내 팔린 이력이 있다.
   • 하지만 어쩌다가 운좋게 1개가 팔린거라면?
   • 재고 보유량과 재고 전환율로 등급을 매겨보자!
3. Historical
   • 6개월간 팔린 이력이 없다. 일단 최 하위 등급.

최종 Mission 결과물

• SKU_number 별 SKU_grade와 File_Type_2
• Inventory.sku_grade 라는 새로운 테이블에 Insert 까지

재고 관리를 위한 SKU Grade 기획 프로젝트 - HINT

• File_ Type 2 로 3개의 대분류로 우선적으로 분리되며, 그 안에서 SKU Grade가 소분류로 추가 분리
• SKU number 별로 Se Jee 컬럼이 강됨 Query를 WITH절로 구비해놓고 시작해보자!
• 필요하다면, WITH절도 여러 개 사용할 수 있어요!

---

• Month of coverage = 보유 재고로 몇 달동안 판매가 가능한가? = 재고량 / 1달 평균 판매량

• 상위 n% → WINDOW FUNCTION percent_rank()를 활용해보자!

  • https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

  -- percent_rank() 예시
  
  mysql> SELECT
           val,
           ROW_NUMBER()   OVER w AS 'row_number',
           CUME_DIST()    OVER w AS 'cume_dist',
           PERCENT_RANK() OVER w AS 'percent_rank'
         FROM numbers
         WINDOW w AS (ORDER BY val);
  +------+------------+--------------------+--------------+
  | val  | row_number | cume_dist          | percent_rank |
  +------+------------+--------------------+--------------+
  |    1 |          1 | 0.2222222222222222 |            0 |
  |    1 |          2 | 0.2222222222222222 |            0 |
  |    2 |          3 | 0.3333333333333333 |         0.25 |
  |    3 |          4 | 0.6666666666666666 |        0.375 |
  |    3 |          5 | 0.6666666666666666 |        0.375 |
  |    3 |          6 | 0.6666666666666666 |        0.375 |
  |    4 |          7 | 0.8888888888888888 |         0.75 |
  |    4 |          8 | 0.8888888888888888 |         0.75 |
  |    5 |          9 |                  1 |            1 |
  +------+------------+--------------------+--------------+
  

File_Type2	SKU Grade	Conditions

1. Active | S | Inven unit 상위 10%이내
2. Active | A | Inven unit 상위 10~50%
3. Active | B | Inven unit 상위 50% 초과
4. Semi-Active | C | Inven_unit 상위 50% 이내 & Month of coverage가 60개월 미만
5. Semi-Active | D | Inven_unit 상위 50% 초과 & Month of coverage가 60개월 미만
6. Semi-Active | E | C,D 등급 조건에 해당 되지 않는 경우
7. Historical | F | Historical 재고 중 6개월 이내 판매 이력이 없는 경우

SKU Grade 기획 실전 쿼리 해설 (1) 기초 데이터 구성

-- STEP (1) : 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_Type_2
	, sum(ItemCount) AS inven_unit
	,sum(SoldCount) AS sold_unit
FROM Inventory i 
GROUP BY 1,2
ORDER BY 1,2
;

/* STEP (2) : (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_Type_2
		, 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_Type_2, inven_unit, sold_unit
	, inven_unit / (sold_unit/6) AS MOC -- 재고보유량 / 월평균 판매량 (Month of coverage = MOC)
	, percent_rank() over(PARTITION BY File_Type_2 ORDER BY inven_unit DESC)* 100 AS pct -- inven_unit 상위 N% 컬럼
FROM sku_1
;

SKU Grade 기획 실전 쿼리 해설 (2) Division by 0

Division by 0 같은 에러는 나누기에서 나타난다.

: 이때에 If문을 써주어서 0과 나누는 값은 NULL 로 치환되게 해주어야 해요.

-- DIVISION BY 0 : 나누기하는데, 분모에 0 -> 분모를 0 대신에 Null로 치환 
--   				(최종 나누기한 결과값은 NULL)
-- IF문 : IF(CONDITION, TRUE값, FALSE)

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_Type_2
		, 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_Type_2, inven_unit, sold_unit
		, inven_unit / **if(sold_unit=0, NULL, sold_unit/6)** AS MOC -- 재고보유량 / 월평균 판매량 (Month of coverage = MOC)
		, percent_rank() over(PARTITION BY File_Type_2 ORDER BY inven_unit DESC)* 100 AS pct -- inven_unit 상위 N% 컬럼
	FROM sku_1) -- sku_1 를 이용 만들었던 테이블을 다시 sku_2에 넣는다.
SELECT *
FROM sku_2
;

SKU Grade 기획 실전 쿼리 해설 (3) SKU Grade

-- STEP (3) : CASE WHEN 절을 사용하여 SKU_Grade 컬럼 조건문 만들기
-- DIVISION BY 0 : 나누기하는데, 분모에 0 -> 분모를 0 대신에 Null로 치환 
--   				(최종 나누기한 결과값은 NULL)
-- IF문 : IF(CONDITION, TRUE값, FALSE)

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_Type_2
		, 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_Type_2, inven_unit, sold_unit
		, inven_unit / if(sold_unit=0, NULL, sold_unit/6) AS MOC -- 재고보유량 / 월평균 판매량 (Month of coverage = MOC)
		, percent_rank() over(PARTITION BY File_Type_2 ORDER BY inven_unit DESC)* 100 AS pct -- inven_unit 상위 N% 컬럼
	FROM sku_1) -- sku_1 를 이용 만들었던 테이블을 다시 sku_2에 넣는다.
SELECT SKU_number
	, File_Type_2
	,CASE WHEN File_Type_2 = '1. Active' AND pct < 10 THEN 'S'  -- 1
		  WHEN File_Type_2 = '1. Active' AND pct < 50 THEN 'A'  -- 2 CASE WHEN은 위부터 순차적으로 필터링 되기때문에 굳이 같은 조건을 써주지 않아도 된다.
		  WHEN File_Type_2 = '1. Active' THEN 'B' -- 3 위에 CASE WHEN 필터링 하고 나머지 애들은 B
		  WHEN File_Type_2 = '2. Semi-Active' AND pct <= 50 AND MOC < 60 THEN 'C'  -- 4
		  WHEN File_Type_2 = '2. Semi-Active' AND MOC < 60 THEN 'D' -- 5
		  WHEN File_Type_2 = '2. Semi-Active' THEN 'E' -- 6
		  ELSE 'F'
		  END AS SKU_Grade
FROM sku_2
;

SKU Grade 기획 실전 쿼리 해설 (4) TABLE CREATE

-- STEP (4) : 'sku_grade' 신규 테이블 CREATE 및 INSERT
CREATE TABLE Inventory.sku_grade AS -- 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_Type_2
			, 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_Type_2, inven_unit, sold_unit
			, inven_unit / if(sold_unit=0, NULL, sold_unit/6) AS MOC -- 재고보유량 / 월평균 판매량 (Month of coverage = MOC)
			, percent_rank() over(PARTITION BY File_Type_2 ORDER BY inven_unit DESC)* 100 AS pct -- inven_unit 상위 N% 컬럼
		FROM sku_1) -- sku_1 를 이용 만들었던 테이블을 다시 sku_2에 넣는다.
	SELECT SKU_number
		, File_Type_2
		,CASE WHEN File_Type_2 = '1. Active' AND pct < 10 THEN 'S'  -- 1
			  WHEN File_Type_2 = '1. Active' AND pct < 50 THEN 'A'  -- 2 CASE WHEN은 위부터 순차적으로 필터링 되기때문에 굳이 같은 조건을 써주지 않아도 된다.
			  WHEN File_Type_2 = '1. Active' THEN 'B' -- 3 위에 CASE WHEN 필터링 하고 나머지 애들은 B
			  WHEN File_Type_2 = '2. Semi-Active' AND pct <= 50 AND MOC < 60 THEN 'C'  -- 4
			  WHEN File_Type_2 = '2. Semi-Active' AND MOC < 60 THEN 'D' -- 5
			  WHEN File_Type_2 = '2. Semi-Active' THEN 'E' -- 6
			  ELSE 'F'
			  END AS 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
;

Power BI로 데이터 가져오기 및 전처리

먼저 Inventory 테이블을 가져온 다음에 조건열을 추가합니다.

습관적으로 열을 전체 선택한 후 ‘중복된 항목 제거’를 클릭하여 중복된 항목을 없애줍니다.

열 전체를 선택하여 ‘변환’ > ‘데이터 형식 검색’ 을 선택하면 각 열에 맞는 데이터 형식에 맞춰준다.

지표 정의

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

Dax 1 - SUM, DIVIDE

먼저 새 빈 테이블(Calculation)을 만들어 여기에 모든 계산식을 넣어주겠습니다.

• 테이블 도구 > 새 테이블
  • Calculation = {blank()}

1. SKU Count (SKU 종수) : SKU Unique 한 개수 - SKU_number 의 Distinctcount

1. SKU_Count = DISTINCTCOUNT(Inventory[SKU_number])

1. Inven Unit: 재고 보유량 - ItemCount의 합

2. Inven_Unit = sum(Inventory[ItemCount])

1. Unit per SKU : 한 SKU 당 Unit 수 - A: 10

   a. A: 10개 + B: 4개 → SKU Coung : 2개 / Unit: 14개

   b. Unit per SKU = 14/2 = 7

3. Unit_per_SKU = DIVIDE([2. Inven_Unit], [1. SKU_Count])

Dax 2 - SUMX

1. Inven Cost: SUM(Unit 수 * PriceReg)⇒ SUMX(Inventory, ItemCount *PriceReg)

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

• 상세 설명

  이것은 새 열 만들어서 수식 만드는것과 같다.

  Inven_cost_per_SKU = Inventory[PriceReg]*Inventory[ItemCount]

1. Inven Cost per Unit : SUM(Unit 수 * PriceReg) / 재고 보유량

5. Inven_Cost_per_Unit = DIVIDE([4. Inven_Cost], [2. Inven_Unit])

1. Month of Coverage : 우리가 가지고 있는 재고로 몇 달을 커버할 수 있는가?

   a. 재고 보유량 / 월 평균 판매량

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

---

Studied from 제로베이스 데이터 스쿨