[TIL] 48일차 TIL(20230413) - SQL 제품/카테고리 매출 지표 분석, PIVOT

 

 

[TIL] 48일차 TIL(20230413) - SQL 제품/카테고리 매출 지표 분석, PIVOT

 

 

 

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

 

✅ 제품별 매출액/순위

-- 제품별 매출액 순위 테이블
WITH cte_product_sale AS (
SELECT o.order_id, o.customer_id, o.order_date,
	to_char(order_date, 'YYYY') order_year, to_char(order_date, 'MM') order_MONTH, to_char(order_date, 'DD') order_DAY,
	TO_CHAR(ORDER_DATE, 'QUARTER') order_QUARTER, od.unit_price, od.quantity, od.discount,
	(od.unit_price * od.quantity * (1-od.discount)) sales,
	c.category_id, c.category_name , p.product_id , p.product_name, p.unit_price master_unit_price,
	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 c.category_id = p.category_id AND
	od.product_id = p.product_id AND p.supplier_id = s.supplier_id
)
SELECT category_name, product_id, product_name, company_name, country, sum(sales),
	rank() over(ORDER BY sum(sales) desc) rnk,
	round((sum(sales) / sum(sum(sales)) over() * 100)::NUMERIC, 2) || '%' sales_per
FROM cte_product_sale
GROUP BY 1,2,3,4,5
ORDER BY rnk;

 

 

✅ 카테고리별 매출액/순위

-- 카테고리별 순위, 매출액 비율 테이블
WITH cte_product_sale AS (
SELECT o.order_id, o.customer_id, o.order_date,
	to_char(order_date, 'YYYY') order_year, to_char(order_date, 'MM') order_MONTH, to_char(order_date, 'DD') order_DAY,
	TO_CHAR(ORDER_DATE, 'QUARTER') order_QUARTER, od.unit_price, od.quantity, od.discount,
	(od.unit_price * od.quantity * (1-od.discount)) sales,
	c.category_id, c.category_name , p.product_id , p.product_name, p.unit_price master_unit_price,
	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 c.category_id = p.category_id AND
	od.product_id = p.product_id AND p.supplier_id = s.supplier_id
)
SELECT category_name, product_id, product_name, company_name, country, sum(sales),
	rank() over(PARTITION BY category_name ORDER BY sum(sales) desc) rnk,
	round((sum(sales) / sum(sum(sales)) over(PARTITION BY category_name) * 100)::NUMERIC, 2) || '%' sales_per
FROM cte_product_sale
GROUP BY 1,2,3,4,5
ORDER BY category_name, rnk;

 

 

✅ ABC분석

• 관리대상을 A,B,C그룹으로 나누고, A그룹을 중점 관리대상으로 선정하여 집중함으로써 효율적으로 관리하기 위한 분석 방법
• '극히 소수의 요인에 의해 대세가 결정된다.'는 파레토 법칙에서 유래
• ① 매출액이 많은 순으로 정렬하고, 총 매출액 대비 고객별 매출액의 비율을 구한다.
• ② 구성비율이 높은 고객부터 정렬하여 순서대로 누적 비율을 구한다.
• ③ 세로축에 매출액 누적 비율을, 가로축에 고객을 기입하고 그래프를 그린다.
• ④ 세로축의 70%와 90% 지점에 속하는 고객을 각각 A, B그룹으로 정의하고, 이 외 그룹을 C그룹으로 정의한다.

 

/*ABC 분석*/
WITH cte_product_sale AS (
SELECT o.order_id, o.customer_id, o.order_date,
	to_char(order_date, 'YYYY') order_year, to_char(order_date, 'MM') order_MONTH, to_char(order_date, 'DD') order_DAY,
	TO_CHAR(ORDER_DATE, 'QUARTER') order_QUARTER, od.unit_price, od.quantity, od.discount,
	(od.unit_price * od.quantity * (1-od.discount)) amount,
	c.category_id, c.category_name , p.product_id , p.product_name, p.unit_price master_unit_price,
	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 c.category_id = p.category_id AND
	od.product_id = p.product_id AND p.supplier_id = s.supplier_id
),
-- 1. 제품별 매출액
cte_amount AS (
SELECT product_id, product_name, sum(amount) AS 매출액
FROM cte_product_sale
GROUP BY 1, 2
ORDER BY product_id
),
-- 2. 구성비
cte_ratio AS (
SELECT *, sum(매출액) over() AS 전체매출액,
	매출액/sum(매출액) over() * 100 구성비
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;

 

 

 

 

✅ CASE ~ WHEN을 사용한 PIVOT

앞서 구한 제품의 카테고리별 순위를 바탕으로 PIVOT 테이블을 만들었다.

PIVOT를 수행하는 SQL구문이 따로 있지만, 우선 CASE ~ WHEN 구문을 활용하여 간단한 피벗을 구현했다.

 

1) 카테고리별 1~3등을 나열하는 형태로 PIVOT

-- group by + case when으로 피벗하기(카테고리별)
SELECT category_name,
	max(CASE WHEN rnk = 1 THEN product_name END) AS "1위",  -- GROUP by를 사용했기 때문에 집계가 필요
	max(CASE WHEN rnk = 2 THEN product_name END) AS "2위",
	max(CASE WHEN rnk = 3 THEN product_name END) AS "3위"
FROM sales_rank
GROUP BY category_name;

카테고리별 순위를 보여주고 싶은 것이기 떄문에 group by category_name을 해주고, select 구문에는 집계된 컬럼만 존재해야 하므로 max()라는 집계함수를 사용하였다. 조건에 해당하는 값은 1개씩이기 때문에 대부분의 집계함수를 쓰더라도 동일한 결과를 얻을 수 있다.

 

 

 

2) 등수별로 각 카테고리 컬럼에 해당 제품 표기

SELECT rnk,
	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 sales_rank
GROUP BY rnk
ORDER BY rnk;

카테고리별 제품 수에 차이가 있어 NULL로 표시되는 경우, COALESCE로 감싸서 결측치를 공백으로 보여주도록 만들었다.

*컬럼명을 지정할 때, 공백이나 특수문자를 지정하고 싶은 경우 ""(쌍따옴표)를 사용하면 가능하다. 이 외에는 넣을 수 없다.

 

 

 

📗 230413 과제 - 판매수량 기준 TOP/RANK 산출

 

✅ 전체 판매수량 top10

WITH cte_product_sale AS (
SELECT o.order_id, o.customer_id, o.order_date,
	to_char(order_date, 'YYYY') order_year, to_char(order_date, 'MM') order_MONTH, to_char(order_date, 'DD') order_DAY,
	TO_CHAR(ORDER_DATE, 'QUARTER') order_QUARTER, od.unit_price, od.quantity, od.discount,
	(od.unit_price * od.quantity * (1-od.discount)) sales,
	c.category_id, c.category_name , p.product_id , p.product_name, p.unit_price master_unit_price,
	p.discontinued, cu.company_name, cu.country
FROM orders o, order_details od, categories c, products p, customers cu
WHERE o.order_id = od.order_id AND c.category_id = p.category_id AND
	od.product_id = p.product_id AND o.customer_id = cu.customer_id
),
-- 제품별 집계
cte_product_total as
(SELECT product_id, product_name, sum(quantity) total_quantity, sum(sales) total_sales, category_name
FROM CTE_PRODUCT_SALE
GROUP BY product_id, product_name, category_name
)
-- rank 생성
SELECT ROW_NUMBER() OVER(ORDER BY total_quantity desc) RNK, * 
FROM cte_product_total
LIMIT 10;

 

 

✅ 국가별 판매수량 top5

WITH cte_product_sale AS (
SELECT o.order_id, o.customer_id, o.order_date,
to_char(order_date, 'YYYY') order_year, to_char(order_date, 'MM') order_MONTH, to_char(order_date, 'DD') order_DAY,
TO_CHAR(ORDER_DATE, 'QUARTER') order_QUARTER, od.unit_price, od.quantity, od.discount,
(od.unit_price * od.quantity * (1-od.discount)) sales,
c.category_id, c.category_name , p.product_id , p.product_name, p.unit_price master_unit_price,
p.discontinued, cu.company_name, cu.country
FROM orders o, order_details od, categories c, products p, customers cu
WHERE o.order_id = od.order_id AND c.category_id = p.category_id AND
od.product_id = p.product_id AND o.customer_id = cu.customer_id
),
-- 국가별 판매수량, 매출, 순위 구하기
cte_country_rank as
(SELECT country, product_name, sum(quantity) total_quantity, sum(sales) total_sales,
row_number() over(PARTITION BY country ORDER BY sum(quantity) desc, sum(sales) desc) rnk
FROM cte_product_sale
GROUP BY country, product_name
ORDER BY 1
)
-- top5 pivot table 및 정렬
SELECT country,
max(CASE WHEN rnk = 1 THEN product_name end) AS rank1,
max(CASE WHEN rnk = 2 THEN product_name end) AS rank2,
max(CASE WHEN rnk = 3 THEN product_name end) AS rank3,
max(CASE WHEN rnk = 4 THEN product_name end) AS rank4,
max(CASE WHEN rnk = 5 THEN product_name end) AS rank5
FROM cte_country_rank
GROUP BY country
ORDER BY sum(total_sales) desc;

 

 

✅ 1997년 분기별 판매수량 top10

WITH cte_product_sale AS (
SELECT o.order_id, o.customer_id, o.order_date,
	to_char(order_date, 'YYYY') order_year, to_char(order_date, 'MM') order_MONTH, to_char(order_date, 'DD') order_DAY,
	TO_CHAR(ORDER_DATE, 'QUARTER') order_QUARTER, od.unit_price, od.quantity, od.discount,
	(od.unit_price * od.quantity * (1-od.discount)) sales,
	c.category_id, c.category_name , p.product_id , p.product_name, p.unit_price master_unit_price,
	p.discontinued, cu.company_name, cu.country
FROM orders o, order_details od, categories c, products p, customers cu
WHERE o.order_id = od.order_id AND c.category_id = p.category_id AND
	od.product_id = p.product_id AND o.customer_id = cu.customer_id AND
	to_char(order_date, 'YYYY') = '1997'
),
-- 분기별 상품의 rank
quarter_product_rank AS (
SELECT to_Char(order_Date, 'yyyy-q') order_yq, product_name, sum(quantity),
	row_number() over(PARTITION BY to_Char(order_Date, 'yyyy-q') ORDER BY sum(quantity) desc) rnk
FROM cte_product_sale
GROUP BY 1, 2
)
-- rank 기준으로 pivot
SELECT rnk, 
	max(CASE WHEN order_yq = '1997-1' THEN product_name END) AS "1997-1분기",
	max(CASE WHEN order_yq = '1997-2' THEN product_name END) AS "1997-2분기",
	max(CASE WHEN order_yq = '1997-3' THEN product_name END) AS "1997-3분기",
	max(CASE WHEN order_yq = '1997-4' THEN product_name END) AS "1997-4분기"
FROM quarter_product_rank
WHERE rnk <= 10
GROUP BY Rnk;

 

 

 

✅ 1997년 분기별 판매수량 - 순위변화

WITH cte_product_sale AS (
SELECT o.order_id, o.customer_id, o.order_date,
	to_char(order_date, 'YYYY') order_year, to_char(order_date, 'MM') order_MONTH, to_char(order_date, 'DD') order_DAY,
	TO_CHAR(ORDER_DATE, 'QUARTER') order_QUARTER, od.unit_price, od.quantity, od.discount,
	(od.unit_price * od.quantity * (1-od.discount)) sales,
	c.category_id, c.category_name , p.product_id , p.product_name, p.unit_price master_unit_price,
	p.discontinued, cu.company_name, cu.country
FROM orders o, order_details od, categories c, products p, customers cu
WHERE o.order_id = od.order_id AND c.category_id = p.category_id AND
	od.product_id = p.product_id AND o.customer_id = cu.customer_id
),
-- 분기별 상품의 rank
quarter_product_rank AS (
SELECT to_Char(order_Date, 'yyyy-q') order_yq, product_name, sum(quantity),
	row_number() over(PARTITION BY to_Char(order_Date, 'yyyy-q') ORDER BY sum(quantity) DESC, sum(sales) DESC) rnk
FROM cte_product_sale
GROUP BY 1, 2)
, product_rank_lag AS (
SELECT *, (LAG(RNK, 1) OVER(PARTITION BY PRODUCT_NAME ORDER BY ORDER_YQ)) - rnk 순위변동
FROM QUARTER_PRODUCT_RANK
ORDER BY PRODUCT_NAME, ORDER_YQ
)
SELECT rnk,
	max(CASE WHEN order_yq = '1997-1' THEN product_name END) AS q9701,
	max(CASE WHEN order_yq = '1997-1' THEN 순위변동 END) AS 순위변동,
	max(CASE WHEN order_yq = '1997-2' THEN product_name END) AS q9702,
	max(CASE WHEN order_yq = '1997-2' THEN 순위변동 END) AS 순위변동,
	max(CASE WHEN order_yq = '1997-3' THEN product_name END) AS q9703,
	max(CASE WHEN order_yq = '1997-3' THEN 순위변동 END) AS 순위변동,
	max(CASE WHEN order_yq = '1997-4' THEN product_name END) AS q9704,
	max(CASE WHEN order_yq = '1997-4' THEN 순위변동 END) AS 순위변동
FROM product_rank_lag
GROUP BY rnk
ORDER BY 1
LIMIT 10;

lag(rnk, 1) over(partition by product_name order by order_yq)를 통한 해당 상품의 이전 분기 순위를 끌어와서 순위변동 컬럼을 만든 뒤에 피벗을 수행한다.

 

 

 

---

 

 

소감 및 정리

오늘은 팀별 과제가 아니라 문제에 맞는 쿼리를 작성하는 개인과제가 주어졌는데, 마지막 문제를 해결하는데 오랜 시간이 걸렸다. 결국 lag를 사용해서 깔끔하게 해결할 수 있었지만, 처음에 순위변동을 구해야겠다고 생각했을 때 분기별 순위를 나열하고 각 컬럼 값을 빼서 구하는 방식이 생각났었다. 하지만 결국 rank는 하나로 고정시키고 제품명을 순위에 따라 정렬해야했는데 이런식으로 구성하는것이 쉽지 않았다. 고민하다가 join을 사용하면 rank에 맞는 각 product_name을 정렬할 수 있다는 생각이 들어 이런 방식으로 구하게 되었다. 이후 lag로도 구현할 수 있다는 점을 파악하고 2가지 방식으로 풀이할 수 있었다. 배운 내용을 활용해서 조금 더 쉽게 구할 수 있는 방식이 있었는데 한 가지에 꽂혀 빨리 생각하지 못했던 점이 조금 아쉬웠다. 하지만 어떻게든 목표로 하는 결과를 얻어낼 수 있는 방법을 발견해냈다는 점도 의미가 있었다. 힘들긴했지만 이처럼 문제를 해결해가는 과정은 재미있게 느껴지기도 한다. 앞으로도 이런 과제로 자주 내주셨으면 좋겠다,,,😉