예시 상황

창고 계약 기간이 끝나기 전에 재고를 최대한 처리해야 하는 상황으로 가정

SKU Grade 분류 계획

SKU Grade별 상세 분류 기준

상위 n%는 WINDOW Function의 percent_rank() 활용하기
사용 예시는 아래 사진 참고
(참고로, Window Function의 OVER() 안에는 Partition by, Group by 활용 가능함. 둘의 차이가 기억이 안 난다면 이 블로그 참고 https://project-notwork.tistory.com/53 )

최종 결과물

---

코드

1. SKU_number별 File_Type_2, inven_unit, sold_unit 테이블 생성

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

2. inven_unit 기준 상위 N% 컬럼 추가 (percent_rank()), Month of coverage 컬럼 추가

-- 2. 1번 테이블을 WITH절에 넣고,
-- inven_unit 기준 상위 N% 컬럼 추가 (percent_rank()
-- Month of coverage 컬럼 추가 (inven_unit / Monthly Avg sold_unit) sold unit은 6개월 기준이었음

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 zerobase.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 month_avg_coverage -- 재고보유량 / 월평균판매량
		, percent_rank() OVER(PARTITION BY File_Type_2 ORDER BY inven_unit DESC) * 100 AS pct
FROM sku_1
-- WHERE File_Type_2 LIKE '2.%' -- 2. Semi-Active에 대한 행만 보기

3. CASE WHEN 절을 사용해서 SKU_Grade 컬럼 만들기

-- 3. CASE WHEN 절을 사용해서 SKU_Grade 컬럼 만들기

-- 위의 2단계에서 작성한 쿼리를 with절로(sku_2) 만들면 division by 0 에러 발생함.
-- 그 이유는, with 절을 사용된 쿼리는 하나의 '테이블'로 간주됨. (위의 Step 2에서는 쿼리로 진행해서 100개씩 fetch 해옴)
-- 따라서, division by 0 에러 발생.
-- 여기선 sold_unit이 0인 건에 대해 에러가 발생하는 것.
-- 따라서, sold_unit이 0인 건을 NULL로 치환해야 함

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 zerobase.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 month_avg_coverage -- 재고보유량 / 월평균판매량
		, 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 month_avg_coverage <60 THEN 'C'
			WHEN File_Type_2 = '2. Semi-Active' AND pct > 50 AND month_avg_coverage <60 THEN 'D'
			WHEN File_Type_2 = '2. Semi-Active' THEN 'E'
			ELSE 'F'
		END AS SKU_Grade
FROM sku_2

4. sku_grade 신규 테이블 CREATE & INSERT

-- 4. sku_grade 신규 테이블 CREATE & INSERT

CREATE TABLE zerobase.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_Type_2
			, sum(ItemCount) AS inven_unit
			, sum(SoldCount) AS sold_unit
	FROM zerobase.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 month_avg_coverage -- 재고보유량 / 월평균판매량
			, 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 month_avg_coverage <60 THEN 'C'
				WHEN File_Type_2 = '2. Semi-Active' AND pct > 50 AND month_avg_coverage <60 THEN 'D'
				WHEN File_Type_2 = '2. Semi-Active' THEN 'E'
				ELSE 'F'
			END AS SKU_Grade
	FROM sku_2
)

결과

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

---