데이터 마트

매일 공부(ML)·2022년 2월 23일
0

데이터 마트- 분석용 데이터

데이터 마트는 분석에 필요한 데이터를 가공한 분석용 데이터

회원 분서용 데이터 마트

  • 요약 변수: 수집된 데이터를 분석에 맞게 종합한 변수(기간별 구매 금액,횟수, 수량)

  • 파생 변수: 사용자가 특정 조건 혹은 함수로 의미를 부여한 변수(연령대, 선호 카테고리등)

USE PRACTICE;

/****************************************************************************/
/*****************************회원 분석용 데이터 마트******************************/
/****************************************************************************/

/***************회원 구매정보***************/

/* 회원 구매정보 */
SELECT  A.MEM_NO, A.GENDER, A.BIRTHDAY, A.ADDR, A.JOIN_DATE
		,SUM(B.SALES_QTY * C.PRICE) AS 구매금액
        ,COUNT(B.ORDER_NO) 			AS 구매횟수
        ,SUM(B.SALES_QTY)			AS 구매수량
  FROM  CUSTOMER AS A
  LEFT
  JOIN  SALES AS B
    ON  A.MEM_NO = B.MEM_NO
  LEFT
  JOIN  PRODUCT AS C
    ON  B.PRODUCT_CODE = C.PRODUCT_CODE
 GROUP
    BY  A.MEM_NO, A.GENDER, A.BIRTHDAY, A.ADDR, A.JOIN_DATE;

/* 회원 구매정보 임시테이블 */
CREATE TEMPORARY TABLE CUSTOMER_PUR_INFO AS
SELECT  A.MEM_NO, A.GENDER, A.BIRTHDAY, A.ADDR, A.JOIN_DATE
		,SUM(B.SALES_QTY * C.PRICE) AS 구매금액
        ,COUNT(B.ORDER_NO) 			AS 구매횟수
        ,SUM(B.SALES_QTY)			AS 구매수량
  FROM  CUSTOMER AS A
  LEFT
  JOIN  SALES AS B
    ON  A.MEM_NO = B.MEM_NO
  LEFT
  JOIN  PRODUCT AS C
    ON  B.PRODUCT_CODE = C.PRODUCT_CODE
 GROUP
    BY  A.MEM_NO, A.GENDER, A.BIRTHDAY, A.ADDR, A.JOIN_DATE;
 
/* 확인 */ 
SELECT  * FROM CUSTOMER_PUR_INFO;
 

/***************회원 연령대***************/
 
/* 생년월일 -> 나이 */
SELECT  *
		,2021-YEAR(BIRTHDAY) + 1 AS 나이
  FROM  CUSTOMER;

/* 생년월일 -> 나이 -> 연령대 */
SELECT  *
		,CASE WHEN 나이 < 10 THEN '10대 미만'
			  WHEN 나이 < 20 THEN '10대'
              WHEN 나이 < 30 THEN '20대'
              WHEN 나이 < 40 THEN '30대'
              WHEN 나이 < 50 THEN '40대'
              ELSE '50대 이상' END AS 연령대         
  FROM  (
		SELECT  *
				,2021-YEAR(BIRTHDAY) +1 AS 나이
		  FROM  CUSTOMER
		)AS A;

/* CASE WHEN 함수 사용시 주의점(순차적) */
SELECT  *
		,CASE WHEN 나이 < 50 THEN '40대'
			  WHEN 나이 < 10 THEN '10대 미만'
              WHEN 나이 < 20 THEN '10대'
              WHEN 나이 < 30 THEN '20대'
              WHEN 나이 < 40 THEN '30대'
              ELSE '50대 이상' END AS 연령대         
  FROM  (
		SELECT  *
				,2021-YEAR(BIRTHDAY) +1 AS 나이
		  FROM  CUSTOMER
		)AS A; 
 
/* 회원 연령대 임시테이블 */
CREATE TEMPORARY TABLE CUSTOMER_AGEBAND AS
SELECT  A.*
		,CASE WHEN 나이 < 10 THEN '10대 미만'
			  WHEN 나이 < 20 THEN '10대'
              WHEN 나이 < 30 THEN '20대'
              WHEN 나이 < 40 THEN '30대'
              WHEN 나이 < 50 THEN '40대'
              ELSE '50대 이상' END AS 연령대         
  FROM  (
		SELECT  *
				,2021-YEAR(BIRTHDAY) + 1 AS 나이
		  FROM  CUSTOMER
		)AS A;

/* 확인 */ 
SELECT  * FROM CUSTOMER_AGEBAND;


/***************회원 구매정보 + 연령대 임시테이블***************/
CREATE TEMPORARY TABLE CUSTOMER_PUR_INFO_AGEBAND AS
SELECT  A.*
		,B.연령대
  FROM  CUSTOMER_PUR_INFO AS A
  LEFT
  JOIN  CUSTOMER_AGEBAND AS B
    ON  A.MEM_NO = B.MEM_NO;

/* 확인 */ 
SELECT  * FROM CUSTOMER_PUR_INFO_AGEBAND;


/***************회원 선호 카테고리***************/

/* 회원 및 카테고리별 구매횟수 순위 */
SELECT  A.MEM_NO
		,B.CATEGORY
		,COUNT(A.ORDER_NO) AS 구매횟수
		,ROW_NUMBER() OVER(PARTITION BY A.MEM_NO ORDER BY COUNT(A.ORDER_NO) DESC) AS 구매횟수_순위
  FROM  SALES AS A
  LEFT
  JOIN  PRODUCT AS B
	ON  A.PRODUCT_CODE = B.PRODUCT_CODE   
 GROUP
	BY  A.MEM_NO
		,B.CATEGORY;

/* 회원 및 카테고리별 구매횟수 순위 + 구매횟수 순위 1위만 필터링 */
SELECT  *
  FROM  (
		SELECT  A.MEM_NO
				,B.CATEGORY
				,COUNT(A.ORDER_NO) AS 구매횟수
				,ROW_NUMBER() OVER(PARTITION BY A.MEM_NO ORDER BY COUNT(A.ORDER_NO) DESC) AS 구매횟수_순위
		  FROM  SALES AS A
		  LEFT
		  JOIN  PRODUCT AS B
			ON  A.PRODUCT_CODE = B.PRODUCT_CODE
		 GROUP
			BY  A.MEM_NO
				,B.CATEGORY
		)AS A
 WHERE  구매횟수_순위 = 1;

/* 회원 선호 카테고리 임시테이블 */
CREATE TEMPORARY TABLE CUSTOMER_PRE_CATEGORY AS
SELECT  *
  FROM  (
		SELECT  A.MEM_NO
				,B.CATEGORY
				,COUNT(A.ORDER_NO) AS 구매횟수
				,ROW_NUMBER() OVER(PARTITION BY A.MEM_NO ORDER BY COUNT(A.ORDER_NO) DESC) AS 구매횟수_순위
		  FROM  SALES AS A
		  LEFT
		  JOIN  PRODUCT AS B
			ON  A.PRODUCT_CODE = B.PRODUCT_CODE
		 GROUP
			BY  A.MEM_NO
				,B.CATEGORY
		)AS A
 WHERE  구매횟수_순위 = 1;

/* 확인 */ 
SELECT  * FROM CUSTOMER_PRE_CATEGORY;


/***************회원 구매정보 + 연령대 + 선호 카테고리 임시테이블***************/
CREATE TEMPORARY TABLE CUSTOMER_PUR_INFO_AGEBAND_PRE_CATEGORY AS
SELECT  A.*
		,B.CATEGORY AS PRE_CATEGORY
  FROM  CUSTOMER_PUR_INFO_AGEBAND AS A
  LEFT
  JOIN  CUSTOMER_PRE_CATEGORY AS B
    ON  A.MEM_NO = B.MEM_NO;

/* 확인 */ 
SELECT  * FROM CUSTOMER_PUR_INFO_AGEBAND_PRE_CATEGORY;


/***************회원 분석용 데이터 마트 생성(회원 구매정보 + 연령대 + 선호 카테고리 임시테이블)***************/
CREATE TABLE CUSTOMER_MART AS
SELECT  *
  FROM  CUSTOMER_PUR_INFO_AGEBAND_PRE_CATEGORY;
   
/* 확인 */    
SELECT  *
  FROM  CUSTOMER_MART;

데이터 마트- 데이터 정합성

데이터 정합성은 데이터가 서로 모순없이 일관되게 일치함을 나타낼 때 사용이 됩니다.

*방법

1.데이터 마트의 회원 수의 중복은 없는가?

2.데이터 마트의 요약 및 파생변수의 오류는 없는가?

3.데이터 마트의 구매자 비중(%)오류는 없는가?

/***************데이터 마트 회원 수의 중복은 없는가?***************/

SELECT  *
  FROM  CUSTOMER_MART;

SELECT  COUNT(MEM_NO)
		,COUNT(DISTINCT MEM_NO)
  FROM  CUSTOMER_MART;


/***************데이터 마트의 요약 및 파생변수의 오류는 없는가?***************/
  
SELECT  *
  FROM  CUSTOMER_MART;  

/* 회원(1000005)의 구매정보 */
/* 구매금액: 408000 / 구매횟수: 3 구매수량: 14 */

SELECT  SUM(A.SALES_QTY * B.PRICE) AS 구매금액
        ,COUNT(A.ORDER_NO)         AS 구매횟수
        ,SUM(A.SALES_QTY)		   AS 구매수량
  FROM  SALES AS A
  LEFT
  JOIN  PRODUCT AS B
    ON  A.PRODUCT_CODE = B.PRODUCT_CODE
 WHERE  MEM_NO = '1000005';

/* 회원(1000005)의 선호 카테고리 */
/* PRE_CATEGORY: home */
SELECT  *
  FROM  SALES AS A
  LEFT
  JOIN  PRODUCT AS B
    ON  A.PRODUCT_CODE = B.PRODUCT_CODE
 WHERE  MEM_NO = '1000005';
 
 
/***************데이터 마트의 구매자 비중(%)의 오류는 없는가?***************/

/* 회원(Customer) 테이블 기준, 주문(Sales) 테이블 구매 회원번호 LEFT JOIN 결합 */
SELECT  *
  FROM  CUSTOMER AS A
  LEFT
  JOIN  (
		SELECT  DISTINCT MEM_NO
          FROM  SALES
		)AS B
    ON  A.MEM_NO = B.MEM_NO;
   
/* 회원(Customer) 테이블 기준, 주문(Sales) 테이블 구매 회원번호 LEFT JOIN 결합 */
/* 구매여부 추가 */
SELECT  *
		,CASE WHEN B.MEM_NO IS NOT NULL THEN '구매'
			  ELSE '미구매' END AS 구매여부
  FROM  CUSTOMER AS A
  LEFT
  JOIN  (
		SELECT  DISTINCT MEM_NO
          FROM  SALES
		)AS B
    ON  A.MEM_NO = B.MEM_NO;
 
/* 구매여부별, 회원수 */
SELECT  구매여부
		,COUNT(MEM_NO) AS 회원수
  FROM  (
		SELECT  A.*
				,CASE WHEN B.MEM_NO IS NOT NULL THEN '구매'
					  ELSE '미구매' END AS 구매여부
		  FROM  CUSTOMER AS A
		  LEFT
		  JOIN  (
				SELECT  DISTINCT MEM_NO
				  FROM  SALES
				)AS B
			ON  A.MEM_NO = B.MEM_NO
		)AS A
 GROUP
    BY  구매여부; 
 
/* 확인(미구매: 1459 / 구매: 1202) */ 
SELECT  *
  FROM  CUSTOMER_MART
 WHERE  구매금액 IS NULL;
 
SELECT  *
  FROM  CUSTOMER_MART
 WHERE  구매금액 IS NOT NULL; 
profile
성장을 도울 아카이빙 블로그

0개의 댓글