데이터셋 :
Kaggle의 Historical Sales and Active Inventory

File_Type : Historical = 악성 재고, Active = 활발히 팔리는 제품

분석 목표 :

재고 관리의 주요 지표

• 입고량
• 판매량
• 재고

• 권장 판매가
• 실 판매가

• SKU
• Unit Quantity (상품 개수)
• Unit Per SKU (SKU 1종 당 상품의 개수. 예를 들어, 총 5 Unit이 있고, SKU 종수는 2개일 때, 평균 Unit Per SKU = 5/2 = 2.5)

• DOC (Day of Coverage, 재고량 / 하루 판매량)

---

데이터 살펴보기

SELECT DISTINCT File_Type 
FROM zerobase.inventory i 

SELECT DISTINCT ReleaseYear 
FROM zerobase.inventory i 
ORDER BY 1 DESC 

-- ReleaseYear  = 0 인 데이터는 1건
SELECT *
FROM zerobase.inventory i 
WHERE ReleaseYear  = 0

SELECT count(DISTINCT SKU_number) AS sku_cnt
FROM zerobase.inventory i 

SELECT min(PriceReg), max(PriceReg), avg(PriceReg)
FROM zerobase.inventory i 

-- PriceReg(정가)가 0인 경우에는 LowNetPrice(실판매가)를 참조하도록 하기
SELECT *
FROM zerobase.inventory i 
WHERE PriceReg =0

SELECT min(LowNetPrice), max(LowNetPrice), avg(LowNetPrice)
FROM zerobase.inventory i 

SELECT *
FROM zerobase.inventory i 
WHERE LowNetPrice =0

---

재고 수량 파악하기

-- File_Type별 + SoldFlag별 재고와 판매량 파악
SELECT File_Type , SoldFlag ,
		sum(ItemCount) AS inven_unit,
		sum(SoldCount) AS sold_unit
FROM zerobase.inventory i 
GROUP BY 1, 2
ORDER BY 1, 2


SELECT File_Type , SoldFlag ,
		sum(ItemCount) AS inven_unit,
		sum(SoldCount) AS sold_unit,
		sum(ItemCount) / (sum(SoldCount)/6) AS month_of_coverage
FROM zerobase.inventory i 
WHERE SoldFlag =1
	AND File_Type = 'Historical'
GROUP BY 1,2

-- 재고 가격 파악
SELECT File_Type , SoldFlag,
sum(ItemCount) AS inven_unit,
round(avg(PriceReg),0) AS avg_price,
round(sum(ItemCount * PriceReg),0) AS inven_cost
FROM zerobase.inventory i 
WHERE 1=1 and
	PriceReg > 0
GROUP BY 1, 2

---

악성 재고 원인 파악

1. 가격

-- 이미 잘 팔리는 제품은 할인율이 가장 낮음
-- 비활성재고 중, 6개월 간 팔린 적이 있는 항목은 할인율이 매우 높음
-- 비활성재고 중, 6개월 간 팔린 적이 없는 항목은 비교적 할인율이 낮음
-- 가격이 영향을 미칠 수 있음
SELECT File_Type , SoldFlag ,
sum(ItemCount) AS inven_unit,
round(avg(PriceReg),0) AS avg_price,
round(avg(LowNetPrice),0) AS avg_net_price,
round(avg((PriceReg - LowNetPrice) / PriceReg), 2) AS discount
FROM zerobase.inventory i 
WHERE 1=1 and
	PriceReg > 0
GROUP BY 1,2

2. 신상품 출시

-- NewReleaseFlag : 1(새로 출시된 다른 SKU가 있음) , 0 (이 상품이 최신 SKU임)
-- ReleaseYear : 입고연도

-- 새로 입고된 적이 있는 상품들
SELECT SKU_number , count(DISTINCT New_Release_Flag) AS release_cnt
FROM zerobase.inventory i 
GROUP BY SKU_number 
HAVING release_cnt > 1
ORDER BY SKU_number 

3. Aging

SELECT min(ReleaseYear), max(ReleaseYear)
FROM zerobase.inventory i 

-- Release Year이 0인 행은 제외하고 보기
SELECT DISTINCT ReleaseYear
FROM zerobase.inventory i 
WHERE ReleaseYear >0

-- 현재가 2019년이라고 가정, 평균 몇 년 동안 가지고 있었는지 (Aging이 몇인지)
SELECT  File_Type , SoldFlag ,
		avg(ReleaseYear) AS avg_year ,
		avg(2019 - ReleaseYear ) AS avg_aging
FROM zerobase.inventory i 
WHERE ReleaseYear >0
AND New_Release_Flag  = 0
GROUP BY 1,2
ORDER BY avg_aging DESC

-- 오래된 상품일 수록 악성 재고로 남고 있음을 확인함

---

문법

CASE WHEN

• 조건문을 1개 이상 활용하여 결과값으로 새로운 컬럼을 생성함

1) 조건 대상 컬럼이 1개일 때

예를 들어서,

END as 로 새 컬럼명을 지정함

2) 조건 대상 컬럼이 2개 이상일 때 (+조건값이 명확하지 않을 때, 여러 조건을 걸고 싶을 때)

예를 들어서,

실제 사용 예시

-- case when (1)
SELECT 
	CASE File_Type 
		WHEN 'Historical' THEN '악성재고'
		WHEN 'Active' THEN '활성재고'
		ELSE '기타' -- 현재 데이터셋에선 기타가 발생할 여지는 X
	END AS File_Type_2
	, sum(ItemCount) AS inven_unit,
	sum(SoldCount) AS sold_unit
FROM zerobase.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 zerobase.inventory i
GROUP BY 1

WITH절 vs FROM절 서브쿼리

FROM절 서브쿼리

실제 사용 예시

-- File_Type별 데이터 구조화
-- 각 File_Type_2가 전체 재고에서 차지하는 비중 구하기
-- row 수는 유지하되, 새로운 컬럼을 추가하기 위해 window function 활용
SELECT  File_Type_2,
		inven_unit,
		round(inven_unit / sum(inven_unit) over(), 3) AS pct
		-- sum(inven_unit) over() AS total_inven_unit
FROM
(
	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 zerobase.inventory i
	GROUP BY 1
	ORDER BY 1
) sub

WITH 절

• 주의사항 ! WITH로 만든 임시 테이블은 메모리를 차지함. (전체 쿼리가 끝나면 메모리에서 내려가지만, 임시 테이블을 사용할 때에는 당연히 메모리를 차지하기 때문에 리소스적으로 주의가 필요하다고 함)

• 장점 : 여러 쿼리를 JOIN, UNION 시켜야할 경우 유용함!
  예를 들어서,
  

실제 사용 예시 (UNION ALL 활용해서 Total 행 추가하기)

-- 위의 From 서브쿼리에 총 합계에 대한 행도 추가하고 싶음!


WITH sub AS
(
	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 zerobase.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

---