[Sql분석] 물류기획

재고관리 주요지표

---

• 재고 (Inventory) : 입고량 (Inbound) - 판매량 (Outbound)
• 권장판매가 (List Price) : 택가격
• 실 판매가 (Net Price) : 할인같은 것이 들어간 실제 판매가격
• SKU (Stock Keeping Unit) : 재고 관리를 위한 최소 단위 코드
  (만약 SKU가 같다면 똑같은 종류의 상품)
• Unit Quantity : 상품의 개수
• Unit per SKU : SKU 1종 당 상품의 개수
• DOC (Day of Coverage) : 재고량 / 하루 판매량
  (우리의 재고로 며칠동안 판매가 가능한가)
• Low DOC = 재고 전환률이 높음
• High DOC = 재고 전환율이 낮음

데이터 살펴보기

---

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

• 스키마를 선택해두면 테이블 불러올때 앞에 스키마 써주지 않아도 됨

• 중복값 없는지 확인하기

• distnct로 새어준 열값과 전체 행을 개수가 동일하면 됨

SELECT count(*)
FROM (
	SELECT DISTINCT *
	FROM inventory.inventory i 
	) i2
	;

• 악성재고를 파악할 수 있는 지표인 File_Type 과 SoldFlag 확인

• Active 는 활성재고라서 SoldFlag가 Null값이고 Historical에서 SoldFlag가 0과 1로 나뉨

• ReleaseYear를 살펴보니 입고년도가 0인 컬럼이 있음

• 아래 식으로 확인해보니 활성재고로 판매되고 있는 내역까지 있고 년도가 누락된 데이터로 보임. 이런경우 1개 뿐이니 재외하고 봐도 됨

SELECT *
FROM inventory.inventory i 
WHERE ReleaseYear = 0
;

• 권장 판매가의 최소, 최대, 평균값을 보자

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

• 최소값이 0인 데이터가 있음
• 이런경우 LownetPrice를 참조해볼 수 있음 (존재하는 경우)

재고수량 파악

---

• 활정재고와 악성재고의 6개월이내 판매내역에 따른 재고량 과 총 판매량 확인

SELECT File_Type , SoldFlag 
	,sum(ItemCount) AS invent_unit
	,sum(SoldCount) AS sold_unit
FROM inventory.inventory i 
GROUP BY 1,2
ORDER BY 1,2
;

• Historical 재고에서 6개월내에 팔린 내역이 있는 재고의 DOC를 알고 싶다면?
• 이 데이터는 재고량이 많이서 월단위로 나누겠음 (Month of Coverage)

SELECT File_Type , SoldFlag 
	,sum(ItemCount) AS invent_unit
	,sum(SoldCount) AS sold_unit
	,sum(ItemCount) / (sum(SoldCount)/6) AS month_of_average
FROM inventory.inventory i 
WHERE File_Type = 'Historical'
	AND SoldFlag = 1
GROUP BY 1,2
ORDER BY 1,2
;

• 재고의 가격을 파악하고 싶다면?

SELECT File_Type , SoldFlag 
	,sum(ItemCount) AS invent_unit
	,round(avg(PriceReg)) AS avg_price
	,round(sum(ItemCount*PriceReg),0) AS inven_cost
FROM inventory.inventory i 
WHERE PriceReg > 0 -- PriceReg의 값이 0인 경우가 포함되는걸 방지
GROUP BY 1,2
;

• 실제판매가격과 할인율이 얼마나 들어갔는지 알고 싶다면?
• 100 -> 70 : (100-70) / 100 = 30% 할인

SELECT File_Type , SoldFlag 
	, sum(ItemCount) AS invent_unit
	, round(avg(PriceReg),0) AS avg_price
	, round(avg(LowNetPrice),0) AS avg_net_price
	, round(avg((PriceReg - LowNetPrice) / PriceReg)*100,1) AS discount
FROM inventory.inventory i 
WHERE PriceReg > 0
GROUP BY 1,2
;

악성재고 원인파악

• NewReleaseFlag는 1,0으로 이루어져 있음

• 1은 새로운 버전의 물품이 입고 되었다는 것이고 0은 지금 버전이 최신버전이라는 뜻

• 이런 경우 동일한 SKU의 제품이 1,0을 모두 가지고 있는 경우가 있음

• 0인 상태의 제품을 입고하고 또 최신버전의 상품을 입고했을 경우

• 한번 입고 되었다가 신상품이 다시 입고된 제품을 확인해보자

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

• 나온 결과중 특정 결과를 살펴보면

• 같은 제품임에도 업그레이드 된 버전은 Active, 구버전은 Historial 재고가 됨

• ReleaseYear를 살펴보면 가장 최근 년도가 2019

• 현재가 2019년이라고 가정, 평균 몇년의 재고 Aging이 있는지 확인

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

Case문 활용

---

1. CASE WHEN 단일컬럼 사용

SELECT 
	CASE File_Type 
		WHEN 'Historical' THEN '악성재고'
		WHEN 'Active' THEN '활성재고'
		ELSE '기타'  -- 현재 우리는 데이터 전처리과정에서 else가 필요 없다는 걸 알고 있지만
		            -- 현업에서는 새로운 타입이 생길 수도 있으니 습관적으로 else 처리 해줘야함
	END AS File_Type_2
	, sum(ItemCount) AS inven_unit
	, sum(SoldCount) AS sold_unit
FROM inventory.inventory i 
GROUP BY 1
ORDER BY 1
;

• 위 결과처럼 File_Type이라는 단일컬럼에 조건을 걸고 싶다면 CASE옆에 적어주면 됨
  

2. CASE WHEN 다중컬럼 사용

SELECT 
	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
ORDER BY 1
;

• 위와 같이 File_Type 과 SoldFlag를 같이 사용한다면 CASE옆에 아무것도 쓰면 안됨
  

File_Type별 데이터 구조화

---

이런한 결과에서 각 unit의 개수가 전체 unit의 몇 %인지 알고 싶을 때

• 위의 결과에서 전체 inventory unit 개수를 컬럼으로 함쳐서 나누면 됨

• 이때 필요한 것! Window Function

• Window Funcion 과 Group by의 차이를 다시 한번 상기시켜보면

  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 = '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
	ORDER BY 1
) sub
;

• 아까 적어준 case문을 서브쿼리로 활용

With & Unionall

---

• 만약 총 합계도 추가해주고 싶다면 With절과 Union all활용
• with으로 임시테이블을 만들어 주면 다른 테이블과 조인할 때 유용하다

WITH sub AS (
	SELECT 
		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
	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
;

• with절은 작성한것 바로아래에 select절을 넣어줘야 사용가능
• unionall은 두개의 select절이 위아래로 접착
• 컬럼명과 순서가 같아야 합쳐지므로 이름과 순서에 유의