[스타터스 TIL] 48일차.SQL 실전 트레이닝 (4) - 제품/카테고리 매출 지표 분석, ABC 분석

웅진X유데미 STARTERS/TIL (Today I Learned)

[스타터스 TIL] 48일차.SQL 실전 트레이닝 (4) - 제품/카테고리 매출 지표 분석, ABC 분석

라밍쏭 2023. 4. 13. 18:55

728x90

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

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

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

1) ERD로 활용할 테이블 확인

→ order_details와 products의 unit_price는 다름! 왜 다른지 파악할 필요가 있음

→ 어느 나라에서 온 제품인지도 파악할 수 있음 (suppliers)

2) 임시 테이블을 만들기

💡 각 테이블의 어떤 컬럼들을 사용할지 먼저 생각해보기

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

WITH cte_products_sales AS (
	SELECT o.order_id
		 , o.customer_id
		 , 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
		 , od.product_id
		 , od.unit_price AS 판매단가
		 , od.quantity 
		 , od.discount 
		 , (od.unit_price * od.quantity * (1-od.discount)) AS sales
		 , 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
)
SELECT count(*)
FROM cte_products_sales;

3) 제품 별 매출액 순위

- 테이블에서 어떤 컬럼을 가져올지 select

- 그에 따라 매출액, 순위, 비율을 구하기

WITH cte_products_sales AS (
-- 생략 --
)
-- 1) 매출액 계산
, cte_sales AS (
	SELECT category_name
		 , product_id AS 제품ID
		 , product_name AS 제품명
		 , company_name AS 공급사
		 , country AS 공급사_국가
		 , sum(sales) AS 매출액
	FROM cte_products_sales
	GROUP BY 1, 2, 3, 4, 5)
-- 2) 매출액 순위, 전체매출액
, cte_rank 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_rank)
-- 4) 최종 테이블
SELECT category_name AS 카테고리명
	 , 제품ID, 제품명, 공급사, 공급사_국가, 매출액, 순위, 전체매출액
	 , round(매출비율::numeric, 2)::varchar(10)||'%' AS 매출비율2
FROM cte_ratio
ORDER BY 1, 순위;

4) 카테고리 별 제품 매출액 순위, 매출액 비율

rank와 전체 매출액 구할 때 'partition by 카테고리'로 쿼리를 짜서 구할 수 있음

WITH cte_products_sales AS (
-- 생략 --
)
-- 1) 매출액 계산
, cte_sales AS (
	SELECT category_name
		 , product_id AS 제품ID
		 , product_name AS 제품명
		 , company_name AS 공급사
		 , country AS 공급사_국가
		 , sum(sales) AS 매출액
	FROM cte_products_sales
	GROUP BY 1, 2, 3, 4, 5)
-- 2) 카테고리 별 순위, 매출액 계산 (partition by 사용)
, cte_rank 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_rank)
-- 4) 전체 테이블
SELECT category_name AS 카테고리명
	 , 제품ID, 제품명, 공급사, 공급사_국가, 매출액, 순위
	 , round(매출비율::numeric, 2)::varchar(10)||'%' AS 매출비율2
FROM cte_ratio
ORDER BY 1, 순위;

2. ABC 분석

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

- 고객 분석, 제품 분석을 할 때 사용하게 됨

[출처] https://terms.naver.com/entry.naver?docId=1125408&cid=40942&categoryId=31608

📌 ABC 분석 순서

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

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

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

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

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

2.1 지표 구하기

1) 제품 별 매출액

-- 1) 제품 별 매출액
, cte_sales AS (
	SELECT product_id
		 , product_name
		 , sum(sales) 매출액
	FROM cte_products_sales
	GROUP BY 1, 2
	ORDER BY product_id)

2) 구성비

-- 2) 구성비
, cte_ratio AS (
	SELECT *
		 , sum(매출액) OVER() AS 전체매출액
		 , 매출액 / sum(매출액) OVER() * 100 AS 구성비
	FROM cte_sales)

3) 구성비 누계

-- 3) 구성비 누계
, cte_ratio_agg AS (
	SELECT *
		 , sum(구성비) OVER(ORDER BY 구성비 DESC) AS 구성비누계
	FROM cte_ratio
	ORDER BY 구성비 DESC)

4) 등급을 포함한 테이블 출력

-- 4) 등급
SELECT *
	 , CASE WHEN 구성비누계 <= 70 THEN 'A'
	 		WHEN 구성비누계 <= 90 THEN 'B'
	 		ELSE 'C'
	 	END AS 등급
FROM cte_ratio_agg
ORDER BY 구성비 DESC;

2.2 시각화

- A 등급 : 매출의 70%를 차지하는 제품

- B 등급 : 매출의 90%를 차지하는 제품

- C 등급 : 그 외 나머지를 차지하는 제품

→ 먼저 A그룹을 고객관리의 최중점목표로 하여 대책을 강구한 다음, B,C 그룹으로 옮겨 간다.

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

1) 매출 상위 3개 제품만 가져오기

WITH cte_products_sales AS (
-- 생략 --
)
-- 1) 매출액 계산
, cte_sales AS (
	SELECT category_name AS 카테고리명
		 , product_id AS 제품ID
		 , product_name AS 제품명
		 , company_name AS 공급사
		 , country AS 공급사_국가
		 , sum(sales) AS 매출액
	FROM cte_products_sales
	GROUP BY 1, 2, 3, 4, 5)
-- 2) 순위 계산
, cte_rank AS (
	SELECT *
		 , rank() over(PARTITION BY 카테고리명 ORDER BY 매출액 DESC) AS 순위
	FROM cte_sales)
-- 3) 1, 2, 3위만 출력
SELECT 카테고리명
	 , 제품명
	 , 순위
FROM cte_rank
WHERE 순위 <= 3
ORDER BY 1, 3;

2) 테이블 피벗하기

카테고리명 별로 1, 2, 3위를 컬럼으로 나타내기

WITH cte_products_sales AS (
-- 생략--
)
-- 1) 매출액 계산
, cte_sales AS (
	SELECT category_name AS 카테고리명
		 , product_id AS 제품ID
		 , product_name AS 제품명
		 , company_name AS 공급사
		 , country AS 공급사_국가
		 , sum(sales) AS 매출액
	FROM cte_products_sales
	GROUP BY 1, 2, 3, 4, 5)
-- 2) 순위 계산
, cte_rank AS (
	SELECT *
		 , rank() over(PARTITION BY 카테고리명 ORDER BY 매출액 DESC) AS 순위
	FROM cte_sales)
-- 3) 카테고리 별 1, 2, 3위 출력
, cte_top3 AS (
	SELECT 카테고리명
		 , 제품명
		 , 순위
	FROM cte_rank
	WHERE 순위 <= 3
	ORDER BY 1, 3)
-- 4) 피벗 테이블 만들기
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_top3
GROUP BY 1;

4. 순위 별 카테고리 별 제품 출력

각 순위에 따른 카테고리 별 제품들을 출력해보자.

이 때 모든 카테고리 (8가지)가 각각의 컬럼이 된다.

[+] 추가로 null인 데이터는 null로 출력이 되지 않도록 하기 → COALESCE() 사용

WITH cte_products_sales AS (
-- 생략--
)
-- 1) 매출액 계산
, cte_sales AS (
	SELECT category_name AS 카테고리명
		 , product_id AS 제품ID
		 , product_name AS 제품명
		 , company_name AS 공급사
		 , country AS 공급사_국가
		 , sum(sales) AS 매출액
	FROM cte_products_sales
	GROUP BY 1, 2, 3, 4, 5)
-- 2) 순위 계산
, cte_rank AS (
	SELECT *
		 , rank() over(PARTITION BY 카테고리명 ORDER BY 매출액 DESC) AS 순위
	FROM cte_sales)
-- 3) 피벗 테이블 만들기
SELECT 순위
	 , COALESCE(max(CASE WHEN 카테고리명 = 'Grains/Cereals' THEN 제품명 END), '') AS "Grains/Cereals"
	 , COALESCE(max(CASE WHEN 카테고리명 = 'Seafood' THEN 제품명 END), '') AS "Seafood"
	 , COALESCE(max(CASE WHEN 카테고리명 = 'Meat/Poultry' THEN 제품명 END), '') AS "Meat/Poultry"
	 , COALESCE(max(CASE WHEN 카테고리명 = 'Beverages' THEN 제품명 END), '') AS "Beverages"
	 , COALESCE(max(CASE WHEN 카테고리명 = 'Dairy Products' THEN 제품명 END), '') AS "Dairy Products"
	 , COALESCE(max(CASE WHEN 카테고리명 = 'Produce' THEN 제품명 END), '') AS "Produce"
	 , COALESCE(max(CASE WHEN 카테고리명 = 'Condiments' THEN 제품명 END), '') AS "Condiments"
	 , COALESCE(max(CASE WHEN 카테고리명 = 'Confections' THEN 제품명 END), '') AS "Confections"
FROM cte_rank
GROUP BY 1
ORDER BY 1;

5. 과제

5.0 필요한 컬럼으로 CTE 생성

각 테이블 별로 필요한 컬럼들로 우선적으로 CTE를 생성한다.

전체 테이블 중 5개의 테이블을 사용할 예정이다.

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

WITH cte_products_sales AS (
	SELECT o.order_id
	     , 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
		 , od.unit_price AS 판매단가
		 , od.quantity 
		 , od.discount 
		 , (od.unit_price * od.quantity * (1-od.discount)) AS sales
		 , c.category_id
		 , c.category_name
		 , p.product_id 
		 , p.product_name 
		 , p.unit_price AS 마스터단가
		 , p.discontinued
		 , c2.customer_id
		 , c2.company_name
		 , c2.country
		 , c2.city
	FROM orders o, order_details od, products p, categories c, customers c2
	WHERE o.order_id = od.order_id
	  AND od.product_id = p.product_id
	  AND p.category_id = c.category_id
	  AND o.customer_id = c2.customer_id)
SELECT count(*)
FROM cte_products_sales;

5.1 전체 판매수량 top10

판매수량 별로 가장 많이 팔린 제품 10개를 추출하고자 한다.

→ 총 판매수량과 총매출액을 구한 후, rank() 윈도우 함수를 활용하여 순위를 산정한다.

→ rank()를 사용하였을 때, 중복되는 값이 있는데, 순위가 중복되지 않도록 하기 위해서 총판매수량과 총매출액 모두 order by로 추가해주었다.

✔ 쿼리문

WITH cte_products_sales AS (
-- 생략 --)
-- 1) 총판매수량, 총매출액 구하기
, cte_quantity_sales AS (
	SELECT product_id AS 제품ID
		 , product_name AS 제품명
		 , sum(quantity) AS 총판매수량
		 , sum(sales) AS 총매출액
		 , category_name AS 카테고리명
	FROM cte_products_sales
	GROUP BY 1, 2, 5)
-- 2) 순위 계산
, cte_rank AS (
	SELECT rank() OVER(ORDER BY 총판매수량 DESC,총매출액 DESC) AS RANK
		 , 제품ID
		 , 제품명
		 , 총판매수량
		 , 총매출액
		 , 카테고리명
	FROM cte_quantity_sales)
-- 3) 판매 수량 TOP10인 제품 추출
SELECT *
FROM cte_rank
WHERE RANK <= 10;

✔ 결과 데이터

5.2 국가별 판매수량 TOP 5

국가를 기준으로 총판매수량, 총매출액 순위를 계산한다.

각 국가 별 1위 ~ 5위를 컬럼으로 나타내야 하므로 피벗테이블을 사용한다.

→ 피벗테이블은 CASE WHEN 구문을 활용하여 해당하는 순위의 제품명을 가져온다.

✔ 쿼리문

WITH cte_products_sales AS (
-- 생략 --)
-- 1) 총판매수량, 총매출액 계산
, cte_sales_quantity AS (
	SELECT country AS 국가
		 , product_name AS 제품명
		 , sum(sales) AS 총매출액
		 , sum(quantity) AS 총판매수량
	FROM cte_products_sales
	GROUP BY country, product_name
	ORDER BY country)
-- 2) 순위 계산
, cte_rank AS (
	SELECT 국가
		 , rank() OVER(PARTITION BY 국가 ORDER BY 총판매수량 DESC,총매출액 DESC) AS RANK
		 , 총매출액
		 , 제품명
	FROM cte_sales_quantity
	ORDER BY 국가, RANK)
-- 3) 피벗 테이블
SELECT 국가
	 , max(CASE WHEN RANK = 1 THEN 제품명 END) AS "RANK1"
	 , max(CASE WHEN RANK = 2 THEN 제품명 END) AS "RANK2"
	 , max(CASE WHEN RANK = 3 THEN 제품명 END) AS "RANK3"
	 , max(CASE WHEN RANK = 4 THEN 제품명 END) AS "RANK4"
	 , max(CASE WHEN RANK = 5 THEN 제품명 END) AS "RANK5"
FROM cte_rank
GROUP BY 국가
ORDER BY sum(총매출액) desc;

✔ 결과 데이터

5.3 1997년 분기별 판매수량

이것 또한 위에서 진행하는 것과 동일하게 하되 국가에서 연-분기로 수정해주면 된다.

→ 단, 1997년의 분기별로 봐야하기 때문에 총판매수량, 총매출액 계산하는 CTE 테이블에 WHERE 절로 조건을 걸어준다.

✔ 쿼리문

WITH cte_products_sales AS (
-- 생략 --)
-- 1) 총판매수량, 총매출액 계산
, cte_sales_quantity AS (
	SELECT YEAR
		 , quarter 
		 , product_name AS 제품명
		 , sum(sales) AS 총매출액
		 , sum(quantity) AS 총판매수량
	FROM cte_products_sales
	WHERE YEAR = '1997'
	GROUP BY 1, 2, 3
	ORDER BY 1, 2, 5 DESC)
-- 2) 순위 계산
, cte_rank AS (
	SELECT YEAR
		 , quarter
		 , rank() OVER(PARTITION BY quarter ORDER BY 총판매수량 DESC,총매출액 DESC) AS RANK
		 , 총판매수량
		 , 제품명
	FROM cte_sales_quantity
	ORDER BY quarter, RANK)
-- 3) 피벗 테이블
SELECT RANK
	 , max(CASE WHEN quarter = '1' THEN 제품명 END) AS "1997-1분기"
	 , max(CASE WHEN quarter = '2' THEN 제품명 END) AS "1997-2분기"
	 , max(CASE WHEN quarter = '3' THEN 제품명 END) AS "1997-3분기"
	 , max(CASE WHEN quarter = '4' THEN 제품명 END) AS "1997-4분기"
FROM cte_rank
WHERE RANK <= 10
GROUP BY RANK;

✔ 결과 데이터

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

순위변화는 WINDOW 함수를 활용하여 구하라고 하셔서, 많은 시도 끝에 LAG 함수를 사용하여 쿼리를 짰다.

① 년도와 분기를 하나의 컬럼으로 묶음 (year_quarter)

② 년도+분기 별로 순위를 계산

③ 년도+분기 별로 순위를 1단계씩 lag한 후, 기존 순위와 lag한 순위의 차이를 구함 (순위변동)④ 기존 순위와 순위변동 컬럼을 각 분기에 맞춰서 피벗 진행 (CASE WHEN 활용)

✔ 쿼리문

WITH cte_products_sales AS (
	SELECT o.order_id
	     , o.order_date
	     , to_char(o.order_date, 'YYYY')||'-'||to_char(o.order_date, 'q') AS year_quarter
		 , 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
		 , od.unit_price AS 판매단가
		 , od.quantity 
		 , od.discount 
		 , (od.unit_price * od.quantity * (1-od.discount)) AS sales
		 , c.category_id
		 , c.category_name
		 , p.product_id 
		 , p.product_name 
		 , p.unit_price AS 마스터단가
		 , p.discontinued
		 , c2.customer_id
		 , c2.company_name
		 , c2.country
		 , c2.city
	FROM orders o, order_details od, products p, categories c, customers c2
	WHERE o.order_id = od.order_id
	  AND od.product_id = p.product_id
	  AND p.category_id = c.category_id
	  AND o.customer_id = c2.customer_id)
-- 1) 총판매수량, 총매출액 계산
, cte_sales_quantity AS (
	SELECT year_quarter 
		 , product_name AS 제품명
		 , sum(sales) AS 총매출액
		 , sum(quantity) AS 총판매수량
	FROM cte_products_sales
	GROUP BY 1, 2
	ORDER BY 1)
-- 2) 순위 계산
, cte_rank AS (
	SELECT year_quarter
		 , 제품명
		 , rank() OVER(PARTITION BY year_quarter ORDER BY 총판매수량 DESC,총매출액 DESC) AS RANK
	FROM cte_sales_quantity)
-- 3) 각 분기 별 순위
, cte_quarter_rank AS (
	SELECT *
		 , lag(RANK, 1) over(PARTITION BY 제품명 ORDER BY year_quarter)
		 , lag(RANK, 1) over(PARTITION BY 제품명 ORDER BY year_quarter) - RANK AS 순위변동
	FROM cte_rank)
-- 4) 피벗 테이블
SELECT RANK
	 , max(CASE WHEN year_quarter = '1997-1' THEN 제품명 END) AS q9701
	 , max(CASE WHEN year_quarter = '1997-1' THEN 순위변동 END) AS 순위변동
	 , max(CASE WHEN year_quarter = '1997-2' THEN 제품명 END) AS q9702
	 , max(CASE WHEN year_quarter = '1997-2' THEN 순위변동 END) AS 순위변동
	 , max(CASE WHEN year_quarter = '1997-3' THEN 제품명 END) AS q9703
	 , max(CASE WHEN year_quarter = '1997-3' THEN 순위변동 END) AS 순위변동
	 , max(CASE WHEN year_quarter = '1997-4' THEN 제품명 END) AS q9704
	 , max(CASE WHEN year_quarter = '1997-4' THEN 순위변동 END) AS 순위변동
FROM cte_quarter_rank
WHERE RANK <= 10
GROUP BY 1
ORDER BY 1;

✔ 결과 데이터

💡 회고

어제 너무나도 팀 프로젝트가 빡셌기 때문에 오늘 개인 과제라고 해서 어제보다 낫지 않을까라는 생각을 했다.

하지만 과제로 내어주신 것 중에 순위 변화 데이터를 추출하는 것이 많이 어려워서 꽤나 헤맸다.

최대한 스스로의 힘으로 해보다가 나중에는 팀원들이랑 같이 머리를 맞대어 해보았다.

다시 보니까 생각보다 어려운 쿼리는 아닌데 왜이렇게 생각이 나지 않았는지 모르겠다....

쿼리를 짜는 힘을 다시 길러야겠다고 다짐을 하는 날이다...🥴

728x90

'웅진X유데미 STARTERS > TIL (Today I Learned)' 카테고리의 다른 글

[스타터스 TIL] 50일차.SQL 실전 트레이닝 (6) - 고객 분석, Decil 분석, RFM 분석 (0)	2023.04.17
[스타터스 TIL] 49일차.SQL 실전 트레이닝 (5) - Z차트, 그룹함수 (GROUPING SETS, ROLLUP, CUBE) (0)	2023.04.14
[스타터스 TIL] 47일차.SQL 실전 트레이닝 (3) - CTE, 윈도우 함수 (0)	2023.04.13
[스타터스 TIL] 46일차.SQL 실전 트레이닝 (2) - 컬럼 연산자, 날짜/시간형 데이터, 다중 행 함수 (0)	2023.04.11
[스타터스 TIL] 45일차.SQL 실전 트레이닝 (1) - 데이터 분석, 테이블파악 (0)	2023.04.10

현재글[스타터스 TIL] 48일차.SQL 실전 트레이닝 (4) - 제품/카테고리 매출 지표 분석, ABC 분석

ARAMDAUN

태블로, 유데미큐레이션, 데이터시각화, 부트캠프후기, 코딩테스트, 데이터분석, 유데미부트캠프, 파이썬 코딩테스트, Python, 스타터스부트캠프, 유데미코리아, 파이썬, SQL, 유데미, 데이터드리븐, COS PRO 파이썬, MySQL, 백준, Cos Pro 2급, 취업부트캠프,

Today :
Yesterday :

ARAMDAUN