[MySQL] SQL로 맛보는 데이터 전처리 분석(4-3)

본 내용은 [멀티캠퍼스] 데이터 분석&데이터 엔지니어링 취업캠프 28회차에서 실시한 수업 내용 중 일부입니다.

 

교재 홍보 (본 과정의 SQL 교재)

교재 구매: https://www.yes24.com/Product/Goods/86544423

SQL로 맛보는 데이터 전처리 분석 - 예스24

 

이전 포스팅: 

https://hsyhrae.tistory.com/35

[MySQL] SQL로 맛보는 데이터 전처리 분석(4-1)

https://hsyhrae.tistory.com/36

[MySQL] SQL로 맛보는 데이터 전처리 분석(4-2)

 

 

ERD

기존 3개 테이블에 더해 product 테이블과의 관계도 주목

 

가정된 상황: 회사 업무 중 요청된 사항 처리하기

 

그룹별 구매 지표 구하기

 

요청사항4: 재구매율

링크 참고: https://hsyhrae.tistory.com/31

[MySQL] 셀프조인(Feat: 재구매율)

 

요청사항5: Best Seller

위의 재구매율의 전체 조회 결과를 확인하며 미국의 재구매율이 가장 높은 것으로 나타났다.

따라서 더 자세한 정보를 찾고자하여 미국의 연도별 매출액 Top5 차량 모델을 조회해보자.

매출액과 차량 모델명을 연결하기 위해서는 4개의 테이블이 필요하다. 이는 위의 ERD를 통해 확인해보자.

 

교재쿼리

CREATE TABLE product_sales AS 
SELECT 
	D.productname
    , SUM(quantityordered * priceeach) AS sales 
FROM orders A
LEFT 
JOIN customers B
ON A.customernumber = B.customernumber 
LEFT 
JOIN orderdetails C
ON A.ordernumber = C.ordernumber 
LEFT
JOIN products D
On C.productcode = D.productcode 
WHERE B.country = 'USA'
GROUP BY 1
ORDER BY 1
;

SELECT * 
FROM (
	SELECT *
    , ROW_NUMBER() OVER(ORDER BY sales DESC) RNK
    FROM product_sales) A
WHERE RNK <= 5
ORDER BY RNK;

 

교재 쿼리 결과가 미국의 연도별 매출액 Top5 차량 모델을 조회하는 것아닌

전체기간 중 Top5 차량 모델을 조회한다고 생각되어 아래와 같이 수정함

 

4개의 테이블 조인후 새로운 테이블 product_sales 생성

CREATE TABLE product_sales AS 
SELECT 
    SUBSTR(A.orderdate, 1, 4) AS YY
    , D.productname
    , SUM(quantityordered * priceeach) AS sales 
FROM orders A
LEFT 
JOIN customers B
ON A.customernumber = B.customernumber 
LEFT 
JOIN orderdetails C
ON A.ordernumber = C.ordernumber 
LEFT
JOIN products D
On C.productcode = D.productcode 
WHERE B.country = 'USA'
GROUP BY 1, 2
ORDER BY 1
;

SELECT * FROM product_sales;

 

윈도우 함수를 사용하여 순위를 생성하고 서브쿼리를 사용하여 조회

FROM (
	SELECT *
    , ROW_NUMBER() OVER(PARTITION BY YY ORDER BY sales DESC) RNK
    FROM product_sales2) A
WHERE RNK <= 5
ORDER BY YY, RNK;

 

요청사항6: Churn Rate(%)

Churn Rate란 활동 고객 중 얼마나 많은 고객이 비활동 고객으로 전화되었는지를 의미하는 지표이다.

실무에서 고객 1명을 획득하는 비용이 생각보다 커서 한 번 획득한 고객을 잘 유지하는 것이 중요하다.

Churn의 일반적 정의 : max(구매일, 접속일) 이후 일정 기간 (ex. 3개월) 구매, 접속하지 않은 상태

 

Churn Rate(%) 구하기

마지막 구매일 구하기

SELECT 
    MAX(orderdate) mx_order -- 마지막 구매일
FROM orders;

 

각 고객의 마지막 구매일 조회

SELECT
    customernumber
    , MAX(orderdate) `마지막 구매일`
FROM orders
GROUP BY 1;

 

DATEDIFF(date1, date2): 두 날짜의 차이를 계산하는 함수

date1 - date2의 결과 출력

SELECT DATEDIFF('2004-11-05', '2004-11-01');

 

SELECT 
    customernumber
    , MX_ORDER 
    , '2005-05-31' -- 전 고객 대상 마지막 구매일
    , DATEDIFF('2005-05-31', MX_ORDER) DIFF 
FROM (
    SELECT 
        customernumber
        , MAX(orderdate) MX_ORDER 
    FROM orders
    GROUP BY 1
) BASE
;

 

CASE WHEN 구문을 사용하여 DIFF가 90이상이면 CHURN, 미만이면 NON-CHURN이라고 하자

SELECT 
	* 
    , CASE WHEN DIFF >= 90 THEN 'CHURN' ELSE 'NON-CHURN' END churn_type
FROM (
    SELECT 
        customernumber
        , MX_ORDER 
        , '2005-06-01' -- 전 고객 대상 마지막 구매일
        , DATEDIFF('2005-06-01', MX_ORDER) DIFF 
    FROM (
        SELECT 
            customernumber
            , MAX(orderdate) MX_ORDER 
        FROM orders
        GROUP BY 1
    ) BASE 
) BASE2
;

 

CHURN RATE 요약 테이블 집계

SELECT 
    CASE WHEN DIFF >= 90 THEN 'CHURN' ELSE 'NON-CHURN' END churn_type
    , COUNT(DISTINCT customernumber) N_CUS
FROM (
	SELECT 
		customernumber
		, MX_ORDER 
		, '2005-06-01' -- 전 고객 대상 마지막 구매일
		, DATEDIFF('2005-06-01', MX_ORDER) DIFF 
	FROM (
		SELECT 
			customernumber
			, MAX(orderdate) MX_ORDER 
		FROM orders
		GROUP BY 1
	) BASE 
) BASE2
GROUP BY 1
;

 

CHURN RATE 계산

0.7041로 약 70%이다.

SELECT 69/(69+29);

 

 

Churn 고객은 어떤 카테고리의 상품을 많이 구매했을끼?

고객별 Churn Table 생성

CREATE TABLE churn_list AS 
SELECT 
	CASE WHEN DIFF >= 90 THEN 'CHURN ' ELSE 'NON-CHURN' END CHURN_TYPE
    , customernumber
FROM 
	(
		SELECT 
			customernumber
			, mx_order
			, '2005-06-01' END_POINT
			, DATEDIFF('2005-06-01', mx_order) DIFF
		FROM
			(
				SELECT 
					customernumber
					, max(orderdate) mx_order
				FROM orders
				GROUP BY 1
			) BASE
    ) BASE
;

SELECT * FROM churn_list;

 

productline별 구매자 수 계산

SELECT 
	C.productline
    , COUNT(DISTINCT B.customernumber) BU
FROM orderdetails A
LEFT 
JOIN orders B
ON A.ordernumber = B.ordernumber
LEFT 
JOIN products C
ON A.productcode = C.productcode
GROUP BY 1
;

 

Churn Type, Product Line별 구매자 수 조회

SELECT 
	D.churn_type
	, C.productline
    , COUNT(DISTINCT B.customernumber) BU
FROM orderdetails A
LEFT 
JOIN orders B
ON A.ordernumber = B.ordernumber
LEFT 
JOIN products C
ON A.productcode = C.productcode
LEFT 
JOIN churn_list D 
ON B.customernumber = D.customernumber
GROUP BY 1, 2
ORDER BY 1, 3 DESC 
;