SQL 4. SQL 활용_데이터 마트

<데이터 마트>

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

 

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

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

 

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 *, 2023-YEAR(BIRTHDAY)+1 AS 나이
FROM CUSTOMER;

/* 생년월일 -> 나이 -> 연령대 */
SELECT * , CASE WHEN 나이 < 20 THEN '청소년'
WHEN 나이 < 60 THEN '청장년'
                WHEN 나이 < 80 THEN '노년'
ELSE '건강주의' END AS 연령층
FROM (
SELECT *, 2023-YEAR(BIRTHDAY)+1 AS 나이
FROM CUSTOMER
        ) AS A;
        
/* CASE WHEN 함수 사용시 주의점(순차적) */ /* 10, 20, 30 대가 반환되지 않는다. */
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 * , CASE WHEN 나이 < 20 THEN '청소년'
WHEN 나이 < 60 THEN '청장년'
                WHEN 나이 < 80 THEN '노년'
ELSE '건강주의' END AS 연령층
FROM (
SELECT *, 2023-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;

/* ROW_NUMBER : 같은 값이어도 고유한 순위를 반환한다. */

/* 회원 및 카테고리별 구매횟수 순위 + 구매횟수 순위 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;
/*ROW NUMBER 사용해서 고유한 순위를 주었기 때문에 중복되는 회원은 없다.*/

/* 회원 선호 카테고리 임시테이블 */
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_AGEBRAND_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_AGEBRAND_PRE_CATEGORY;

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

/* 확인 */    
SELECT  *  FROM  CUSTOMER_MART;

 

 

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

데이터 적합성은 데이터가 서로 모순 없이 일관되게 일치함을 나타낼 떄 사용된다.

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

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

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

 

 

 

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

/* 확인 */    
SELECT  *  FROM  CUSTOMER_MART;

/*********************************데이터 정합성**********************************/
SELECT * FROM CUSTOMER_MART;
/* DISTINCT : 중복없이 */ 
SELECT COUNT(MEM_NO), COUNT(DISTINCT MEM_NO)
FROM CUSTOMER_MART;

/* 회원(1000005)의 구매정보 */
/* 구매금액: 408000 / 구매횟수: 3 구매수량: 14 */
SELECT MEM_NO, SUM(SALES_QTY*PRICE) AS 구매금액,
COUNT(A.ORDER_NO) AS 구매횟수,
        SUM(SALES_QTY) AS 구매수량
FROM SALES AS A
LEFT JOIN PRODUCT AS B
ON A.PRODUCT_CODE = B.PRODUCT_CODE
WHERE MEM_NO = '1000005';

/* 회원(1000005)의 선호 카테고리 */
SELECT * 
FROM SALES A
LEFT JOIN CUSTOMER_PRE_CATEGORY 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; /*구매*/