1. SQL 연습
1.1 상품/카테고리 판매 지표 분석
CTE 테이블 만들기
– 주문 : 주문번호, 고객번호, 주문일자, 년월일 분기
'추천 관련글,
– order_details : 상품번호, 단가, 수량, 할인율, 판매량
– 카테고리 : 카테고리 ID, 카테고리명
– 상품 : 상품명, 원가, 단종현황
– 공급자: 공급자 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 매출액순위;
카테고리별 상품 판매 순위 및 판매 비율
– 카테고리별 파티션으로 카테고리별 집계 수행
-- 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그룹을 가장 중요한 관리대상으로 우선 선정하여 관리효과를 높이기 위해 관리노력을 집중하는 분석방법.
– 고객관리, 재고관리, 품질관리, 상품관리 등 다양한 분야에서 활용되고 있는 분석기법입니다.
1) 매출 내림차순으로 정렬합니다.
② 전체 매출을 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;
카테고리별 매출순위
coalesce()를 사용하여 null 데이터를 공백으로 표시
-- 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. 총 판매량 상위 10위
2. 국가별 매출 상위 5위
3. 1997년 분기별 매출 상위 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;
국가별 상위 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년 분기별 매출 상위 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;
