[ 유데미 스타터스 ] 48일차 TIL 프로젝트 기반 SQL 실전 트레이닝 - 제품/카테고리 매출 지표 분석, ABC 분석, 피벗 테이블

1. SQL 실습

1.1 제품/카테고리 매출 지표 분석

📌 CTE 테이블 만들기

- orders : 주문번호, 고객번호, 주문일, 연, 월, 일 분기

- order_details : 제품번호, 판매단가, 수량, 할인율, 매출액

- categories : 카테고리ID, 카테고리명

- products : 제품명, 마스터단가, 단종여부

- suppliers : 공급자ID, 공급자명, 국가, 도시

❗ od.unit_price는 판매될 당시의 단가이고, p.unit_price는 변하는 값임

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

 

❓ 전체 제품 매출 순위 및 매출 비율

-- 1) 매출액 계산
, cte_sales as (
select 
	category_name
    , product_id
    , product_name
    , company_name
    , country
    , sum(매출액) as 매출액
from cte_products_sale
group by 1,2,3,4,5
order by product_id)
-- 2) 순위, 전체 매출액 계산
, cte_rant_totamount as (
select *
	, rank() over(order by 매출액 desc) as 매출액순위
	, sum(매출액) over() as 전체매출액
from cte_sales
order by 매출액순위)
-- 3) 매출비율 계산
, cte_ratio as (
select *
	, 매출액/전체매출액*100 as 매출비율
from cte_rant_totamount)
-- 4) 최종 테이블
select category_name, product_id, product_name, company_name, country, 매출액, 매출액순위
	, round(매출비율::numeric,2)::varchar(10)||'%' as 매출비율
from cte_ratio
order by 매출액순위;

 

❓ 카테고리 별 제품 매출 순위 및 매출 비율

- partition by 카테고리로 카테고리 별 집계를 수행

-- 1) 매출액 계산
, cte_sales as (
select 
	category_name
    , product_id
    , product_name
    , company_name
    , country
    , sum(매출액) as 매출액
from cte_products_sale
group by 1,2,3,4,5
order by product_id)
-- 2) 순위, 전체 매출액 계산
, cte_rant_totamount as (
select *
	, rank() over(partition by category_name order by 매출액 desc) as 매출액순위
	, sum(매출액) over(partition by category_name) as 전체매출액
from cte_sales
order by 매출액순위)
-- 3) 매출비율 계산
, cte_ratio as (
select *
	, 매출액/전체매출액*100 as 매출비율
from cte_rant_totamount)
-- 4) 최종 테이블
select category_name, product_id, product_name, company_name, country, 매출액, 매출액순위
	, round(매출비율::numeric,2)::varchar(10)||'%' as 매출비율
from cte_ratio
order by 1, 매출액순위;

 

1.2 ABC 분석

- 통계적 방법에 의해 관리 대상을 A, B, C 그룹으로 나누고, 먼저 A 그룹을 최중점 관리대상으로 선정하여 관리노력을 집중함으로써 관리효과를 높이려는 분석방법

- 이러한 분석 방법은 고객관리, 재고관리, 품질관리, 상품관리 등 많은 분야에서 이용됨

① 매출액이 많은 순으로 정렬한다.

② 총매출액을 100%로 하여 제품별 백분비를 산출한다.

③ 그 누적 구성비율을 상위의 제품부터 순서대로 누적해 간다.

④ 그래프의 세로에 매출액 점유비의 누적치를, 가로축에 제품을 기입하고 제품별 누적구성비를 표시해간다.

⑤ 세로축의 70%와 90%의 누적치 해당점에서 가로선을 긋고, 그래프의 선과의 교차점에서 수직선을 긋는다.

 

❓ 제품 ABC 분석 지표 구하기

1) 제품 별 매출액

, cte_amount as (
 select product_id, product_name, sum(매출액) as 매출액
 from cte_products_sale
 group by 1,2
 order by product_id)

2) 구성비

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

 

3) 구성비 누계

, cte_ratio_agg as (
 select *
 	, sum(구성비) over(order by 구성비 desc) as 구성비누계
 from cte_ratio
 order by 구성비 desc
 )

 

4) 등급 부여

 select *
 	, case 
 		when 구성비누계<= 70 then 'A'
 		when 구성비누계<= 90 then 'B'
 		else 'C'
 	end as 등급
 from cte_ratio_agg
 order by 구성비 desc;

 

1.3 피벗하기

❓ 카테고리 별 매출 상위 3개 제품

1) 카테고리 별 매출 상위 3개 제품 가져오기

-- 1) 매출액 계산
, cte_sales as (
select category_name, product_name, sum(매출액) as 매출액
from cte_products_sale
group by 1,2
)
-- 2) 카테고리별 순위 계산
, cte_rank as (
select *
	, rank() over(partition by category_name order by 매출액 asc) as rank
from cte_sales
)
-- 3) TOP3
, cte_top3 as (
select *
from cte_rank
where rank <= 3
)

2) 테이블 피벗하기

-- 4) 피벗하기
select category_name
	, max(case when rank=1 then product_name end) as "1위"
	, max(case when rank=2 then product_name end) as "2위"
	, max(case when rank=3 then product_name end) as "3위"
from cte_top3
group by 1;

 

❓ 카테고리 별 제품 매출순위

❗  null인 데이터는 coalesce()로 공백으로 표시하기

-- 1) 매출액 계산
, cte_amount as (
select category_name, product_name, sum(매출액) as 매출액
from cte_products_sale
group by 1,2)
-- 2) 카테고리별 순위계산
, cte_rank as (
select *, rank() over(partition by category_name order by 매출액 desc) as 매출순위
from cte_amount)
-- 3) 피벗
select 매출순위
, coalesce(max(case when category_name='Beverages' then product_name end), '') as Beverages
, coalesce(max(case when category_name='Condiments' then product_name end), '') as Condiments
, coalesce(max(case when category_name='Confections' then product_name end), '') as Confections
, coalesce(max(case when category_name='Dairy Products' then product_name end), '') as DairyProducts
, coalesce(max(case when category_name='Grains/Cereals' then product_name end), '') as GrainsCereals
, coalesce(max(case when category_name='Meat/Poultry' then product_name end), '') as MeatPoultry
, coalesce(max(case when category_name='Produce' then product_name end), '') as Produce
, coalesce(max(case when category_name='Seafood' then product_name end), '') as Seafood
from cte_rank
group by 매출순위
order by 매출순위;

 

2. 개인 과제

📌 제품/카테고리 매출 지표 분석 (총 판매 수량을 기준으로)

1. 전체 판매수량 top 10

2. 국가별 판매수량 top 5

3. 1997년 분기별 판매수량 top 10

4. 1997년 분기별 판매수량 top 10 - 순위변화

 

📌 CTE 테이블 만들기

with cte_products_quantity as (
select
	to_char( o.order_date, 'yyyy') as year
	, to_char( o.order_date, 'q') as quarter
	, to_char( o.order_date, 'yyyy-quarter') as year_quarter
	, od.quantity
	, p.product_id 
	, p.product_name
	, c.category_name 
	, s.country
	, (od.unit_price * od.quantity * (1 - od.discount)) as 매출액
from orders o, order_details od, categories c, products p, suppliers s
where o.order_id = od.order_id 
	and od.product_id = p.product_id 
	and p.category_id = c.category_id 
    and p.supplier_id = s.supplier_id
)

 

❓ 전체 판매수량 TOP 10

-- 1) 총 판매수량, 총 매출액 계산
, cte_calc as (
select product_id
	, product_name
	, category_name
	, sum(quantity) as 총판매수량
	, sum(매출액) as 총매출액
from cte_products_quantity
group by 1,2,3
order by 1
)
-- 2) 순위 계산
, cte_rank as (
select row_number() over(order by 총판매수량 desc) as rank, *
from cte_calc
order by 1
)
-- 3) TOP 10
select * from cte_rank
where rank <= 10;

 

❓ 국가별 판매수량 TOP 5

- 국가는 총 매출액이 높은 순으로 정렬

-- 1) 총 판매수량, 총 매출액 계산
, cte_calc as (
select product_name
	, country
	, sum(quantity) as 총판매수량
	, sum(매출액) as 총매출액
from cte_products_quantity
group by 1,2
order by 2
)
-- 2) 국가별 순위계산
, cte_rank as (
select *, row_number() over(partition by country order by 총판매수량 desc) as rank 
from cte_calc
)
-- 3) TOP 5
, cte_top5 as (
select * from cte_rank
where rank <= 5
)
-- 4) 피벗하기
, cte_pivot as (
select country
	, sum(총매출액) as 국가총매출액
	, max(case when rank=1 then product_name end) as "rank1"
	, max(case when rank=2 then product_name end) as "rank2"
	, max(case when rank=3 then product_name end) as "rank3"
	, max(case when rank=4 then product_name end) as "rank4"
	, max(case when rank=5 then product_name end) as "rank5"
from cte_top5
group by country
order by 국가총매출액 desc
)
-- 5) 테이블 조회하기
select country, rank1, rank2, rank3, rank4, rank5
from cte_pivot;

 

❓ 1997년 분기별 판매수량 TOP 10

-- 1) 총 판매수량 계산
, cte_calc as (
select year, quarter
	, product_name
	, sum(quantity) as 총판매수량
from cte_products_quantity
where year='1997'
group by 1,2,3
order by 1,2
)
-- 2) 순위 계산
, cte_rank as (
select row_number() over(partition by quarter order by 총판매수량 desc) as rank, *
from cte_calc
order by 1
)
-- 3) TOP 10
, cte_top10 as (
select * from cte_rank
where rank <= 10
)
-- 4) 피벗하기
select rank
	, max(case when year='1997' and quarter='1' then product_name end) as "1997-1분기"
	, max(case when year='1997' and quarter='2' then product_name end) as "1997-2분기"
	, max(case when year='1997' and quarter='3' then product_name end) as "1997-3분기"
	, max(case when year='1997' and quarter='4' then product_name end) as "1997-4분기"
from cte_top10
group by 1
order by 1;

 

❓ 1997년 분기별 판매수량 top 10 - 순위변화

1) 총 판매수량 계산

❗ 연도 지정하기 (1996-4uarter ~ 1997-4uarter)

, cte_calc as (
select year_quarter
	, product_name
	, sum(quantity) as 총판매수량
from cte_products_quantity
where year_quarter = '1996-4uarter' or year='1997'
group by 1,2
order by 1,2
)

2) 순위 계산

, cte_rank as (
select row_number() over(partition by year_quarter order by 총판매수량 desc) as rank, *
from cte_calc
order by product_name, year_quarter
)

3) 전분기 순위, 순위 변화 계산

❗ product_name을 그룹으로 지정하고, year_quarter로 정렬하여 이전 행 값을 가져와야 함

, cte_pre_rank as (
select *
	, lag(rank) over(partition by product_name order by product_name, year_quarter) as pre_rank
	, lag(rank) over(partition by product_name order by product_name, year_quarter) - rank as diff
from cte_rank
order by product_name, year_quarter
)

4) 피벗하기

select rank
	, max(case when year_quarter='1997-1uarter' then product_name end) as "1997-1분기"
	, max(case when year_quarter='1997-1uarter' then diff end) as "순위변화"
	, max(case when year_quarter='1997-2uarter' then product_name end) as "1997-2분기"
	, max(case when year_quarter='1997-2uarter' then diff end) as "순위변화"
	, max(case when year_quarter='1997-3uarter' then product_name end) as "1997-3분기"
	, max(case when year_quarter='1997-3uarter' then diff end) as "순위변화"
	, max(case when year_quarter='1997-4uarter' then product_name end) as "1997-4분기"
	, max(case when year_quarter='1997-4uarter' then diff end) as "순위변화"
from cte_pre_rank
where rank <= 10
group by 1
order by 1;