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

1. 전체 흐름 요약

1. Step 1: sku_1이라는 임시 집계 테이블 만들기
   • 각 sku_number별 file_type, inven_unit, sold_unit 계산
   • 예) Active/Historical + soldflag 조합으로 “1. Active”, “2. Semi-Active”, “3. Historical”, “Others” 분류
2. Step 2: sku_2라는 임시 테이블에서 추가 연산
   • MOC(Month of coverage) = inven_unit / (sold_unit / 6) (월 평균 판매량을 sold_unit/6으로 가정)
   • percent_rank()를 이용해 inven_unit 상위 퍼센트(pct)를 구함
3. Step 3: 최종 CASE WHEN 구문으로 sku_grade 결정
   • file_type_2가 “1. Active”이면서 pct < 10이면 ‘S’
   • file_type_2가 “1. Active”이면서 pct < 50이면 ‘A’, else ‘B’
   • file_type_2가 “2. Semi-Active”이면서 MOC 조건 등에 따라 ‘C’, ‘D’, ‘E’
   • 그 밖에는 ‘F’
4. Step 4: 결과를 CREATE TABLE ... AS SELECT(CTAS) 형태로 실제 물리 테이블(Inventory.sku_grade) 생성 후, SELECT로 확인

---

2. 구문별 상세 해설

2-1. Step 1: sku_1 임시 결과 (WITH 구문)

sql
코드 복사
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.Inventory i
    GROUP BY 1, 2
    ORDER BY 1, 2
)
SELECT * FROM sku_1;

• CASE: file_type와 soldflag 조합을 기반으로 “1. Active”, “2. Semi-Active”, “3. Historical” 등을 부여
• SUM(itemcount) AS inven_unit: SKU별 재고 합계
• SUM(soldcount) AS sold_unit: SKU별 판매 합계
• 결과적으로 sku_1 임시 테이블에는 (sku_number, file_type_2, inven_unit, sold_unit)가 모이게 됨.

2-2. Step 2: sku_2에서 추가 계산

sql
코드 복사
sku_2 AS (
    SELECT
        sku_number,
        file_type_2,
        inven_unit,
        sold_unit,
        -- MOC: 재고 보유량 / 월평균 판매
        inven_unit / IF(sold_unit=0, NULL, sold_unit / 6) AS MOC,
        -- inven_unit 상위 퍼센트: PERCENT_RANK() OVER (PARTITION BY file_type_2 ORDER BY inven_unit DESC)*100
        percent_rank() OVER (
            PARTITION BY file_type_2
            ORDER BY inven_unit DESC
        ) * 100 AS pct
    FROM sku_1
)
SELECT * FROM sku_2;

• MOC(Month of coverage) = inven_unit / (sold_unit / 6)
  • 여기서는 sold_unit/6을 “월평균 판매량”이라고 가정(즉, 6개월 동안의 판매수 sold_unit을 6으로 나눠 월 판매 추정).
  • IF(sold_unit=0, NULL, ...): 판매가 전혀 없으면 NULL로 처리(무한대 or 계산 불가로 간주).
• percent_rank(): 동일한 file_type_2 그룹 내에서 inven_unit DESC 기준 상위 몇 퍼센트인지 계산
  • 0.0 ~ 1.0 사이 값으로 나오므로 100 하여 실제 퍼센티지(pct)로 변환

2-3. Step 3: 최종 CASE WHEN으로 sku_grade 부여

sql
코드 복사
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 MOC < 60 THEN 'C'
        WHEN file_type_2 = '2. Semi-Active' AND MOC < 60               THEN 'D'
        WHEN file_type_2 = '2. Semi-Active'                            THEN 'E'
        ELSE 'F'
    END AS SKU_Grade
FROM sku_2;

• Active(S, A, B) 구분
  • pct < 10 → S, pct < 50 → A, 그 외 B
• Semi-Active(C, D, E) 구분
  • pct <= 50 AND MOC < 60 → C
  • pct > 50 AND MOC < 60 → D
  • 그 외 → E
• 나머지 전부 → F

2-4. Step 4: 물리 테이블로 CREATE & INSERT

sql
코드 복사
CREATE TABLE Inventory.sku_grade AS
WITH sku_1 AS (
  ...
),
sku_2 AS (
  ...
)
SELECT
    sku_number,
    file_type_2,
    CASE
        ...
    END AS SKU_Grade
FROM sku_2;

• 최종적으로 CREATE TABLE ... AS SELECT(CTAS) 구문으로 Inventory.sku_grade 테이블 생성
• 그 후 SELECT file_type_2, sku_grade, COUNT(DISTINCT sku_number) ... 하면 등급별 SKU 수를 확인 가능

---

3. 핵심 포인트 정리

1. 중간 집계 & 로직 분리
   • WITH sku_1에서 먼저 file_type_2, inven_unit, sold_unit 등을 집계
   • WITH sku_2에서 MOC, pct 계산
   • 최종 SELECT에서 CASE WHEN으로 등급 분류
   • 쿼리가 여러 단계로 나뉘어 가독성이 좋아짐
2. percent_rank() 함수 활용
   • PARTITION BY file_type_2 ORDER BY inven_unit DESC
   • 각 file_type_2 그룹 내에서 재고량이 어느 퍼센트 지점인지 판단할 수 있음
   • Low ~ High 재고의 구간을 설정하기 용이
3. Month of Coverage(MOC) 가정
   • inven_unit / (sold_unit / 6) : 6개월치 판매수로부터 월평균 판매량 추정
   • 60개월 미만이면 5년 이내 소진 가능하다고 보는 식
4. CASE WHEN 분류 기준
   • file_type_2 = '1. Active' → S, A, B (재고 상위 10%, 50%, 그 외)
   • file_type_2 = '2. Semi-Active' → MOC와 pct 조건으로 C, D, E
   • 나머지 F(= Historical, etc.)

---

4. 실제 활용 시 주의사항

1. 데이터 스케일
   • CREATE TABLE ... AS SELECT는 대용량 테이블 생성 시 리소스 많이 사용
   • 배치(ETL) 혹은 파이프라인에서 스케줄링하여 업데이트 필요
2. sold_unit=0 일때
   • 여기서는 NULL로 처리하여 MOC 계산 불가로 둠
   • 실제 비즈니스 로직에서는 무한대로 볼지, 특정 방식으로 처리할지 결정 필요
3. percent_rank() vs. ntile()
   • 만약 정확히 상위 10% 구간, 10~50% 구간을 나누려면 NTILE(10) 분할도 가능
   • percent_rank()는 분위로 자연스럽게 분포를 보고 싶을 때 쓸 수 있음

---

5. 결론

이 로직을 통해 SKU 재고를 “Active / Semi-Active / Historical”로 우선 분류하고,

각 그룹 내에서 재고량 percentile(pct)와 Month of coverage(MOC) 기준으로

S, A, B, C, D, E, F 등 세분화된 등급을 생성할 수 있습니다.

• 코드 구조:
  • WITH 절로 중간 테이블(sku_1, sku_2) 생성
  • 최종 CASE WHEN으로 새 컬럼(sku_grade) 분류
  • CREATE TABLE AS로 결과 테이블 생성
• 가독성:
  • 단일 쿼리에 모든 로직을 넣기보다, 단계별로 나눠서 관리
  • 중간 계산(집계)과 최종 분류 로직을 분리해 유지보수 용이

분류가 끝나면 이 테이블(Inventory.sku_grade)을 기반으로 SKU별 재고 전략(발주, 프로모션, 폐기 등)을 수립할 수 있습니다.