[데이터 분석을 위한 SQL 레시피] 10강 - 다면적인 축을 사용해 데이터 집약하기

목차

1) 카테고리별 매출과 소계 계산하기

2) ABC 분석으로 잘 팔리는 상품 판별하기

3) 팬 차트로 상품의 매출 증가율 확인하기

4) 히스토그램으로 구매 가격대 집계하기

 

10강 다면적인 축을 사용해 데이터 집약하기

상품의 카테고리, 가격 등을 조합해서 데이터의 특징을 추출해 리포팅하기

 

1) 카테고리별 매출과 소계 계산하기

카테고리의 소계와 총계를 한 번에 출력하려면, 계층별로 집계한 결과를 같은 컬럼(같은이름)이 되게 변환한 뒤, UNION ALL 구문으로 하나의 테이블로 합치면 됨.

** UNION ALL = 이름 같은 컬럼으로 구성된 두 개의 테이블 있을 때, 중복 데이터도 지우지 않고 모두 표시하여 합치는 방법 → but, 성능 안좋음.

—> ROLLUP 사용

 

** ROLLUP : 그룹화 및 집계를 수행, 부분합계와 총합계를 계산할 수 있음

ROLLUP 사용하면 소계를 계산할 때 레코드 집계 키가 NULL(합쳤을 때 이름이 지정 안되어있으니까) 이 되므로, COALESCE 함수로 NULL을 문자열 ‘all로 변환’

** COALESCE 함수 → parameter를 순차적으로 확인하여 앞에 값이 null이면 뒤에 값을 사용하고, 만약 해당값도 null이면, 그 뒤에 들어온 값을 사용한다.

#10-2 ROLLUP 사용해서 카테고리별 매출과 소계를 동시에 구하는 쿼리

SELECT
	COALESCE(category, 'all') AS category
	,COALESCE(sub_category, 'all') AS sub_category
	,SUM(price) AS amount
FROM
	purchase_detail_log
GROUP BY
	ROLLUP(category, sub_category)
;

#실행결과

category  | sub_category  | amount
-----------------------------------
ladys_fashion | bag    | 176000
ladys_fashion | jacket | 128000
ladys_fashion | all    | 304000
mens_fashion  | jacket | 147000
mens_fashion  | shoes  | 309000
mens_fashion  | all    | 456000
all           | all    | 760000

 

2) ABC 분석으로 잘 팔리는 상품 판별하기

ABC 분석 = 재고 관리 등에서 사용, 매출 중요도에 따라 상품을 나누고, 그에 맞게 전략을 만들 때 사용.

서비스의 매출이 어떻게 구성 되어 있는지 파악할 때 효과적임.

위에서 까만 선은 구성비 누계로 보면 됨.

일반적으로,

• A등급: 상위 0~70%
• B등급: 상위 70~90%
• C등급: 상위 90~1000%

데이터 작성하는 방법

1. 매출이 높은 순서로 데이터 정렬
2. 매출 합계를 집계
3. 매출 합계를 기반으로 각 데이터가 차지하는 비율을 계산하고, 구성비를 구함.
4. 계산한 카테고리의 구성비를 기반으로 구성비누계를 구함.

(카테고리의 매출과 해당시점까지의 누계를 따로 계산하고, 총 매출로 나누면 구성비 누계를 구할 수 있음.)

 

 

-원하는 시간에 있는 구매로그 압축 → 상품 카테고리마다 매출 계산 → 전체 매출에 대해 항목별 매출 구성비와 구성비누계를 계산 → 구성비누계를 기준으로 등급을 나눔

#10-3 매출 구성비누계와 ABC 등급을 계산하는 쿼리 

WITH 
monthly_sales AS(
	SELECT
		category
		-- 항목별 매출 계산하기
		, SUM(price) AS amount
	FROM
		purchase_detail_log
-- 대상 1개월 동안의 로그를 조건으로 걸기
WHERE
	dt BETWEEN '2015-12-01' AND '2015-12-31'
GROUP BY
	category
)
, sales_composition_ratio AS(
	SELECT
		category
		,amount

	-- 구성비: 100.0 *<항목별 매출> / <전체 매출>
		, 100.0 * amount / SUM(amount) OVER() AS composition_ratop

	-- 구성비누계 : 100.0 * <항목별 누계 매출> / <전체 매출>
		, 100.0 * SUM(amount) OVER (ORDER BY amount DESC
		ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
		/ SUM(amount) OVER () cumulative_ratio
	FROM
		monthly_sales
)
SELECT
	*
-- 구성비 누계 번위에 따라 순위 붙이기
	, CASE
			WHEN cumulative_ratio BETWEEN 0 AND 70 THEN 'A'
			WHEN cumulative_ratio BETWEEN 70 AND 90 THEN 'B'
			WHEN cumulative_ratio BETWEEN 90 AND 100 THEN 'C'
		END AS abc_rank
FROM
	sales_composition_ratio
ORDER BY
	amount DESC
;

 

3) 팬 차트로 상품의 매출 증가율 확인하기

 

팬 차트

= 어떤 기준 시점을 100%로 두고, 이후의 숫자 변동을 확인할 수 있게 해주는 그래프.

작은 변화는 그래프에서 변화를 확인하기조차 힘들기 때문.

 

팬차트를 사용하면, 변화가 백분율로 표시되므로, 작은 변화도 쉽게 인지하고 상황을 판단할 수 있음. (변화 비율이 그래프에 표시되므로, 성장과 쇠퇴를 쉽게 파악할 수 있음.)

1. 날짜 데이터를 기반으로 연과 월의 값 추출
2. 연과 월 단위로 매출을 구함
3. 구한 매출을 시계열 순서로 정렬
4. 팬 차트 작성을 위한 기준이 되는 월 매출을 기준으로 비율을 구함.

(기준이 되는 매출이 시계열로 정렬했을 때 가장 첫 월의 매출일 때, FIRST_VALUE 윈도 함수 사용.)

#10-4 팬 차트 작성 때 필요한 데이터를 구하는 쿼리

WITH
daily_category_amount AS(
	SELECT
		dt
		, category
		, substring(dt,1,4) AS year
		, substring(dt,6,2) AS year
		, substring(dt,9,2) AS year
		, SUM(price) AS amount
	FROM purchase_detail_log
	GROUP BY dt, category
)
, monthly_category_amount AS(
SELECT
	concat(year,'-',month) AS year_month
	, category
	, SUM(amount) AS amount
FROM
	daily_category_amount
GROUP BY
	year,month, category
)
SELECT
	year_month
	, category
	, amount
	, FIRST_VALUE(amount)
			OVER(PARTITION BY category ORDER BY year_month, category ROWS UNBOUNDED PRECEDING)
		AS base_amount
	,100.0
		* amount
		/ FIRST_VALUE(amount)
				OVER(PARTITION BY category ORDER BY year_month, category ROWS UNBOUNDED PRECEDING)
		AS rate
FROM monthly_category_amount
ORDER BY
year_month, category
;

** 어떤 시점에서의 매출 금액을 기준점으로 채택할 것인가 에 따라 성장 경향인지 쇠퇴 경향인지 판단이 크게 달라짐.

→ 계절 변동이 적은 평균적인 달을 기준으로 선택하는 것이 좋음. 근거를 가지고 기준점을 채택해야함.

 

4) 히스토그램으로 구매 가격대 집계하기

히스토그램

= 가로 축에 단계 (데이터의 범위), 세로 축에 도수(데이터의 개수)를 나타내는 그래프 → 데이터가 어떻게 분산되어 있는지 한눈에 확인 가능.

** 최빈값은 평균값과 비슷하지 않을 수도 있음.

 

• 히스토그램 만드는 방법

1. 최댓값, 최솟값, 범위(최댓값-최솟값)를 구한다. → 첫시작과 마지막 지머
2. 범위를 기반으로 몇 개의 계급으로 나눌지 결정하고, 각 계급의 하한과 상한(각각 어디부터 어디까지할지)을 구한다.
3. 각 계급에 들어가는 데이터 개수(도수)를 구하고, 이를 표로 정리한다.

** 막대그래프와 차이점!

히스토그램은 각각의 막대 사이에 공백을 넣지 않음.

막대 그래프의 경우는 서로다른 항목을 나타내므로 중간에 공백을 넣지만,

히스토그램은 연속된 데이터의 분포를 확인하기 위해 사용하는 것이므로 공백을 넣지않고 그림.

 

• 임의의 계층 수로 히스토그램 만들기

# 10-8 히스토그램을 구하는 쿼리

WITH
stats AS(
SELECT
	-- <금액의 최댓값> + 1 -> 모든 레코드가 지정한 범위 내부에 들어가기 위해서는 +1 해줘야함, 계급 판정 로직이 <계급 하한 이상> ~ <계급 상한 미만> 이기 때문에 최댓값이 11로 계산됨.
	MAX(price) AS max_price
	-- <금액의 최솟값> + 1
	, MIN(price) AS min_price
	-- 금액의 범위
	, MAX(price) - MIN(price) AS range_price
	-- 계층 수
	, 10 AS bucket_num
FROM
	purchase_detail_log
)
, purchase_log_with_bucket AS (
	SELECT
		price
		, min_price
		-- 정규화 금액 : 대상 금액에서 최소 금액을 뺀것
		, price - min_price AS diff
		-- 계층 범위 : 금액 범위를 계층 수로 나눈 것
		, 1.0 * range_price / bucket_num AS bucket_range
		-- 계층 판정 :
		, with_bucket(price, min_price, max_price, bucket_num) AS bucket
	FROM
		purchase_detail_log, stats
)
SELECT
	bucket
	-- 계층의 하한과 상한 계산하기
	, min_price + bucket_range * (bucket -1) AS lower_limit
	, min_price + bucket_range * bucket AS upper_limit
	-- 도수세기
	, COUNT(price) AS num_purchase
	-- 합계 금액 계사하기
	, SUM(price) AS total_amount
FROM
	purchase_log_with_bucket
GROUP BY
	bucket, min_price, bucket_range
ORDER BY bucket
;

 

• 임의의 계층 너비로 히스토그램 작성하기

위에처럼 가격의 상한과 하한 기준으로도 최적의 범위를 구할 수 있지만, 소수점으로 계층을 구분한 리포트는 직감적이지 않아서,

금액의 최댓값, 최솟값, 금액 범위 등의 고정값을 기반으로 임의의 계층 너비로 변경할 수 있는 기능을 사용하기도 함.

50000 AS max_price, 0 AS min_price, 50000 AS range_price 이렇게 정해서! → 0 ~ 50000원의 범위를 10개의 계층으로 구분하는 쿼리.

→ 5,000원 단위로 구분된 계층 히스토그램 데이터.

 

• 히스토그램이 나누어진 경우

히스토그램 산이 2개로 나누어진 경우 서로 다른 모집단을 기반으로 하나의 데이터를 도출한 경우일수도 있음 ex) 성별 등등

→ 데이터에 여러 조건을 걸어 필터링해서 확인