스터디노트 45일차

과제8 실습

dBeaver inventory 테이블에서 실습
-Case When
-With절
-Subquery

-- 100 -> 70 : (100-70) / 100 = 30% 할인
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 1=1
AND PriceReg > 0
GROUP BY 1,2
;

-- NewReleaseFlag : 1 > 최신 신규 Sku출시 / 0> 신규 Sku 더이상 X , ReleaseYear : 입고년도
SELECT SKU_number , count(DISTINCT New_Release_Flag) cnt
FROM inventory i
GROUP BY SKU_number
HAVING cnt > 1
ORDER BY SKU_number
;

SELECT *
FROM inventory i
WHERE SKU_number = 58313
;

-- 현재가 2019년이라고 가정 , 평균 몇년의 재고 Aging
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
;

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

-- case when (1)
SELECT
CASE File_Type
WHEN 'Historical' THEN '악성재고'
WHEN 'Active' THEN '활성재고'
ELSE '기타'
END File_Type_2
, sum(ItemCount) inven_unit
, sum(SoldCount) sold_unit
FROM inventory i
GROUP BY 1
;

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

-- File_Type별 데이터 구조화
-- Group by : 기준 컬럼으로 집계하여 Row수가 줄어듬
-- Window Function : Row 수가 그대로 + 새로운 컬럼에 집계값을 추가해서 보여줌
SELECT File_Type_2
, inven_unit
, round(inven_unit / sum(inven_unit) over() * 100, 1) pct
, sum(inven_unit) over() total_inven_unit
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 File_Type_2
, sum(ItemCount) inven_unit
, sum(SoldCount) sold_unit
FROM inventory i
GROUP BY 1
ORDER BY 1
) sub
;

-- 총 합계도 추가 해주고 싶을 때
-- With절 & Union all 활용

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