[STARTERS 4기 TIL] #48일차(23.04.13)

---

📚 오늘의  학습

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

1️⃣ 전체 제품 매출 순위 및 매출 비율

2️⃣ 카테고리별 제품 내출 순위 및 매출 비율

📌 임시 테이블 cte_products_sale 📌

✅ orders : 주문번호, 고객번호, 주문일, 연, 월, 일, 분기
✅ order_details : 제품번호 판매단가, 수량, 할인율, 매출액
✅ categories : 카테고리ID, 카테고리명
✅ products : 제품ID, 제품명, 마스터단가, 단종여부
✅ suppliers : 공급자ID, 공급자명, 국가, 도시

with cte_products_sale as(
select o.order_id , o.customer_id , o.order_date
	 , to_char(order_date, 'YYYY') as year 
	 , to_char(order_date, 'mm') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , 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_id , 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 
)
select count(*)
from cte_products_sale;

📌 제품별 매출액 순위 📌

with cte_products_sale as(
select o.order_id , o.customer_id , o.order_date
	 , to_char(order_date, 'YYYY') as year 
	 , to_char(order_date, 'mm') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id , od.unit_price as 판매단가 , od.quantity , od.discount , (od.unit_price * od.quantity * (1-od.discount)) as amount
	 , 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_amount as (
select category_name, product_id, product_name, company_name, country
	 , sum(amount) as 매출액
from cte_products_sale
group by 1,2,3,4,5
order by product_id
)
✅ 2. 순위, 전체 매출액 계산
, cte_rank_totalamount as (
select *
	 , rank() over(order by 매출액 desc) as 매출액순위
	 , sum(매출액) over() as 전체매출
from cte_amount
)
✅ 3. 매출 비율 계산
, cte_radio as (
select *
	 , 매출액 / 전체매출 * 100 as 매출비율
from cte_rank_totalamount
)
✅ 4. 최종테이블
select category_name, product_id, product_name, company_name, country, 매출액, 매출액순위
	 ,round(매출비율::numeric ,2)::varchar(10)||'%' as 매출비율 
from cte_radio
order by 1;

 

 

ABC 분석

통계적 방법에 의해 관리대상을 A, B, C 그룹으로 나누고,

먼저 A그룹을 최중점 관리대상으로 선정하여 관리노력을 집중함으로써 관리효과를 높이려는 분석방법.

ABC분석

 

📌 ABC 테스트 📌

with cte_products_sale as(
select o.order_id , o.customer_id , o.order_date
	 , to_char(order_date, 'YYYY') as year 
	 , to_char(order_date, 'mm') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id , od.unit_price as 판매단가 , od.quantity , od.discount , (od.unit_price * od.quantity * (1-od.discount)) as amount
	 , 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_amount as (
select product_id , product_name
	 , sum(amount) 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. 등급
, cte_class as (
select *
	 , case 
	 	when 구성비누계 <= 70 then 'A'
	 	when 구성비누계 <= 90 then 'B'
	 	else 'C'
	 end as 등급
from cte_ratio_agg
order by 구성비 desc
)
✅ 최종 테이블
select * from cte_class;

 

 

Pivot

📌 카테고리별 순위를 피벗하기 📌

with cte_products_sale as(
select o.order_id , o.customer_id , o.order_date
	 , to_char(order_date, 'YYYY') as year 
	 , to_char(order_date, 'mm') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id , od.unit_price as 판매단가 , od.quantity , od.discount , (od.unit_price * od.quantity * (1-od.discount)) as amount
	 , 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_amount as (
select category_name, product_id, product_name, company_name, country
	 , sum(amount) as 매출액
from cte_products_sale
group by 1,2,3,4,5
order by product_id
)
✅ 2. 순위
, cte_rank_totalamount_category as (
select *
	 , rank() over(partition by category_name order by 매출액 desc) as 매출액순위
from cte_amount
)
✅ 3. 3위까지 뽑기
, cte_rank_top3 as (
select *
from cte_rank_totalamount_category
where 매출액순위 between 1 and 3
)
✅ 4. 피봇하기
select category_name
	 ✅ 1위 열
	 , max(case when 매출액순위 = 1 then product_name end) as "1위"
	 ✅ 2위 열
 	 , max(case when 매출액순위 = 2 then product_name end) as "2위"
	 ✅ 3위 열
 	 , max(case when 매출액순위 = 3 then product_name end) as "3위"
from cte_rank_top3
group by 1;

 

📌 매출액 순위 별로 카테고리 피벗하기 📌

with cte_products_sale as(
select o.order_id , o.customer_id , o.order_date
	 , to_char(order_date, 'YYYY') as year 
	 , to_char(order_date, 'mm') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id , od.unit_price as 판매단가 , od.quantity , od.discount , (od.unit_price * od.quantity * (1-od.discount)) as amount
	 , 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_amount as (
select category_name, product_id, product_name, company_name, country
	 , sum(amount) as 매출액
from cte_products_sale
group by 1,2,3,4,5
order by product_id
)
✅ 2. 순위
, cte_rank_totalamount_category as (
select *
	 , rank() over(partition by category_name order by 매출액 desc) as 매출액순위
from cte_amount
)
✅ 3. 피봇, null값 처리
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 "Dairy Products"
	 , coalesce(max(case when category_name = 'Grains/Cereals' then product_name end), '') as "Grains/Cereals"
	 , coalesce(max(case when category_name = 'Meat/Poultry' then product_name end), '') as "Meat/Poultry"
	 , 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_totalamount_category
group by 1
order by 1;

 

 

 

📝 오늘의 과제

1️⃣ 전체 판매 수량 탑10 cte_products_quantity
-- products : product_id, product_name,
-- order_details : quantity, unit_price, discount, 매출액
-- categories : category_name

with cte_products_quantity as (
select p.product_id , p.product_name 
	 , od.quantity , od.unit_price , od.discount , od.quantity * od.unit_price * (1-od.discount) as amount
	 , c.category_name 
from products p , order_details od , categories c 
where p.product_id = od.product_id 
  and p.category_id = c.category_id 
)
-- 1. 제품별로 그루핑
, cte_products_quantity_total as (
select product_id, product_name
	 , sum(quantity) as total_quantity 
	 , sum(amount) as total_sales 
	 , max(category_name) as category_name
from cte_products_quantity
group by product_id , product_name )
-- 2. 순위 추가 및 10위까지 추출
 , cte_products_rank as (
select rank() over (order by total_quantity desc), *
from cte_products_quantity_total
limit 10)
select * from cte_products_rank;

 

2️⃣ 국가별 판매수량 탑5 cte_country_sales
-- suppliers : country
-- products : product_id, product_name,
-- order_details : quantity

with cte_country_sales as (
select c.country , p.product_id , p.product_name 
	 , od.quantity , od.unit_price , od.discount , od.quantity * od.unit_price * (1-od.discount) as amount
from products p , order_details od , orders o, customers c 
where p.product_id = od.product_id 
  and od.order_id = o.order_id
  and o.customer_id = c.customer_id 
order by c.country)
-- 1. 국가, 제품 별로 그루핑
, cte_country_product as (
select country, product_id, product_name
	 , sum(quantity) as total_quantity
	 , sum(amount) as total_sales
from cte_country_sales
group by country, product_id, product_name
order by country, product_id
)
-- 2. 국가별 판매수량 순위 컬럼 추가
, cte_country_rank as (
select rank() over (partition by country order by total_quantity desc, total_sales desc) as rank, *
from cte_country_product)
-- 3. 피봇 진행, 국가 총 매출액 컬럼 추가
, cte_country_pivot as (
select country
	 , 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
	 , sum(total_sales) as total_total_sales
from cte_country_rank
group by country
)
-- 4. 총 매출액 순으로 정렬
, cte_country_table as (
select country, rank1, rank2, rank3, rank4, rank5
from cte_country_pivot
order by total_total_sales desc)
 select * from cte_country_table;

 

3️⃣ 1997년 분기별 판매수량 탑10 cte_quarter_sales
-- products : product_id, product_name,
-- order_details : quantity
-- orders : order_date, 연도, 분

with cte_quarter_sales as (
select p.product_id , p.product_name 
	 , od.quantity , od.unit_price , od.discount , od.quantity * od.unit_price * (1-od.discount) as amount
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'q') as quarter
from order_details od , products p , orders o 
where od.product_id = p.product_id 
  and od.order_id = o.order_id 
)
-- 1. 1997년 데이터 추출
, cte_1997_sales as (
select *
from cte_quarter_sales
where year = '1997')
-- 2. 그루핑
, cte_group as (
select year, quarter, product_name
	 , sum(quantity) as total_quantity 
	 , sum(amount) as total_amount
from cte_1997_sales
group by year, quarter, product_name
)
-- 3. 분기별 판매수량 순위 선정
, cte_1997_rank as (
select rank() over (partition by quarter order by total_quantity desc, total_amount desc) as rank
	 , *
from cte_group
)
-- 4. 피봇
, cte_1997_pivot as (
select rank 
	 , max(case when quarter = '1' then product_name end) as "1997-1분기"
	 , max(case when quarter = '2' then product_name end) as "1997-2분기"
	 , max(case when quarter = '3' then product_name end) as "1997-3분기"
	 , max(case when quarter = '4' then product_name end) as "1997-4분기"
from cte_1997_rank
group by rank
limit 10
)
select * from cte_1997_pivot;

 

4️⃣ 1997년 분기별 판매수량 순위변화 cte_quarter_sales
-- products : product_id, product_name,
-- order_details : quantity
-- orders : order_date, 연도, 분

with cte_quarter_sales as (
select p.product_id , p.product_name 
	 , od.quantity , od.unit_price , od.discount , od.quantity * od.unit_price * (1-od.discount) as amount
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'q') as quarter
from order_details od , products p , orders o 
where od.product_id = p.product_id 
  and od.order_id = o.order_id 
)
-- 1. 분기 넘버링
, cte_quarter_no as (
select *
	, case 
	when year = '1996' and quarter = '4' then 1
	when year = '1997' and quarter = '1' then 2  
	when year = '1997' and quarter = '2' then 3 
	when year = '1997' and quarter = '3' then 4 
	when year = '1997' and quarter = '4' then 5 end as no
from cte_quarter_sales
)
-- 2. 필요 분기만 추출
, cte_need_year as (
select *
from cte_quarter_no
where not no isnull 
)
-- 3. 그루핑
, cte_group as (
select no, year, quarter, product_name
	 , sum(quantity) as total_quantity
	 , sum(amount) as total_sales
from cte_need_year
group by no, year, quarter, product_name
order by no, year, quarter, product_name
)
-- 4. 순위
, cte_rank as (
select rank () over (partition by no order by total_quantity desc, total_sales desc), *
from cte_group)
-- 5. 순위변화 산출
, cte_lag as (
select *, lag(rank,1) over(partition by product_name order by no)-rank as "순위변화"
from cte_rank)
-- 6. 피벗 진행
, cte_pivot as (
select rank
	 , max(case when no = 2 then product_name end) as "1997년 1분기"
	 , max(case when no = 2 then 순위변화 end) as "순위변화"
	 , max(case when no = 3 then product_name end) as "1997년 2분기"
	 , max(case when no = 3 then 순위변화 end) as "순위변화"
	 , max(case when no = 4 then product_name end) as "1997년 3분기"
	 , max(case when no = 4 then 순위변화 end) as "순위변화"
	 , max(case when no = 5 then product_name end) as "1997년 4분기"
	 , max(case when no = 5 then 순위변화 end) as "순위변화"
from cte_lag
group by rank
order by rank
limit 10
)
select * from cte_pivot;