10주 4일차 TIL 정리

10주 4일차에는 rank와 case문 등을 활용하여 순위를 활용한 피벗 생성 실습을 위주로 PostgreSQL수업을 수강했다.

---

※ 제품의 매출기준 분석 ※

---

1. 활용할 데이터를 선택해 CTE 테이블(with절) 만들기

with cte_products_sale as (
select o.order_id , o.customer_id , o.order_date 
     , to_char(o.order_date,'yyyy') as year
     , to_char(o.order_date,'q') as quarter
     , to_char(o.order_date,'mm') as month 
     , to_char(o.order_date,'dd') as day 
     , od.unit_price as 판매단가 , od.quantity , od.discount
     , od.unit_price * od.quantity * (1-od.discount) as sales
     , c.category_id , c.category_name 
     , p.product_id , p.product_name 
     , p.unit_price as "마스터단가"
     , p.discontinued as "단종여부"
     , s.supplier_id , s.company_name , s.country , s.city 
from northwind.products p 
   , northwind.categories c 
   , northwind.orders o 
   , northwind.order_details od 
   , northwind.suppliers s 
where c.category_id = p.category_id and s.supplier_id = p.supplier_id 
  and p.product_id = od.product_id and od.order_id = o.order_id
)

northwind 데이터셋에서 product별 매출에 연관하여 분석을 진행해보기 위해 다음과 같이 테이블과 컬럼을 선택해줬다.

 

TABLES = { products,categories,orders,order_details,suppliers }

 

COLUMNS: products = { product_id, product_name, unit_price ( '마스터단가' ), discounted ( '단종여부' ) }

                    categories= { category_id, category_name }

                    orders = { order_id, customer_id, order_date, year, quarter, month, day }

                    order_details = { unit_price ( '판매단가' ), quantity, discount, sales }

                    suppliers = { supplier_id, company_name, country, city }

 

 

2. 제품별 매출액, 매출순위와 비율 구하기

with cte_products_sale as (

	/* (생략) */
        
)
select max(category_name), product_id, max(product_name) 
     , max(company_name), max(country), sum(sales)
     , rank() over (order by sum(sales) desc) as 순위
     , round((sum(sales)/sum(sum(sales)) over()*100)::numeric,2) || '%' as 비율
from cte_products_sale
group by 2

여기서 밑에서 세 번째 줄의 다음 코드를 살펴보면,

round((sum(sales)/sum(sum(sales)) over()*100)::numeric,2) || '%' as 비율

1. product id 별 매출합 구하기 : sum(sales) 

2. 전체 매출합 구하기: sum(sales) over ()

3. product id별 매출합이 전체에서 차지하는 비율 구하기: sum(sales) / sum(sales) over ()

4. 백분율로 표시하기 위해 100 곱하기: sum(sales) / sum(sales) over () * 100

5. float8형태를 numeric으로 변환하고(round적용 위함), round로 소수점 2자리까지 표시하게 변경:

    ( sum(sales) / sum(sales) over () * 100 ) :: numeric

                                            ↓

    round( ( sum(sales) / sum(sales) over () * 100 ) :: numeric, 2 )

6. 퍼센트 붙이기: round( ( sum(sales) / sum(sales) over () * 100 ) :: numeric, 2 ) || '%'

 

다음과 같이 형성된 것이다.

 

결과를 확인하고 이 또한 cte로 저장한다.

-- cte테이블 만들기
with cte_products_sale as (

	/* (생략) */
        
)
, cte_prod_sales as(
-- 1. 제품별 매출액 매출순위와 비율 구하기
select max(category_name), product_id, max(product_name) 
     , max(company_name), max(country), sum(sales)
     , rank() over (order by sum(sales) desc) as 순위
     , round((sum(sales)/sum(sum(sales)) over()*100)::numeric,2) || '%' as 비율
from cte_products_sale
group by 2
)

 

3. 카테고리별, 제품별 매출순위와 비율 구하기

-- cte테이블 만들기
with cte_products_sale as (

	/* (생략) */
        
)
, cte_prod_sales as(

	/* (생략) */
        
)
-- 2. 카테고리별, 제품별 매출순위와 비율 구하기
select category_name, product_id, max(product_name) 
     , max(company_name), max(country), sum(sales)
     , rank() over (partition by category_name order by sum(sales) desc) as 순위
     , round((sum(sales)/sum(sum(sales)) over(partition by category_name)*100)::numeric,2) || '%' as 비율
from cte_products_sale
group by 1,2
order by 1,7

* 1번에서 만든 cte절을 활용하므로, 2번의 코드 없이 cte_products_sale 절에 바로 해당 코드를 붙여도 문제없다.

 

---

※ ABC 분석 ※

---

1. 구간 정의

- A구간: 70% 구간의 수익을 발생시키는 구간 (누계가 70%이하)

- B구간: A구간이 아닌 동시에 90%의 수익을 발생시키는 구간 (누계가 70% 초과 90% 이하)

- C구간: A, B구간에 포함되지 않는 구간(누계가 90% 초과 100% 이하)

 

2. cte 테이블 가져오기

with cte_products_sale as (
select o.order_id , o.customer_id , o.order_date 
     , to_char(o.order_date,'yyyy') as year
     , to_char(o.order_date,'q') as quarter
     , to_char(o.order_date,'mm') as month 
     , to_char(o.order_date,'dd') as day 
     , od.unit_price as 판매단가 , od.quantity , od.discount
     , od.unit_price * od.quantity * (1-od.discount) as sales
     , c.category_id , c.category_name 
     , p.product_id , p.product_name 
     , p.unit_price as "마스터단가"
     , p.discontinued as "단종여부"
     , s.supplier_id , s.company_name , s.country , s.city 
from northwind.products p 
   , northwind.categories c 
   , northwind.orders o 
   , northwind.order_details od 
   , northwind.suppliers s 
where c.category_id = p.category_id and s.supplier_id = p.supplier_id 
  and p.product_id = od.product_id and od.order_id = o.order_id
)

 

3. 제품별 매출액, 매출순위와 비율 구하기

-- cte테이블 만들기
with cte_products_sale as (

	/* (생략) */
        
)
-- ABC분석하기
-- 1. 제품별 매출액 구하기
, cte_amount as(
select product_id, product_name , sum(sales) as 매출액
from cte_products_sale
group by 1,2
order by 1
)

 

 

4. 구성비 만들기

-- cte테이블 만들기
with cte_products_sale as (

	/* (생략) */
        
)
-- ABC분석하기
-- 1. 제품별 매출액 구하기
, cte_amount as(

	/* (생략) */
        
)
-- 2. 구성비
select *, sum(매출액) over() as 전체매출액
        , 매출액 / sum(매출액) over() * 100 as 구성비
from cte_amount

 

 

마찬가지로 cte테이블에 담아준다.

-- cte테이블 만들기
with cte_products_sale as (

	/* (생략) */
        
)
-- ABC분석하기
-- 1. 제품별 매출액 구하기
, cte_amount as(

	/* (생략) */
        
)
-- 2. 구성비
, cte_ratio as(
select *, sum(매출액) over() as 전체매출액
        , 매출액 / sum(매출액) over() * 100 as 구성비
from cte_amount
)

 

5. 구성비 누계 계산하기

-- cte테이블 만들기
with cte_products_sale as (

	/* (생략) */
        
)
-- ABC분석하기
-- 1. 제품별 매출액 구하기
, cte_amount as(

	/* (생략) */
        
)
-- 2. 구성비
, cte_ratio as(

	/* (생략) */
        
)
-- 3. 구성비 누계
, cte_ratio_agg as (
select *
     , sum(구성비) over (order by 구성비 desc) as 구성비누계
from cte_ratio
order by 구성비 desc
)

 

마찬가지로 cte 테이블에 담아준다.

-- cte테이블 만들기
with cte_products_sale as (

	/* (생략) */
        
)
-- ABC분석하기
-- 1. 제품별 매출액 구하기
, cte_amount as(

	/* (생략) */
        
)
-- 2. 구성비
, cte_ratio as(

	/* (생략) */
        
)
-- 3. 구성비 누계
, cte_ratio_agg as (
select *
     , sum(구성비) over (order by 구성비 desc) as 구성비누계
from cte_ratio
order by 구성비 desc
)

 

6. case문으로 등급 매기기

-- cte테이블 만들기
with cte_products_sale as (

	/* (생략) */
        
)
-- ABC분석하기
-- 1. 제품별 매출액 구하기
, cte_amount as(

	/* (생략) */
        
)
-- 2. 구성비
, cte_ratio as(

	/* (생략) */
        
)
-- 3. 구성비 누계

	/* (생략) */
        
)
-- 4. 등급
select *
     , case when 구성비누계 <= 70 then 'A'
            when 구성비누계 <= 90 then 'B'
            else 'C'
            end as 등급
from cte_ratio_agg
order by 구성비 desc;

 

---

※ case 응용 - pivot으로 카테고리별 매출 1, 2, 3위 제품 산정 ※

---

 

1. cte 테이블과 '카테고리별, 제품별 매출순위 및 비율' 가져오기 (cte_cat_sales)

* 제일 상단의 '제품의 매출기준 분석' 에서 진행했던 것과 동일함

↓                                                                     

, cte_cat_sales as(

-- 카테고리별, 제품별 매출순위와 비율 구하기

select category_name, product_id, product_name , company_name, country, sum(sales)

, rank() over (partition by category_name order by sum(sales) desc) as 순위, round((sum(sales)/sum(sum(sales)) over(partition by category_name)*100)::numeric,2) || '%' as 비율

from cte_products_sale

group by 1,2,3,4,5

order by 1,7

)

가져오기

with cte_products_sale as (
select o.order_id , o.customer_id , o.order_date 
     , to_char(o.order_date,'yyyy') as year
     , to_char(o.order_date,'q') as quarter
     , to_char(o.order_date,'mm') as month 
     , to_char(o.order_date,'dd') as day 
     , od.unit_price as 판매단가 , od.quantity , od.discount
     , od.unit_price * od.quantity * (1-od.discount) as sales
     , c.category_id , c.category_name 
     , p.product_id , p.product_name 
     , p.unit_price as "마스터단가"
     , p.discontinued as "단종여부"
     , s.supplier_id , s.company_name , s.country , s.city 
from northwind.products p 
   , northwind.categories c 
   , northwind.orders o 
   , northwind.order_details od 
   , northwind.suppliers s 
where c.category_id = p.category_id and s.supplier_id = p.supplier_id 
  and p.product_id = od.product_id and od.order_id = o.order_id
)
, cte_cat_sales as(
-- 카테고리별, 제품별 매출순위와 비율 구하기
select category_name, product_id, product_name , company_name, country, sum(sales)
     , rank() over (partition by category_name order by sum(sales) desc) as 순위
     , round((sum(sales)/sum(sum(sales)) over(partition by category_name)*100)::numeric,2) || '%' as 비율
from cte_products_sale
group by 1,2,3,4,5
order by 1,7
)

 

2. 카테고리별 매출 순위가 상위 3등 이내인 제품을 조건을 걸어 추출

with cte_products_sale as (

	/* (생략) */
	
)
-- 카테고리별, 제품별 매출순위와 비율 구하기
, cte_cat_sales as(


	/* (생략) */
	
)
-- 1. 카테고리별 매출 상위 3개 제품 추출
select *
from cte_cat_sales
where 순위 <=3

 

cte 테이블에 추가해준다.

with cte_products_sale as (

	/* (생략) */

)
, cte_cat_top3 as (
-- 1. 카테고리별 매출 상위 3개 제품 추출
select *
from cte_cat_sales
where 순위 <=3
)

 

3. case문을 활용한 pivot 생성

with cte_products_sale as (

	/* (생략) */
	
), cte_cat_top3 as (
-- 1. 카테고리별 매출 상위 3개 제품 추출

	/* (생략) */
	
),
cte_cte_ranks as (
-- 2. case문으로 피벗 생성
select category_name
	   ,max(case when 순위 = 1 then product_name end) as "1위"
	   ,max(case when 순위 = 2 then product_name end) as "2위"
	   ,max(case when 순위 = 3 then product_name end) as "3위"
from cte_cat_top3
group by 1

 

 

---

※ pivot 응용 - 순위가 행에, 카테고리명이 열에 오게 설정 (+ null 처리) ※

---

- case문을 활용해 pivot을 category 기준으로 생성한다

with cte_products_sale as (

	/* (생략) */
	
)
순위가 행에 오고, 카테고리명이 열이 오도록 만들기
*/
select 순위
      ,coalesce (max(case when category_name = 'Beverages' then product_name end),'NO VAVLUE') as Beverages
      ,coalesce (max(case when category_name = 'Condiments' then product_name end),'NO VAVLUE') as Condiments
      ,coalesce (max(case when category_name = 'Confections' then product_name end),'NO VAVLUE') as Confections
      ,coalesce (max(case when category_name = 'Dairy Products' then product_name end),'NO VAVLUE') as "Daily Products"
      ,coalesce (max(case when category_name = 'Grains/Cereals' then product_name end),'NO VAVLUE') as "Grains/Cereals"
      ,coalesce (max(case when category_name = 'Meat/Poultry' then product_name end),'NO VAVLUE') as "Meat/Poultry"
      ,coalesce (max(case when category_name = 'Produce' then product_name end),'NO VAVLUE') as Produce
      ,coalesce (max(case when category_name = 'Seafood' then product_name end),'NO VAVLUE') as Seafood
from cte_cat_sales
group by 1
order by 1

 

프로세스

1. cte_cat_sales를 활용, 순위를 가져온다.

2. 1에서 case조건을 활용, 해당 카테고리에 속하는 정보만 가져오도록 1줄짜리 case문을 작성한다.

3. coalesce를 활용해 null을 처리한다.