[SQL 분석][Ch5] 재고량 / 악성 재고 원인 파악

김지현·2024년 8월 3일

목록 보기

34/40

재고 수량 파악

활성 재고와 악성 재고의 재고량과 총 판매량 확인
→ 활성 재고는 계속해서 판매할 제품이라 재고량 데이터만 있다 (판매량 데이터 x)

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

Historical 재고에서 팔린 이력이 있는 재고의 DOC 확인 (여기서는 day대신 month별로 확인)
→ month_of_coverage 결과 201개월이 지나야 남은 재고를 다 팔 수 있겠다

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

재고 가격 파악

SELECT File_Type, SoldFlag
		, sum(ItemCount) inven_unit
		, round(avg(PriceReg), 1) avg_price
		, round(sum(ItemCount * PriceReg), 0) inven_cost
FROM inventory i
WHERE PriceReg > 0   -- PriceReg값이 0인 경우가 포함되는 것을 방지 (혹시 모를 음수값의 경우도)
GROUP BY 1,2
;

악성 재고 원인 파악

가격

활성 재고와 비활성 재고의 평균 판매 금액, 실 구매가, 할인률 확인
→ 활성 재고는 계속해서 needs가 있어 할인률이 높지 않고, 비활성 재고는 (재고 처리 등을 위해) 상대적으로 할인률이 높은 것으로 추정

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

신상품 출시

New_Release_Flag = 1 : 최신 sku물품 출시 / New_Release_Flag = 0 : 현재 버전이 최신
처음 입고된 상태에서 신상품이 입고된 제품 확인

SELECT SKU_number, count(DISTINCT New_Release_Flag) cnt
FROM inventory i
GROUP BY SKU_number
HAVING cnt > 1   -- 신상 미출시면 cnt = 1 / 출시했다면 2 이상의 값을 가질 것
ORDER BY SKU_number
;

위 결과에서 대표적으로 한 개의 물건에 대해 확인

SELECT *
FROM inventory i 
WHERE SKU_number = 58313
;

Aging

현재를 2019년이라고 가정(ReleaseYear max값이 2018)
- 평균 입고년도, 재고가 평균적으로 몇 년동안 있었는지

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

CASE WHEN

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

1. 조건 대상 컬럼이 1개, 조건값(when_value)이 명확할 때

-- 단일 컬럼 조건
SELECT 
	CASE File_Type
		WHEN 'Historical' THEN '악성재고'
		WHEN 'Active' THEN '활성재고'
		ELSE '기타'  -- 현재 데이터는 기타가 발생할 여지 x
	END AS File_Type2
	, sum(ItemCount) AS inven_unit
	, sum(SoldCount) AS sold_unit
FROM inventory i 
GROUP BY 1
ORDER BY 1
;

2. 조건 대상 컬럼이 복수, 조건값이 명확하지 않을 때, 여러 복합적인 조건을 걸고 싶을 때

-- 다중 컬럼 조건 >> CASE 옆에 컬럼 명시하지않고 WHEN절에 입력
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_Type2
	, sum(ItemCount) AS inven_unit
	, sum(SoldCount) AS sold_unit
FROM inventory i 
GROUP BY 1 
ORDER BY 1
;

WITH절 vs FROM절 Subquery

FROM절 Subquery

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

위 결과에서 각 inven_unit이 전체 unit의 몇 %인지 계산
→ 각 행별로 전체 inven unit 값 열을 추가하여 계산하고자 함 >> Window Function 사용

SELECT File_Type2, inven_unit
		, sum(inven_unit) over() AS total_inven_unit
		, round(inven_unit / sum(inven_unit) over() * 100, 1) AS pct
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_Type2
		, sum(ItemCount) AS inven_unit
		, sum(SoldCount) AS sold_unit
	FROM inventory i 
	GROUP BY 1
	ORDER BY 1
) sub
;

WITH절

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

위 결과의 마지막 행에 총 합계를 나타내는 Total도 추가하고자 함
→ 위에서 활용한 query를 WITH절로 임시 테이블로 생성

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_Type2
		, sum(ItemCount) AS inven_unit
		, sum(SoldCount) AS sold_unit
	FROM inventory i 
	GROUP BY 1
	ORDER BY 1
)
SELECT File_Type2
		, inven_unit
		, round(inven_unit / sum(inven_unit) OVER () * 100, 1) AS pct
FROM sub
UNION ALL
SELECT 'Total' AS File_Type2
		, sum(inven_unit) AS inven_unit
		, sum(inven_unit) / sum(inven_unit) * 100 AS pct
FROM sub
;