[STARTERS 4기 TIL] SQL 심화 #4 - WINDOW 함수, Quantity 기준 분석 (230413)
춘시기좋아
2023. 4. 13. 19:46
📖 오늘 내가 배운 것
1. 지난 과제 피드백
2. 제품/카테고리 매출 지표 분석
3. 금일 과제
4. 핵심 내용 정리
1. 지난 과제 피드백
- 말 천천히, 쿼리 결과 테이블 피피티에 넣으면 좋겠다, 결과 부분에 핵심 내용은 볼드나 글씨 진하게 하면 좋겠다!
- 근속기간이 높을수록 매출이 높나!? 너무 당연한 사실! -> 기간을 기준으로 두고 구했어야 했다.
2. 제품/카테고리 매출 지표 분석
- 전체 제품 매출 순위, 비율
- 카테고리 별 제품 순위, 비율
📌 사용할 테이블
categories
products
order
order_details
suppliers 공급업체 제품 공급하는 업체
📌 주의할 점은?
판매 당시의 uit_price? 단가는 변화
order_details와 products의 unit_price가 다르다! 시가가 있나 보다
# suppliers에서 어느 나라에서 온 제품인지 확인할 수 있다.
2.1 임시 테이블 생성
orders : 주문번호, 고객번호, 주문일, 연,월.일,분기
order_details : 제품번호 판매단가 수량 할인율 매출액
categoreis : 카테고리 id, 카테고리명
products : 제품id, 제품명, 마스터단가, 단종여부
suppliers : 공급자 id, 공급자명, 국가, 도시
orders- order_details 1:N
products - categories N:1
등등 일대다 관계가 많다!
인지하고 JOIN
/*
임시 테이블 만들기 : cte_products_sales
orders : 주문번호, 고객번호, 주문일, 연,월.일,분기
order_details : 제품번호 판매단가 수량 할인율 매출액
categoreis : 카테고리 id, 카테고리명
products : 제품id, 제품명, 마스터단가, 단종여부
suppliers : 공급자 id, 공급자명, 국가, 도시
*/
with cte_products_sales as (
select -- order
o.order_id as 주문번호
, o.customer_id as 고객번호
, o.order_date as 주문일
, to_char(order_date, 'YYYY') as 연
, to_char(order_date, 'MM') as 월
, to_char(order_date, 'dd') as 일
, to_char(order_date, 'q') as 분기
-- order_details
, od.product_id as 제품ID
, od.unit_price as 판매당시금액
, od.quantity as 수량
, od.discount as 할인율
, round( od.unit_price * od.quantity * (1-od.discount) ) as 매출액
-- categories
, c.category_id as 카테고리ID
, c.category_name as 카테고리명
--, p.product_id as 제품ID order_details와 중복!
, p.product_name as 제품명
, p.unit_price as 마스터단가
, p.discontinued as 단종여부
-- 서플라이어
, s.supplier_id as 공급자ID
, s.contact_name as 공급사
, s.country as 공급사국가
, s.city as 공급사도시
from orders o
left join order_details od ON od.order_id = o.order_id
left join products p on p.product_id = od.product_id
left join categories c on p.category_id = c.category_id
left join suppliers s ON p.supplier_id = s.supplier_id
)
2.2 제품(카테고리)별 매출 순위 및 비율
📌 작업 순서
1. 필요한 컬럼 가져오고
2. 순위 구하고
3. 비율 구하고
with cte_products_sales as (
select -- order
o.order_id as 주문번호
, o.customer_id as 고객번호
, o.order_date as 주문일
, to_char(order_date, 'YYYY') as 연
, to_char(order_date, 'MM') as 월
, to_char(order_date, 'dd') as 일
, to_char(order_date, 'q') as 분기
-- order_details
, od.product_id as 제품ID
, od.unit_price as 판매당시금액
, od.quantity as 수량
, od.discount as 할인율
, round( od.unit_price * od.quantity * (1-od.discount) ) as 매출액
-- categories
, c.category_id as 카테고리ID
, c.category_name as 카테고리명
--, p.product_id as 제품ID order_details와 중복!
, p.product_name as 제품명
, p.unit_price as 마스터단가
, p.discontinued as 단종여부
-- 서플라이어
, s.supplier_id as 공급자ID
, s.contact_name as 공급사
, s.country as 공급사국가
, s.city as 공급사도시
from orders o
left join order_details od ON od.order_id = o.order_id
left join products p on p.product_id = od.product_id
left join categories c on p.category_id = c.category_id
left join suppliers s ON p.supplier_id = s.supplier_id
)
-- 1. 매출액 계산 (그룹핑)
, cte_amount as (
select 카테고리명
, 제품ID
, 제품명
, 공급사
, 공급사국가
, sum(매출액) 매출액
from cte_products_sales
group by 1,2,3,4,5
)
-- 2. 순위
, cte_rank_totamount as (
select *
, rank() over (order by 매출액 DESC) as 순위
, sum(매출액) over() as 전체매출액
from cte_amount
), cte_ratio as (
-- 3. 매출비율 계산
select *
, (매출액 / 전체매출액 * 100) as 매출비율
from cte_rank_totamount
)
-- 4. 최종 테이블
select 카테고리명, 제품ID, 제품명, 공급사, 공급사국가, 매출액, 순위
, round(매출비율::numeric ,2)::varchar(10)||'%' as 매출비율
from cte_ratio;
프랑스의 뭐시기 공급사가 제공하는 ~한 카테고리의 제품이 11%
with cte_products_sales as (
select -- order
o.order_id as 주문번호
, o.customer_id as 고객번호
, o.order_date as 주문일
, to_char(order_date, 'YYYY') as 연
, to_char(order_date, 'MM') as 월
, to_char(order_date, 'dd') as 일
, to_char(order_date, 'q') as 분기
-- order_details
, od.product_id as 제품ID
, od.unit_price as 판매당시금액
, od.quantity as 수량
, od.discount as 할인율
, round( od.unit_price * od.quantity * (1-od.discount) ) as 매출액
-- categories
, c.category_id as 카테고리ID
, c.category_name as 카테고리명
--, p.product_id as 제품ID order_details와 중복!
, p.product_name as 제품명
, p.unit_price as 마스터단가
, p.discontinued as 단종여부
-- 서플라이어
, s.supplier_id as 공급자ID
, s.contact_name as 공급사
, s.country as 공급사국가
, s.city as 공급사도시
from orders o
left join order_details od ON od.order_id = o.order_id
left join products p on p.product_id = od.product_id
left join categories c on p.category_id = c.category_id
left join suppliers s ON p.supplier_id = s.supplier_id
)
-- 1. 매출액 계산 (그룹핑)
, cte_products_sales2 as (
select 카테고리명
, 제품ID
, 제품명
, 공급사
, 공급사국가
, sum(매출액) 매출액
from cte_products_sales
group by 1,2,3,4,5
)
-- 2. 순위, 전체매출액계산
, rankk as (
select *
, rank() over (partition by 카테고리명 order by 매출액 DESC) as 매출액순위
, sum(매출액) over(partition by 카테고리명) as 전체매출액
from cte_products_sales2
), cte_ratio as(
-- 3. 매출비율 계산
select *, (매출액/전체매출액) * 100 as 매출비율
from rankk
)
-- 최종테이블
select 카테고리명, 제품ID, 제품명, 공급사, 공급사국가, 매출액, 매출액순위
, ROUND(매출비율::numeric ,2)::varchar(10)||'%' as 매출비율
from cte_ratio
order by 1, 7;
프랑스의 뭐시기 공급사가 제공하는 ~한 카테고리의 제품이 11%
그럼 얘는 왜 이렇게 비싼 걸까? 가설을 세워보자
1. 단가가 비싸서
2. 판매량 높아서
3. 트랜드여서? (정량적으로 가능? - 시계열(계절성))
4. 판매 기간이 길어서
# remind : group by로 묶을 때는 묶이는 컬럼 외에는 집계 함수만 존재할 수 있다!
때문에 추가적인 window 함수 등을 적용하려면 서브쿼리 사용하는 게 좋음!
+++ case when 사용한 pivot의 경우 max, min 등으로 각 열에 해당하는 행 데이터 한 개만 받아올 수 있다.
2.3 ABC 분석
제품 분석을 하게 되면 우리가 중점적으로 관리를 해줘야 하는 제품을 확인하고 맞춤화 전략을 펼쳐볼 수 있겠지?
/*ABC 분석!*/
-- 1. 제품별 매출액
with cte_products_sales as (
select -- order
o.order_id as 주문번호
, o.customer_id as 고객번호
, o.order_date as 주문일
, to_char(order_date, 'YYYY') as 연
, to_char(order_date, 'MM') as 월
, to_char(order_date, 'dd') as 일
, to_char(order_date, 'q') as 분기
-- order_details
, od.product_id as 제품ID
, od.unit_price as 판매당시금액
, od.quantity as 수량
, od.discount as 할인율
, round( od.unit_price * od.quantity * (1-od.discount) ) as 매출액
-- categories
, c.category_id as 카테고리ID
, c.category_name as 카테고리명
--, p.product_id as 제품ID order_details와 중복!
, p.product_name as 제품명
, p.unit_price as 마스터단가
, p.discontinued as 단종여부
-- 서플라이어
, s.supplier_id as 공급자ID
, s.contact_name as 공급사
, s.country as 공급사국가
, s.city as 공급사도시
from orders o
left join order_details od ON od.order_id = o.order_id
left join products p on p.product_id = od.product_id
left join categories c on p.category_id = c.category_id
left join suppliers s ON p.supplier_id = s.supplier_id
)
-- 1. 제품별 매출액 구하기
, cte_amount as(
select 제품ID, 제품명, sum(매출액) as 매출액
from cte_products_sales
group by 1,2
order by 제품ID
)
-- 2. 구성비
, cte_ratio as (
select *
, sum(매출액) over() as 전체매출액
, 매출액/sum(매출액) over() * 100 as 구성비
from cte_amount
), cte_ration_agg as (
-- 3. 구성비 누계 (주의점 : 1등 부터 매출이 높은 순으로 정렬해야 함)
select *
, sum(구성비) over(order by 구성비 desc) as 구성비누계
from cte_ratio
order by 구성비 desc
) -- 70 %까지를 A그룹, 90 %까지를 B그룹, 그 이상을 C그룹
select *
, case
when 구성비누계 <= 70 then 'A'
when 구성비누계 <= 90 then 'B'
else 'C'
end 등급
from cte_ration_agg
order by 구성비 DESC;
2.4 Pivot Table 1
매출 상위 3개 제품 가져온다! 제품별 매출액!
/*Pivot Table*/
--1. 매출 상위 3개 제품 가져온다 -> 피벗한다.
-- 1. 제품별 매출액
with cte_products_sales as (
select -- order
o.order_id as 주문번호
, o.customer_id as 고객번호
, o.order_date as 주문일
, to_char(order_date, 'YYYY') as 연
, to_char(order_date, 'MM') as 월
, to_char(order_date, 'dd') as 일
, to_char(order_date, 'q') as 분기
-- order_details
, od.product_id as 제품ID
, od.unit_price as 판매당시금액
, od.quantity as 수량
, od.discount as 할인율
, round( od.unit_price * od.quantity * (1-od.discount) ) as 매출액
-- categories
, c.category_id as 카테고리ID
, c.category_name as 카테고리명
--, p.product_id as 제품ID order_details와 중복!
, p.product_name as 제품명
, p.unit_price as 마스터단가
, p.discontinued as 단종여부
-- 서플라이어
, s.supplier_id as 공급자ID
, s.contact_name as 공급사
, s.country as 공급사국가
, s.city as 공급사도시
from orders o
left join order_details od ON od.order_id = o.order_id
left join products p on p.product_id = od.product_id
left join categories c on p.category_id = c.category_id
left join suppliers s ON p.supplier_id = s.supplier_id
)
-- 1. 제품별 매출액 구하기
, cte_amount as (
select 카테고리명, 제품명
, sum(매출액) as 매출액
from cte_products_sales
group by 1,2
)
-- 2. rank
, cte_rank as (
select *
, rank () over (partition by 카테고리명 order by 매출액 desc) 랭크
from cte_amount
)
-- 3. rank top 3
, cte_rank_top3 as (
select *
from cte_rank
where 랭크 <= 3
)
-- 4. 각 열을 case when 사용해서 만들어준다!
-- 여기서 max는 카테고리명으로 그룹핑 되었기 때문에 집계함수를 사용할 수 밖에 없어서 넣는 것.
-- 어차피 각 열 행 당 결과는 rank에 딱딱 맞는 1개이기 때문에 max,min, mean 뭐 다 상관없다.
select 카테고리명
, max(case when 랭크 = 1 then 제품명 end ) as "1위"
, max(case when 랭크 = 2 then 제품명 end ) as "2위"
, max(case when 랭크 = 3 then 제품명 end ) as "3위"
from cte_rank_top3
group by 카테고리명;
2.5 Pivot Table 2
두 번째 방법!
매출순위로 그룹핑 후 카테고리 명 뽑기!!!!!
조건 : 카테고리마다 제품 수가 다르기 때문에 null 처리까지
--두 번째 방법!
--매출순위로 그룹핑 후 카테고리 명 뽑기!
--카테고리 마다 제품 수가 다르다!, null 처리까지 하기~
with cte_products_sales as (
select -- order
o.order_id as 주문번호
, o.customer_id as 고객번호
, o.order_date as 주문일
, to_char(order_date, 'YYYY') as 연
, to_char(order_date, 'MM') as 월
, to_char(order_date, 'dd') as 일
, to_char(order_date, 'q') as 분기
-- order_details
, od.product_id as 제품ID
, od.unit_price as 판매당시금액
, od.quantity as 수량
, od.discount as 할인율
, round( od.unit_price * od.quantity * (1-od.discount) ) as 매출액
-- categories
, c.category_id as 카테고리ID
, c.category_name as 카테고리명
--, p.product_id as 제품ID order_details와 중복!
, p.product_name as 제품명
, p.unit_price as 마스터단가
, p.discontinued as 단종여부
-- 서플라이어
, s.supplier_id as 공급자ID
, s.contact_name as 공급사
, s.country as 공급사국가
, s.city as 공급사도시
from orders o
left join order_details od ON od.order_id = o.order_id
left join products p on p.product_id = od.product_id
left join categories c on p.category_id = c.category_id
left join suppliers s ON p.supplier_id = s.supplier_id
)
-- 1. 제품별 매출액 구하기
, cte_amount as (
select 카테고리명, 제품명
, sum(매출액) as 매출액
from cte_products_sales
group by 1,2
)
-- 2. rank
, cte_rank as (
select *
, rank () over (partition by 카테고리명 order by 매출액 desc) 랭크
from cte_amount
)
-- 3. 각 랭크에 해당하는 카테고리 별 제품
select 랭크
, COALESCE(max(case when 카테고리명 = 'Beverages' then 제품명 end), '') as Beverages
, COALESCE(max(case when 카테고리명 = 'Condiments' then 제품명 end), '') as Condiments
, COALESCE(max(case when 카테고리명 = 'Confections' then 제품명 end), '') as Confections
, COALESCE(max(case when 카테고리명 = 'Dairy Products' then 제품명 end), '') as "Dairy Products"
, COALESCE(max(case when 카테고리명 = 'Grains/Cereals' then 제품명 end), '') as "Grains/Cereals"
, COALESCE(max(case when 카테고리명 = 'Meat/Poultry' then 제품명 end), '') as "Meat/Poultry"
, COALESCE(max(case when 카테고리명 = 'Produce' then 제품명 end), '') as Produce
, COALESCE(max(case when 카테고리명 = 'Seafood' then 제품명 end), '') as Seafood
from cte_rank
group by 1
order by 1;
# 공백, 특수문자 alias는 ""로 감싸서 넣기!
# null 처리는 COALESCE로! COALESCE(식, null 일 때 대체 값)
# 이전번에 했던 것과 같이 그룹핑되어있을 때는 집계함수를 사용해야 하고, 어차피 각 랭크에 해당하는 제품을 한 개씩만 가져오는 것이기 때문에 MAX를 통해 집계인 척하기
3. 금일 과제
이전까지는 매출액을 기준으로 했는데? 과제는 수량을 기준으로 해서 가장 많이 팔린 게 뭔지 구하기
3.1 전체 판매수량 TOP10
- 먼저 판매수량 기준 RANK로 정렬!
- RANK 제품ID 제품명 총판매수량 퐁 매출액 카테고리명
- 여기서 LIMIT 10
/*
1. 전체 판매수량 TOP10
- 먼저 판매수량 기준 RANK로 정렬!
- RANK 제품ID 제품명 총 판매수량 퐁 매출액 카테고리명
- 여기서 LIMIT 10
*/
with cte_products_sales as (
select -- order
o.order_id as 주문번호
, o.order_date as 주문일
, to_char(order_date, 'YYYY') as 연
, to_char(order_date, 'MM') as 월
, to_char(order_date, 'dd') as 일
, to_char(order_date, 'q') as 분기
-- order_details
, od.product_id as 제품ID
, od.unit_price as 판매금액
, od.quantity as 수량
, od.discount as 할인율
, round( od.unit_price * od.quantity * (1-od.discount) ) as 매출액
-- categories
, c.category_id as 카테고리ID
, c.category_name as 카테고리명
, p.product_name as 제품명
from orders o
left join order_details od ON od.order_id = o.order_id
left join products p on p.product_id = od.product_id
left join categories c on p.category_id = c.category_id
left join suppliers s ON p.supplier_id = s.supplier_id
)
-- 1. 제품별 매출액,판매수량
, cte_amount as (
select 제품ID, 제품명, 카테고리명
, sum(수량) as 판매수량
, sum(매출액) as 매출액
from cte_products_sales
group by 1,2,3
order by 판매수량 desc
)
-- 2. 랭크구하기
, cte_rank_top10 as (
select *
, row_number() over(order by 판매수량 DESC) as Rank
from cte_amount
)
-- 3. 판매수량 top 10
select rank as "Rank"
, 제품ID as "제품ID"
, 제품명
, 판매수량 as "총 판매수량"
, 매출액 as "총 매출액"
, 카테고리명
from cte_rank_top10
limit 10
3.2 국가별 판매수량 TOP5
국가별로 파티션 나눠서 다시 랭크, 국가명 기준으로
- 국가명 RANK 1 RANK 2 RANK 3 RANK 4 RANK 5
- 그리고 각 제품명 넣기
/*
2. 국가별 판매수량 TOP5
국가별로 파티션 나눠서 다시 랭크, 국가명 기준으로
- 국가명 RANK 1 RANK 2 RANK 3 RANK 4 RANK 5
- 그리고 각 제품명 넣기
*/
with cte_products_sales as (
select -- order
o.order_id as 주문번호
, o.customer_id as 고객번호
, o.order_date as 주문일
, to_char(order_date, 'YYYY') as 연
, to_char(order_date, 'MM') as 월
, to_char(order_date, 'dd') as 일
, to_char(order_date, 'q') as 분기
, o.ship_country as 주문국가
-- order_details
, od.product_id as 제품ID
, od.unit_price as 판매당시금액
, od.quantity as 수량
, od.discount as 할인율
, round( od.unit_price * od.quantity * (1-od.discount) ) as 매출액
-- categories
, c.category_id as 카테고리ID
, c.category_name as 카테고리명
, p.product_name as 제품명
from orders o
left join order_details od ON od.order_id = o.order_id
left join products p on p.product_id = od.product_id
left join categories c on p.category_id = c.category_id
left join suppliers s ON p.supplier_id = s.supplier_id
)
-- 1. 필요 컬럼 선정 + 판매수량, 매출액
, cte_amount as (
select 주문국가, 제품ID, 제품명
, sum(수량) as 판매수량
, sum(매출액) as 매출액
from cte_products_sales
group by 1,2,3
order by 1 desc
)
-- 2. 국가 별 판매수량 랭크구하기
, cte_rank as (
select *
, row_number() over(partition by 주문국가 order by 판매수량 DESC) as Rank
from cte_amount
)
-- 3. 국가 별 판매수량 top 5
, cte_rank_top5 as (
select *
from cte_rank
order by 매출액
), cte_pivot as (
-- 4. PIVOT 및 국가별 총 매출액 순 내림차순 정렬
select 주문국가 as 국가명
, sum(매출액) as 총매출액
, max(case when Rank = 1 then 제품명 end ) as "RANK 1"
, max(case when Rank = 2 then 제품명 end ) as "RANK 2"
, max(case when Rank = 3 then 제품명 end ) as "RANK 3"
, max(case when Rank = 4 then 제품명 end ) as "RANK 4"
, max(case when Rank = 5 then 제품명 end ) as "RANK 5"
from cte_rank_top5
group by 주문국가
order by 총매출액 desc
)
-- 5. 국가별 판매량 TOP 5 제품 - 최종정리
select 국가명, "RANK 1", "RANK 2", "RANK 3", "RANK 4", "RANK 5"
from cte_pivot
3.3 1997년 분기별 판매수량 TOP10
97년만 가져와서 분기별 총 판매 수량
/*
3. 1997년 분기별 판매수량 TOP10
97년만 가져와서 분기별 총 판매 수량
*/
with cte_products_sales as (
select -- order
o.order_id as 주문번호
, o.customer_id as 고객번호
, o.order_date as 주문일
, to_char(order_date, 'YYYY') as 연
, to_char(order_date, 'MM') as 월
, to_char(order_date, 'dd') as 일
, to_char(order_date, 'q') as 분기
-- order_details
, od.product_id as 제품ID
, od.unit_price as 판매당시금액
, od.quantity as 수량
, od.discount as 할인율
, round( od.unit_price * od.quantity * (1-od.discount) ) as 매출액
-- categories
, c.category_id as 카테고리ID
, c.category_name as 카테고리명
, p.product_name as 제품명
from orders o
left join order_details od ON od.order_id = o.order_id
left join products p on p.product_id = od.product_id
left join categories c on p.category_id = c.category_id
left join suppliers s ON p.supplier_id = s.supplier_id
)
-- 1. 1997년의 분기, 판매수량 등 필요 컬럼 가져오기
, cte_amount as (
select 분기, 제품명
, sum(수량) as 판매수량
, sum(매출액) as 매출액
from cte_products_sales
where 연 = '1997'
group by 1,2
order by 1
)
-- 2. 각 분기 partition 놓고 rank 구하기
, cte_rank as (
select *
, row_number () over (partition by 분기 order by 판매수량 desc) 랭크
from cte_amount
)
-- 3. 각 분기 별 rank top 10 조건 걸어주기
, cte_rank_top10 as (
select *
from cte_rank
where 랭크 <= 10
)-- 4. pivot - 최종정리
select 랭크
, COALESCE(max(case when 분기 = '1' then 제품명 end), '') as "1997-1분기"
, COALESCE(max(case when 분기 = '2' then 제품명 end), '') as "1997-2분기"
, COALESCE(max(case when 분기 = '3' then 제품명 end), '') as "1997-3분기"
, COALESCE(max(case when 분기 = '4' then 제품명 end), '') as "1997-4분기"
from cte_rank_top10
group by 1
order by 1;
with cte_products_sales as (
select -- order
o.order_id as 주문번호
, o.customer_id as 고객번호
, o.order_date as 주문일
, to_char(order_date, 'YYYY') as 연
, to_char(order_date, 'MM') as 월
, to_char(order_date, 'dd') as 일
, to_char(order_date, 'q') as 분기
-- order_details
, od.product_id as 제품ID
, od.unit_price as 판매당시금액
, od.quantity as 수량
, od.discount as 할인율
, round( od.unit_price * od.quantity * (1-od.discount) ) as 매출액
-- categories
, c.category_id as 카테고리ID
, c.category_name as 카테고리명
, p.product_name as 제품명
from orders o
left join order_details od ON od.order_id = o.order_id
left join products p on p.product_id = od.product_id
left join categories c on p.category_id = c.category_id
left join suppliers s ON p.supplier_id = s.supplier_id
), cte_amount as (
-- 1. 1997년의 분기, 판매수량 등 필요 컬럼 가져오기
select 연, 분기, 제품명
, sum(수량) as 판매수량
from cte_products_sales
where (연 = '1996' and 분기 = '4') or 연 = '1997'
group by 1,2,3
order by 연, 분기 asc
), cte_rank as (
-- 2. 랭크 구하기
select *
, row_number () over (partition by 연, 분기 order by 판매수량 desc) 랭크
from cte_amount
order by 제품명, 연, 분기
)
-- 3. LAG 사용해서 이전 분기와 연산/비교하기
, cte_lag as (
select *
, lag(랭크) over(partition by 제품명 order by 연, 분기) - 랭크 as 순위변화
from cte_rank
)
-- 4. pivot - 최종정리
select 랭크 as rank
, max(case when 연 = '1997' and 분기 = '1' then 제품명 end) as "97_1분기"
, max(case when 연 = '1997' and 분기 = '1' then 순위변화 end) as "순위변화"
, max(case when 연 = '1997' and 분기 = '2' then 제품명 end) as "97_2분기"
, max(case when 연 = '1997' and 분기 = '2' then 순위변화 end) as "순위변화"
, max(case when 연 = '1997' and 분기 = '3' then 제품명 end) as "97_3분기"
, max(case when 연 = '1997' and 분기 = '3' then 순위변화 end) as "순위변화"
, max(case when 연 = '1997' and 분기 = '4' then 제품명 end) as "97_4분기"
, max(case when 연 = '1997' and 분기 = '4' then 순위변화 end) as "순위변화"
from cte_lag
group by 1
limit 10;
97_1분기의 답이 강사님과 다른 건, 아래 우측 사진과 같이 판매수량 153인 두 제품이 존재하기 때문이다!
총매출까지 고려하여 rank하고 싶다면 간단하게 row number 사용 시 order by 인자에 총매출액 desc 추가로 넣어주면 된다!
with cte_products_sales as (
select -- order
o.order_id as 주문번호
, o.customer_id as 고객번호
, o.order_date as 주문일
, to_char(order_date, 'YYYY') as 연
, to_char(order_date, 'MM') as 월
, to_char(order_date, 'dd') as 일
, to_char(order_date, 'q') as 분기
-- order_details
, od.product_id as 제품ID
, od.unit_price as 판매당시금액
, od.quantity as 수량
, od.discount as 할인율
, round( od.unit_price * od.quantity * (1-od.discount) ) as 매출액
-- categories
, c.category_id as 카테고리ID
, c.category_name as 카테고리명
, p.product_name as 제품명
from orders o
left join order_details od ON od.order_id = o.order_id
left join products p on p.product_id = od.product_id
left join categories c on p.category_id = c.category_id
left join suppliers s ON p.supplier_id = s.supplier_id
), cte_amount as (
-- 1. 1997년의 분기, 판매수량 등 필요 컬럼 가져오기
select 연, 분기, 제품명
, sum(수량) as 판매수량
, sum(매출액) as 총매출액
from cte_products_sales
where (연 = '1996' and 분기 = '4') or 연 = '1997'
group by 1,2,3
order by 연, 분기, 판매수량, 총매출액 asc
), cte_rank as (
-- 2. 랭크 구하기
select *
, row_number () over (partition by 연, 분기 order by 판매수량 desc, 총매출액 desc) 랭크
from cte_amount
order by 제품명, 연, 분기
), cte_lag as (
select *
, lag(랭크) over(partition by 제품명 order by 연, 분기) - 랭크 as 순위변화
from cte_rank
)
select 랭크 as rank
, max(case when 연 = '1997' and 분기 = '1' then 제품명 end) as "97_1분기"
, max(case when 연 = '1997' and 분기 = '1' then 순위변화 end) as "순위변화"
, max(case when 연 = '1997' and 분기 = '2' then 제품명 end) as "97_2분기"
, max(case when 연 = '1997' and 분기 = '2' then 순위변화 end) as "순위변화"
, max(case when 연 = '1997' and 분기 = '3' then 제품명 end) as "97_3분기"
, max(case when 연 = '1997' and 분기 = '3' then 순위변화 end) as "순위변화"
, max(case when 연 = '1997' and 분기 = '4' then 제품명 end) as "97_4분기"
, max(case when 연 = '1997' and 분기 = '4' then 순위변화 end) as "순위변화"
from cte_lag
group by 1
limit 10;
4. 핵심내용 정리
1. WINDOW FUNCTION - 상황 별로 사용하는 함수가 있음! 익숙해지기~ LAG, RANK, 집계함수 활용
