[7] SQL로 분석하고 Tableau로 시각화하자 - 실무에 쓰이는 SQL 데이터분석

* ERD(Entity-Relationship Diagram)는 ERM 프로세스의 최종산출물.

* ERM : 데이터를 구조화하기 위한 개체-관계 모델링 기법

* 파일 가져오기

http://github.com/bjpublic/SQL-Tableau 

GitHub - bjpublic/SQL-Tableau: 데이터 분석과 시각화: SQL로 분석하고 Tableau로 시각화하자

- ch.5에서 Car_member.csv / Car_order.csv / Car_orderdetail.csv / Car_product.csv / Car_store.csv 다운

 

순서

1. ERD를 활용한 데이터 마트 구성

2. 고객 구매 프로파일 분석 

3. RFM 고객 세분화 분석 

4. 구매 전환율 및 구매 주기 분석

5. 제품 및 성장률 분석

---

 

1. ERD를 활용한 데이터마트 구성

 

 

1. [Car_order] 테이블을 왼쪽으로 하여 모든 테이블을 left join하기

 

2. [Car_orderdetail]의 주문수량(quantity) 및 [Car_product]의 가격(price)을 곱하여 주문금액(sales_amt)열을 생성하기

3. [Car_MART] 테이블 생성

 

SELECT A.*
	,B.prod_cd
	,B.quantity
	,D.price
	,B.quantity * D.price AS sales_amt
	,C.store_addr
	,D.brand
	,D.model
	,E.gender
	,E.age
	,E.addr
	,E.join_date
INTO [Car_MART]
FROM [Car_order] A
LEFT JOIN [Car_orderdetail] B ON A.order_no = B.order_no
LEFT JOIN [Car_store] C ON A.store_cd = C.store_cd
LEFT JOIN [Car_product] D ON B.prod_cd = D.prod_cd
LEFT JOIN [Car_member] E ON A.mem_no = E.mem_no

SELECT * 
	FROM [Car_Mart]

→ 산술 오버플로우 에러가 난다.

 

→ 두가지 컬럼 곱해서 새로운 열을 생성한 부분에서 ( B.quantity * D.price AS sales_amt ) 해당하는 컬럼의 속성을 BIGINT로 바꿔주면 해결된다.

 

ALTER table Car_orderdetail alter column quantity BIGINT
ALTER table Car_product alter column price BIGINT

 

→ 또 이미 Car_mart가 존재한다는 에러가 뜨는데, 데이터베이스에서 에러와 함께 생성되었던 Car_mart를 삭제해준 뒤 재실행해보면 잘 실행된다.

 

→ 그리고 left join 되는 테이블의 열 이름을 작성한 이유는, 중복되는 열 이름이 있으면 테이블이 생성되지 않기 때문이다.

 

---

2. 구매 고객 프로파일 분석

 

 

1. [Car_Mart] 테이블에 '연령대' 열을 추가한 세션 임시 테이블을생성

-- 구매 고객 프로파일 분석
-- 연령대 열 추가
USE EDU
SELECT * 
		,CASE WHEN age < 20 THEN '20대 미만'
			WHEN age BETWEEN 20 AND 29 THEN '20대'
			WHEN age BETWEEN 30 AND 39 THEN '30대'
			WHEN age BETWEEN 40 AND 49 THEN '40대'
			WHEN age BETWEEN 50 AND 59 THEN '50대'
			ELSE '60대 이상' END AS ageband
		INTO #PROFILE_BASE
		FROM [Car_MART]

SELECT *
	FROM #PROFILE_BASE

실행하면, 세션 임시 테이블이 생성되며 [ageband]가 추가되어 조회된다. 

 

세션 임시 테이블은 해당 쿼리창에서만 사용되고, 창을 종료하면 자동으로 삭제된다.

 

*임시 테이블이란?

              테이블           사용 위치             생성 방법                                    삭제 방법

1. 일반 테이블 - 영구적으로 사용 - create table [테이블명] - drop table [테이블명]
2. 세션 임시 테이블 - 해당 쿼리창에서만 사용 - into #[테이블명] - drop table #[테이블명] 또는 해당 쿼리창 종료
3. 전역 임시 테이블 - 모든 쿼리 창 - into ##[테이블명] - drop table ##[테이블명] 또는 모든 쿼리창 종료

 

2. #PROFILE_BASE 테이블을 이용하여 성별 및 연령대별 구매자 분포 확인

 

--성별 구매자 분포
SELECT gender
		,COUNT(DISTINCT mem_no) AS tot_mem
	FROM #PROFILE_BASE
	GROUP
	BY gender

--연령대별 구매자 분포
SELECT ageband
		,COUNT(DISTINCT mem_no) AS tot_mem
	FROM #PROFILE_BASE
	GROUP
	BY	ageband

--성별 및 연령대별 구매자 분포
SELECT gender, ageband
		,COUNT(DISTINCT mem_no) AS tot_mem
	FROM #PROFILE_BASE
	GROUP 
	BY	gender
		,ageband
	ORDER
		BY 1

성별 구매자 분포

연령대별 구매자 분포

성별 및 연령대별 구매자 분포

 

3. 2의 결과에 "연도 별" 추가 (CASE WHEN 이용)

 

-- 성별 및 연령대별 구매자 분포 + 연도 별
SELECT gender, ageband
		,COUNT(DISTINCT CASE WHEN YEAR(order_date) = 2020 THEN mem_no END) AS tot_mem_2020
		,COUNT(DISTINCT CASE WHEN YEAR(order_date) = 2021 THEN mem_no END) AS tot_mem_2021
	FROM #PROFILE_BASE
	GROUP
	BY	gender, ageband
	ORDER BY 1

연도별 성별 및 연령대별 구매자 분포

* CASE WHEN 에서 ELSE 값을 지정하지 않으면, 조건 외 값은 NULL로 반환된다. 

* COUNT는 NULL을 제외하고 개수를 집계한다.

 

---

3. RFM 고객 세분화 분석

 

 

* RFM 고객 세분화 분석은 고객들의 구매지표(최근성,구매빈도,구매금액..) 등을  활용해 고객을 세분화할 때 사용

* Recency, Frequency, Monetary

1. [Car_MART] 테이블을 활용해 고객별 RFM 세션 임시 테이블 생성

-- RFM 고객 세분화 분석
SELECT mem_no
		,SUM(sales_amt) AS tot_amt --M: 구매금액
		,COUNT(order_no) AS tot_tr --F: 구매빈도
	INTO #RFM_BASE
	FROM [Car_MART]
	WHERE YEAR(order_date) BETWEEN 2020 AND 2021 --R:최근성
	GROUP
		BY mem_no
--세션 임시테이블(#RRM_BASE) 조회
SELECT *
	FROM #RFM_BASE

고객별 R,F,M이 출력된다.

 

2. [Car_member] 왼쪽 테이블 기준으로  #RFM_BASE를 LEFT JOIN 후, '고객 세분화 그룹 '열을 추가한 세션 임시 테이블생성 

• 조건
  1. VVIP : 구매금액 10억 이상 & 구매빈도 3회 이상
  2. VIP : 구매금액 5억 이상 & 구매빈도 2회 이상
  3. GOLD : 구매금액 3억 이상
  4. SILVER : 구매금액 1억 이상
  5. BRONZE : 구매빈도 1회이상
  6. POTENTIAL : 구매이력 없음
• 임시 테이블 명 : #RFM_BAES_SEG

 

-- 고객 세분화 열 추가하기
SELECT A.*
		,B.tot_amt
		,B.tot_tr
		,CASE WHEN B.tot_amt >= 1000000000 AND B.tot_tr >=3 THEN '1_VVIP'
			WHEN B.tot_amt >= 500000000 AND B.tot_tr >=2 THEN '2_VIP'
			WHEN B.tot_amt >= 300000000 THEN '3_GOLD'
			WHEN B.tot_tr >=1 THEN '5_BRONZE'
			ELSE '6_POTENTIAL' END AS segmentation
		INTO #RFM_BASE_SEG
		FROM [Car_member] A
		LEFT
		JOIN #RFM_BASE B
			ON A.mem_no = B.mem_no

--세션 임시 테이블(#RFM_BASE_SEG) 조회
SELECT *
	FROM #RFM_BASE_SEG

출력 결과. 색칠된 부분이 LEFT JOIN한 임시 테이블 #RFM_BASE 이고, 맨 오른쪽이 추가된 고객 세분화 열 segmentation이다.

* [Car_member] (맨 왼쪽 테이블) 기준으로 #RFM_BASE 테이블이 LEFT JOIN 되었기 때문에, 가입만 하고 구매 이력이 없는 고객(mem_no)은 POTENTIAL이 된다.

 

위에서 만든 고객 세분화(segmentation)별 고객 수 및 매출 비중을 보려면

--고객 세분화 별 고객 수 및 매출 비중
SELECT segmentation	
		,COUNT(mem_no) AS tot_mem
		,SUM(tot_amt) AS tot_amt
	FROM #RFM_BASE_SEG
	GROUP
	BY segmentation
	ORDER 
	BY 1

세그먼트 별 고객 수, 매출

 

---

4. 구매 전환율 및 구매 주기 분석

 

 

고객들의 구매 패턴을 파악하고자 할 때 사용된다.

 

1. [Car_MART] 테이블을 활용해, 구매 전환율 세션 임시 테이블 #RETENTION_BASE 생성

 

--구매 전환율 세션 임시테이블 #RETENTION_BASE 생성
-- 2020 구매자 중 2021 구매여부 열 추가한 가 테이블
SELECT A.mem_no AS pur_mem_2020
		,B.mem_no AS pur_mem_2021
		,CASE WHEN B.mem_no IS NOT NULL THEN 'Y' ELSE 'N' END AS retention_yn
	INTO #RETENTION_BASE
	FROM (SELECT DISTINCT mem_no FROM [Car_MART] WHERE YEAR(order_date) = 2020) A
	LEFT
	JOIN (SELECT DISTINCT mem_no FROM [Car_MART] WHERE YEAR(order_date) = 2021) B
		ON A.mem_no = B.mem_no

--세션 임시테이블 (#RETENTION_BASE) 조회
SELECT *
	FROM #RETENTION_BASE

 

2020 구매자 중 2021 구매여부 열 retention_yn 추가

위에서 만든 가테이블 #RETENTION_BASE 테이블을 활용한 구매전환율 파악하기

-- 구매 전환율 파악
SELECT COUNT(pur_mem_2020) AS tot_mem
		,COUNT(CASE WHEN retention_yn = 'Y' THEN pur_mem_2020 END) AS retention_mem
	FROM #RETENTION_BASE

 

2. [Car_MART] 테이블을 활용하여 매장코드(store_cd) 별로 구매주기에 필요한 세션 임시 테이블 생성

 

• 구매 주기에 필요한 값
  1. 최근 구매일
  2. 최초 구매일
  3. 구매횟수 -1
• 임시 테이블명 = #CYCLE_BASE
  
  

-- 매장 코드 별 구매주기
-- 구매횟수 2회 이상 필터링
SELECT store_cd
		,MIN(order_date) AS min_order_date --최초 구매일
		,MAX(order_date) AS max_order_date --최근 구매일
		,COUNT(DISTINCT order_no) AS tot_tr_1 --구매횟수
	INTO #CYCLE_BASE
	FROM [Car_MART]
	GROUP
		BY store_cd
	HAVING COUNT(DISTINCT order_no) >=2

--세션 임시테이블 #CYCLE_BASE 조회
SELECT * 
	FROM #CYCLE_BASE

• 구매횟수를 2회이상으로 필터링한 이유는, 구매횟수 1회면 분모가 0이기 때문이다.

매장코드별 구매주기에 필요한 값 3가지인 최근 구매일, 최초 구매일, 구매횟수-1 테이블이 생성되었다.

위에서 만든 테이블을 이용해서 구매주기를 구하였다.

-- 위에서 만든 가테이블을 이용해서 매장코드별 구매주기 구하기
SELECT *
		,DATEDIFF(DAY,min_order_date, max_order_date) AS diff_day
		,DATEDIFF(DAY,min_order_date, max_order_date) * 1.00 / tot_tr_1 AS cycle
	FROM #CYCLE_BASE
	ORDER BY 6 DESC

• DATEDIFF 함수를 이용해서 최초구매일과 최근구매일의 일(DAY) 차이를 구하였다.
• 1.00을 곱해준 이유는 소수점까지 나타내기 위함이다.

 

---

5. 제품 및 성장률 분석

1. [Car_Mart] 테이블을 활용하여 브랜드 및 모델별 2020,2021년 구매금액 세션 임시 테이블 생성

* 임시테이블명 : #PRODUCT_GROWTH_BASE

-- 제품 및 성장률 분석
-- 브랜드 및 모델별 2020,2021년 구매금액 가테이블(#) 생성
SELECT brand
		,model
		,SUM(CASE WHEN YEAR(order_date) = 2020 THEN sales_amt END) AS tot_amt_2020
		,SUM(CASE WHEN YEAR(order_date) = 2021 THEN sales_amt END) AS tot_amt_2021
	INTO #PRODUCT_GROWTH_BASE
	FROM [Car_MART]
	GROUP
	BY brand
		,model
--세션 임시테이블 # PRODUCT_GROWTH_RATE 조회
SELECT *
	FROM #PRODUCT_GROWTH_BASE

 

2. # Product_growth_base 테이블을 활용한 브랜드별 성장률 파악

-- 위 테이블을 이용한 브랜드별 성장률 파악
SELECT brand
		,SUM(tot_amt_2021) / SUM(tot_amt_2020) -1 AS growth
	FROM #PRODUCT_GROWTH_BASE
	GROUP
	BY brand
ORDER BY 2 DESC

 

3. #Product_growth_base 테이블을 활용한 브랜드 및 모델별 성장률 파악 ( ROW_NUMBER 함수 이용,윈도우 함수 참고 )

* 조건 : 각 브랜드별 성장률 top2 모델만 필터

--위의 성장률에 모델별 추가
SELECT *
		,ROW_NUMBER() OVER(PARTITION BY brand ORDER BY growth DESC) AS rnk
	FROM (
		SELECT brand
				,model
				,SUM(tot_amt_2021) / SUM(tot_amt_2020) -1 AS growth
				FROM #PRODUCT_GROWTH_BASE
				GROUP
				BY brand,model
				)A

 

그 다음 위의 명렁어를 서브쿼리로하여, WHERE절로 브랜드 모델 성장률 순위가 2이하인 것만 필터하면 된다.

그렇게하면 브랜드별 성장률 top2 모델을 파악할 수 있다.

-- 위의 명령어를 서브쿼리로 where절 사용
SELECT *
	FROM (
			SELECT *
				,ROW_NUMBER() OVER(PARTITION BY brand ORDER BY growth DESC) AS rnk
			FROM (
				SELECT brand
						,model
						,SUM(tot_amt_2021) / SUM(tot_amt_2020) -1 AS growth
						FROM #PRODUCT_GROWTH_BASE
						GROUP
						BY brand,model
						)A
					)B
				WHERE rnk<=2