본 글은 SQL을 공부하기 위해
<SQL로 맛보는 데이터 전처리 분석> 속 예제를 연습하고 기록한 글이다.
매일 배운 내용을 잘 기록해서 제대로 소화하는 게 이 글의 최종 목표이다.
막간 도서 소개
출처 YES 24 (노수영 저)
6장
식품 배송 데이터 분석
1. 데이터 생성하기
https://www.kaggle.com/c/instacart-market-basket-analysis/data
Instacart라는 e-commerce 회사의 데이터를 분석해본다.
해당 데이터 세트는 위의 URL로 들어가 Clone or Download를 클릭해 데이터를 다운 받을 수 있다.
다운로드한 파일은 MySQL Workbench에서 실행한다.
2. 데이터 구조 이해하기
SELECT * FROM aisles; -- 상품 카테고리
SELECT * FROM departments; -- 상품 카테고리
SELECT * FROM order_products__prior; -- 주문번호의 상세 구매 내역
SELECT * FROM orders; -- 주문 대표 정보
SELECT * FROM products; -- 상품 정보
3. 구매 지표 추출
1) 전체 주문 건수, 구매자 수
-- 주문 건수, 구매자 수 구하기
SELECT COUNT(DISTINCT order_id)
, COUNT(DISTINCT user_id)
FROM orders;
주문 건수와 구매자수는 order 테이블에서 중복으로 존재할 수 있기 때문에
DISTINCT를 이용해 중복을 피해준다.
2) 상품별 주문 건수
-- product_id로 2개 테이블 조인
SELECT *
FROM order_products__prior A
LEFT JOIN products B
ON A.product_id = B.product_id;상품별 주문 건수를 구하려면 먼저 LEFT JOIN을 통해
분산된 주문번호와 상품명 정보를 결합해야한다.
-- 상품별 주문 건수 구하기
SELECT product_name
, COUNT(DISTINCT order_id) AS cnt_order
FROM order_products__prior A
LEFT JOIN products B
ON A.product_id = B.product_id
GROUP BY 1
ORDER BY 2 DESC;결합한 결과에서 product_name별로 그룹핑하고
중복없이 order_id를 카운트하면 된다.
3) 카트에 가장 먼저 넣는 상품 10개
-- 상품별로 장바구니에 가장 먼저 담긴 건 수
SELECT product_id,
SUM(CASE WHEN add_to_cart_order = 1 THEN 1 ELSE 0 END) F_1ST
FROM order_products__prior
GROUP BY 1
ORDER BY 1;장바구니에 가장 먼저 담는 상품에 무엇이 있는지 알기 위해서는
order_products_prior 테이블의 add_to_cart_order 컬럼을 주목해야한다.
상품이 장바구니에 몇번째로 담겼는지를 의미하는 컬럼이다.
우선 product_id 별로 장바구니에 가장 먼저 담긴 경우,
(CASE WHEN add_to_cart_order = 1)에는 1을 출력하는 쿼리를 서브쿼리 F_1ST로 만들어야한다.
그래야 F_1ST에서 순서를 매겨서 상위 10개 상품 정보를 나열할 수 있다.
-- F_1ST에서 순서 매기고 10 (서브쿼리)
SELECT *
FROM
(SELECT *,
ROW_NUMBER() OVER(ORDER BY F_1ST DESC) AS RNK
FROM (SELECT product_id,
SUM(CASE WHEN add_to_cart_order = 1 THEN 1 ELSE 0 END) F_1ST
FROM order_products__prior
GROUP BY 1
ORDER BY 1) A) B
WHERE B.RNK BETWEEN 1 AND 10;
여기서 주의해야하는 점은
각 구문의 순서를 정확하게 지켜서 작성해야하는 점이다.
순서가 맞지 않을 경우 쿼리가 실행이 안되거나 오류가 생기기 때문이다.
다시 한번 SQL 기본 구문에 대해 짚고, 그 순서를 이해보도록 하자.
- FROM : 어느 테이블을 사용해?
- WHERE : 테이블에 어떤 필터를 걸어?
- GROUP BY : 필터된 테이블을 어떤 기준으로 그룹을 지어?
- HAVING : 어떤 기준에 맞는 그룹만 산출해?
- SELECT : 그룹을 산출할 때 어떤 컬럼값을 산출해?
- ORDER : 산출한 컬럼값을 어떤 기준으로 정렬해?
상위 10개 상품을 산출할 때 간단하게 LIMIT 10으로 표현할 수도 있다.
SELECT product_id,
SUM(CASE WHEN add_to_cart_order = 1 THEN 1 ELSE 0 END) F_1ST
FROM order_products__prior
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;
4) 시간별 주문 건수
SELECT order_hour_of_day,
COUNT(DISTINCT order_id)
FROM orders
GROUP BY 1
ORDER BY 1;orders 테이블의 order_hour_of_day로 그룹핑하고
order_id를 카운트하여 시간별 주문건수를 구한다.
DISTINCT를 통해 중복 주문 건수를 제거해 준다.
5) 첫 구매 후 다음 구매까지 걸린 평균 일수
SELECT AVG(days_since_prior_order) as avg_recency
FROM orders
WHERE order_number = 2;orders 테이블의 days_since_prior_order는 이전 주문이 이루어진지
며칠 뒤에 구매가 이루어졌느지 나타낸 값이다.
따라서 order_number이 2인 경우에서 days_since_prior_order의 평균값을 구하면 된다.
6) 주문 건당 평균 구매 상품 수 (Unit Per Transaction)
SELECT
COUNT(product_id) / COUNT(DISTINCT order_id) UPT
FROM order_products__prior;product_id를 카운트 해 상품 개수를 구하고 이걸 order_id 카운트 값인 주문 건수로 나누면
주문 1건에 평균적으로 몇 개의 상품을 구매하는지 알 수 있다.
7) 인당 주문 건수
--전체 주문 건수를 구매자 수로 나누어서 계산
SELECT COUNT(DISTINCT order_id) / COUNT(DISTINCT user_id) AVG_F
FROM orders;주문 건당과 동일하게 전체 주문 건수를 구매자 수로 나누면
한 사람 당 평균적으로 몇 개의 상품을 구매하는지 알 수 있다.
8) 재구매율이 가장 높은 상품 10개
-- 상품별 재구매율
SELECT product_id
, SUM(CASE WHEN reordered THEN 1 ELSE 0 END)/COUNT(*) RET_RATIO
FROM order_products__prior
GROUP BY 1;
-- 상품별 재구매율 VER.2
SELECT product_id,
SUM(reordered) / COUNT(*) AS RET_RATIO
FROM order_products__prior
GROUP BY 1
ORDER BY 1;상품 번호로 데이터를 그룹핑하고 재구매 수 (SUM(CASE WHEN reordered THEN 1 ELSE 0 END))를
전체 구매수(COUNT(*))로 나누어 재구매율을 계산해준다.
다른 방법으로는 SUM(reordered) 값을 전체 구매 수로 나누어서 할 수도 있다.
-- 재구매율 랭크(순위) 열 생성
SELECT *
, ROW_NUMBER() OVER (ORDER BY RET_RATIO DESC) RNK
FROM (SELECT product_id
, SUM(CASE WHEN reordered THEN 1 ELSE 0 END)/COUNT(*) RET_RATIO
FROM order_products__prior
GROUP BY 1) A;앞에서 생성한 상품별 재구매율로 순위를 생성한다.
재구매율 순위를 매기려면 앞의 쿼리를 서브쿼리로 생성하고
SELECT 문에서는 순위를 나타내는 ROW_NUMBER 함수로 열을 추가한다.
-- Top 10(재구매율) 상품 추출
SELECT *
, ROW_NUMBER() OVER (ORDER BY RET_RATIO DESC) RNK
FROM (SELECT product_id,
SUM(reordered) / COUNT(*) AS RET_RATIO
FROM order_products__prior
GROUP BY 1) A
LIMIT 10;이제 생성한 순위의 1부터 10까지의 값을 조회해야 한다.
여기에서는 LIMIT 10으로 했지만
WHERE 절로 BETWEEN 1 AND 10으로 표현할 수도 있다.
(번외)
위의 쿼리로 조회해본 결과를 볼 때 상위 10개의 차이가 잘 두드러지지 않는 점을 발견,
재구매한 수량이 50개 이상인 것으로 제한해서 추출해볼 수도 있다.
-- 재구매한 수량 50개 이상인 것 추출
SELECT *
FROM (
SELECT
product_id
, SUM(reordered) AS 합계
FROM order_products__prior
GROUP BY 1
) A
WHERE A.합계 >= 50
ORDER BY 2 DESC;각 제품별로 재구매한 수량을 계산해 A라는 임시 테이블을 만들고
A 테이블을 서브쿼리로 사용해 수량이 50개 이상인 것들을 필터링 할 수 있다.
위처럼 50개 이상인 것들을 펄터링한 뒤,
재구매율에 따라 순위를 매길 수 있다.
-- 재구매율로 랭크(순위) 열 생성하기 (재구매건수가 50개 이상인걸로, 응용)
SELECT
*
, ROW_NUMBER() OVER(ORDER BY RET_RATIO DESC) RNK
FROM (
SELECT
product_id
, SUM(reordered) / COUNT(*) AS RET_RATIO
FROM
order_products__prior
GROUP BY 1
) A
WHERE A.product_id IN (
SELECT product_id
FROM (
SELECT
product_id
, SUM(reordered) AS 합계
FROM
order_products__prior
GROUP BY 1
) B
WHERE B.합계 >= 50
)
;먼저 서브쿼리를 사용하여 각 제품별로 재구매율을 계산하고
이를 A라는 임시 테이블로 만든다.
이후 상품별로 재구매한 건수를 계산한 임시테이블 B를 만들어서
A 테이블에서 B 서브쿼리로 선택된 product_id에 대한 결과를 추출하고
이를 재구매율을 기준으로 순위를 매긴다.
9) Department별 재구매율이 가장 높은 상품 10개
지금까지 상품별로 재구매율을 구한 뒤
재구매율이 가장 높은 상품 10개를 추출했다.
이제는 Department별 재구매율이 높은 상위 10개의 상품을 추출해보자.
SELECT
C.department
, A.product_id
, SUM(reordered) / COUNT(*) AS RET_RATIO
FROM order_products__prior A
LEFT JOIN products B
ON A.product_id = B.product_id
LEFT JOIN departments C
ON B.department_id = C.department_id
GROUP BY 1, 2
ORDER BY 3 DESC;
Department별로 상품의 재구매율을 구하고 재구매율이 높은 순서로 정렬한다.
여기서 중요한 건 order_products_prior 테이블을 기준으로
순차적으로 products와 departments 테이블을 조인하여 테이블을 가져오는 것이다.
-- Department별 재구매율이 가장 높은 상품별 비율 구하기
SELECT *
, ROW_NUMBER () OVER(ORDER BY RET_RATIO DESC) AS RNK
FROM (
SELECT
C.department
, A.product_id
, SUM(reordered) / COUNT(*) AS RET_RATIO
FROM order_products__prior A
LEFT JOIN products B
ON A.product_id = B.product_id
LEFT JOIN departments C
ON B.department_id = C.department_id
GROUP BY 1, 2
) BASE;윈도우 함수 중 ROW_NUMBER() 함수를 사용해
전체 상품에서 재구매율에 따라 랭킹을 매긴다.
기존에 재구매율을 구한 임시 테이블을 FROM 절에 넣어 서브쿼리로 활용한다.
SELECT
*
, ROW_NUMBER () OVER(PARTITION BY department ORDER BY RET_RATIO DESC) AS RNK
FROM (
SELECT
C.department
, A.product_id
, SUM(reordered) / COUNT(*) AS RET_RATIO
FROM order_products__prior A
LEFT JOIN products B
ON A.product_id = B.product_id
LEFT JOIN departments C
ON B.department_id = C.department_id
GROUP BY 1, 2
) BASE
;이 구문은 PARTITION BY를 사용해 각 부서별로 재구매율에 따라 랭킹을 매긴다.
이하 위의 쿼리와 동일하다.
-- WHERE 서브쿼리 추가
SELECT *
, ROW_NUMBER () OVER(PARTITION BY department ORDER BY RET_RATIO DESC) AS RNK
FROM (
SELECT
department
, A.product_id
, SUM(reordered) / COUNT(*) AS RET_RATIO
FROM order_products__prior A
LEFT
JOIN products B
ON A.product_id = B.product_id
LEFT
JOIN departments C
ON B.department_id = C.department_id
GROUP BY 1, 2
) A
WHERE product_id IN (
SELECT product_id
FROM (
SELECT
product_id
, SUM(reordered) AS 합계
FROM
order_products__prior
GROUP BY 1
) A
WHERE 합계 >= 10
)
;부서별로 재구매율을 계산하고 해당 부서의 상품 중 재구매 건수가
10개 이상인 것들만 선택하기 위해 WHERE 절에 서브쿼리를 추가한다.
SELECT *
FROM (
SELECT
*
, ROW_NUMBER() OVER(PARTITION BY department ORDER BY RET_RATIO DESC) AS RNK
FROM (
SELECT
department
, A.product_id
, SUM(reordered) / COUNT(*) AS RET_RATIO
FROM order_products__prior A
LEFT
JOIN products B
ON A.product_id = B.product_id
LEFT
JOIN departments C
ON B.department_id = C.department_id
GROUP BY 1, 2
) A
WHERE product_id IN (
SELECT product_id
FROM (
SELECT
product_id
, SUM(reordered) AS 합계
FROM
order_products__prior
GROUP BY 1
) A
WHERE 합계 >= 10
)
) BASE
WHERE RNK <= 10위의 쿼리 결과 중에서 각 부서별로 재구매율에 따라 랭킹에 매긴 후
상위 10개만 선택한다.
이처럼 서브쿼리는 중간 결과로서 활용해 랭킹을 부여하는 등
필요한 조건에 따라 데이터를 추출하는데에 유용하다.
4. 구매자 분석
고객별로 주문건수를 계산한 뒤 주문건수를 기준으로
각 고객이 어떤 그룹에 속하는지, 10분위 분석을 하고자 한다.
먼저 아래와 같은 방법으로 주문건수에 따른 Rank를 생성한다.
SELECT *
, ROW_NUMBER() OVER (ORDER BY F DESC) RNK
FROM
(SELECT user_id,
COUNT(DISTINCT order_id) F
FROM orders
GROUP BY 1) A;
고객별로 분위수를 매기려면 전체 고객 수를 알아야 한다.
아래와 같은 방법으로 전체 고객 수를 계산한다.
SELECT COUNT(DISTINCT user_id)
FROM (SELECT user_id,
COUNT(DISTINCT order_id) F
FROM orders
GROUP BY 1) A;
각 등수에 따른 분위수는 여러 방식으로 정할 수 있다.
첫번째 방법은 CASE WHEN 구문을 이용하는 것이다.
-- 10분위수 지정
SELECT *
, CASE WHEN RNK BETWEEN 1 AND 316 THEN 'Quantile_1'
WHEN RNK BETWEEN 317 AND 632 THEN 'Quantile_2'
WHEN RNK BETWEEN 633 AND 948 THEN 'Quantile_3'
WHEN RNK BETWEEN 949 AND 1264 THEN 'Quantile_4'
WHEN RNK BETWEEN 1265 AND 1500 THEN 'Quantile_5'
WHEN RNK BETWEEN 1501 AND 1895 THEN 'Quantile_6'
WHEN RNK BETWEEN 1896 AND 2211 THEN 'Quantile_7'
WHEN RNK BETWEEN 2212 AND 2527 THEN 'Quantile_8'
WHEN RNK BETWEEN 2528 AND 2843 THEN 'Quantile_9'
WHEN RNK BETWEEN 2844 AND 3159 THEN 'Quantile_10' END quantile
FROM
(SELECT *
, ROW_NUMBER() OVER (ORDER BY F DESC) RNK
FROM
(SELECT user_id,
COUNT(DISTINCT order_id) F
FROM orders
GROUP BY 1) A) A;
위의 방법은 각 분위수별로 등수를 지정했는데
모든 등수를 기재할 필요없이 부등호로 처리가 가능하다.
FROM 절에는 이전에 주문 건수에 따른 Rank 의 중간 결과를 넣는다.
-- 10분위수 지정 2번째 방법
SELECT *
, CASE WHEN RNK BETWEEN 1 AND 316 THEN 'Quantile_1'
WHEN RNK <= 632 THEN 'Quantile_2'
WHEN RNK <= 948 THEN 'Quantile_3'
WHEN RNK <= 1264 THEN 'Quantile_4'
WHEN RNK <= 1500 THEN 'Quantile_5'
WHEN RNK <= 1895 THEN 'Quantile_6'
WHEN RNK <= 2211 THEN 'Quantile_7'
WHEN RNK <= 2527 THEN 'Quantile_8'
WHEN RNK <= 2843 THEN 'Quantile_9'
WHEN RNK <= 3159 THEN 'Quantile_10' END quantile
FROM
(SELECT *
, ROW_NUMBER() OVER (ORDER BY F DESC) RNK
FROM
(SELECT user_id,
COUNT(DISTINCT order_id) F
FROM orders
GROUP BY 1) A) A;
그러나 위의 두 방법은 모두 상수로 작성되어 있기 때문에
이후에 데이터가 새로 생성되거나 변동될 때 유지보수가 어려운 단점이 있다.
자동화할 수 있도록 다음과 같은 방식을 활용할 수도 있다.
SELECT
*,
CASE WHEN RNK <= (SELECT COUNT(DISTINCT user_id)
FROM
(SELECT
user_id
, COUNT(DISTINCT order_id) F
FROM orders
GROUP BY 1
) A
) / 10 THEN 'Quantile_1'
WHEN RNK <= ((SELECT COUNT(DISTINCT user_id)
FROM
(SELECT
user_id
, COUNT(DISTINCT order_id) F
FROM orders
GROUP BY 1
) A
) / 10) * 2 THEN 'Quantile_2'
END quantile
FROM (
SELECT
*
, ROW_NUMBER() OVER(ORDER BY F DESC) RNK
FROM (
SELECT
user_id
, COUNT(DISTINCT order_id) AS F
FROM
orders
GROUP BY 1
) A
) A
;
핵심은 10분위수를 상수로 구분하지 않고
주문건수를 기준으로 나열된 순위를 이용해 10분위수로 분류하고
전체 사용자의 10분위수보다 작거나 같으면 'Quantile_1'을 할당하는 것이다.
즉 'Quantile_1'은 주문 건수가 많은 상위 10%의 사용자들을 나타낸다.
CREATE TEMPORARY TABLE INSTACART.USER_QUANTILE AS
SELECT *,
CASE WHEN RNK <= 316 THEN 'Quantile_1'
WHEN RNK <= 632 THEN 'Quantile_2'
WHEN RNK <= 948 THEN 'Quantile_3'
WHEN RNK <= 1264 THEN 'Quantile_4'
WHEN RNK <= 1580 THEN 'Quantile_5'
WHEN RNK <= 1895 THEN 'Quantile_6'
WHEN RNK <= 2211 THEN 'Quantile_7'
WHEN RNK <= 2527 THEN 'Quantile_8'
WHEN RNK <= 2843 THEN 'Quantile_9'
WHEN RNK <= 3159 THEN 'Quantile_10' END quantile
FROM
(SELECT *,
ROW_NUMBER() OVER(ORDER BY F DESC) RNK
FROM
(SELECT USER_ID,
COUNT(DISTINCT ORDER_ID) F
FROM INSTACART.ORDERS
GROUP
BY 1) A) A
;
각 분위수별 특성을 파악하기 위해 위의 조회 결과를 하나의 테이블을 생성한다.
테이블명은 user_quantile 이다.
아래와 같이 새로 생긴 테이블을 활용해 전체 주문건수의 합을 구할 수 있다.
-- 각 분위수별 전체 주문 건수의 합을 구하기
SELECT quantile
, SUM(F)
FROM user_quantile
GROUP BY 1;
그리고 전체 주문 건수를 계산해서
해당 분위수별로 주문건수가 얼만큼의 비중을 차지하는지 나누어볼 수 있다.
-- 전체 주문건수 계산, 각 분위수의 건수를 전체 주문 건수로 나누기
SELECT SUM(F)
FROM USER_QUANTILE;
SELECT quantile AS 분위수
, SUM(F) AS 주문건수
, SUM(F) / 3220 AS 비율
FROM user_quantile
GROUP BY 1;
지금까지 분위 수에 따른 주문 건수의 비중을 계산하였다.
5. 상품 분석
이제는 상품별로 재구매를 많이 하는 상품을 알아보고
각 상품의 판매 특성에 대해 알아보자.
먼저 재구매 비중이 높은 상품을 찾아본다.
-- 상품별 재구매율, 주문건수 구하기
SELECT A.product_id
, SUM(reordered) /SUM(1) REORDER_RATE
, COUNT(DISTINCT order_id) F
FROM order_products__prior A
LEFT JOIN products B
ON A.product_id = B.product_id
GROUP BY 1
ORDER BY 2 DESC;
-- 주문건수가 일정 건수(10건) 이하인 상품은 제외
SELECT A.product_id
, SUM(reordered) /SUM(1) REORDER_RATE
, COUNT(DISTINCT order_id) F
FROM order_products__prior A
LEFT JOIN products B
ON A.product_id = B.product_id
GROUP BY 1
HAVING F > 10;
주문 건수가 너무 작은 상품은 찾아보지 않기로 판단,
HAVING을 이용해 일정 건수 이하의 상품을 제외할 수 있다.
조심해야할 건, 해당 필터링은 그룹핑이 완료된 데이터에 조건을 생성한 경우로
FROM에 위치한 테이블에 조건을 거는 WHERE과는 차이가 있다.
-- 상품별로 재구매율이 높은지 보기 위해 products 테이블을 join
SELECT A.product_id
, B.product_name
, SUM(reordered) /SUM(1) REORDER_RATE
, COUNT(DISTINCT order_id) F
FROM order_products__prior A
LEFT JOIN products B
ON A.product_id = B.product_id
GROUP BY 1, 2
HAVING F > 10
ORDER BY 3 DESC;
어떤 상품들이 재구매율이 높은지 보기 위해 product_name 열을 추가한다.
6. 다음 구매까지의 소요 기간과 재구매 관계
'고객이 자주 재구매하는 상품은 그렇지 않은 상품보다 일정한 주기를 가질 것이다.'
라는 가정을 세우고 수치를 살펴보도록 한다.'
재구매율이 높은 순서대로 상품을 10가지 그룹으로 구분하고,
각 그룹에서의 구매 소요 기간의 분산을 구해보자.
-- 상품별 재구매율 구하기
SELECT product_id
, SUM(reordered) / COUNT(*) AS RET_RATIO
FROM order_products__prior
GROUP BY 1;
-- 상품별 재구매율 순위 구하기
SELECT *
, ROW_NUMBER() OVER(ORDER BY RET_RATIO DESC) RNK
FROM (
SELECT product_id
, SUM(reordered) / COUNT(*) AS RET_RATIO
FROM order_products__prior
GROUP BY 1
) A;
-- 상품 재구매율 가장 높은 순서대로 순위 매기기
SELECT *
FROM (
SELECT *
, ROW_NUMBER() OVER(ORDER BY RET_RATIO DESC) RNK
FROM (
SELECT product_id
, SUM(reordered) / COUNT(*) AS RET_RATIO
FROM order_products__prior
GROUP BY 1
) A ) A;
이전 고객 분석에서 했던 10분위 분석과 동일한 방법으로
각 상품을 10개의 그룹으로 나누어 분위수를 할당한다.
-- 상품을 10개의 그룹으로 나누기
CREATE TEMPORARY TABLE product_repurchase_quantile AS
SELECT A.product_id,
CASE WHEN RNK <= 929 then 'Q_1'
WHEN RNK <= 1858 then 'Q_2'
WHEN RNK <= 2786 then 'Q_3'
WHEN RNK <= 3715 then 'Q_4'
WHEN RNK <= 4644 then 'Q_5'
WHEN RNK <= 5573 then 'Q_6'
WHEN RNK <= 6502 then 'Q_7'
WHEN RNK <= 7430 then 'Q_8'
WHEN RNK <= 8359 then 'Q_9'
WHEN RNK <= 9288 then 'Q_10' END RNK_GRP
FROM (SELECT *
, ROW_NUMBER() OVER(ORDER BY RET_RATIO DESC) RNK
FROM (
SELECT product_id
, SUM(CASE WHEN reordered = 1 THEN 1 ELSE 0 END) / COUNT(*) AS RET_RATIO
FROM order_products__prior
GROUP BY 1
) A ) A
GROUP BY 1, 2;
상품별로 분위수를 잘 나누었다면
이제 각 분위수별로 재구매 소요시간의 분산을 구한다.
그러기 위해서는 아래와 같은 정보를 결합해야함.
-- 상품별 분위수 (product_repurchase_quantile)
-- 주문 소요 시간 (orders)
-- 주문번호와 상품번호 (order_products__prior)
CREATE TEMPORARY TABLE order_products_prior2 AS
SELECT product_id,
days_since_prior_order
FROM order_products__prior A
INNER JOIN orders B
ON A.order_id = B.order_id;
우선 order_products__prior 테이블과 orders 테이블을 LEFT JOIN해
product_id 별 days_since_prior_order(구매 소요 기간)를 구한다.
이후 VARIANCE() 함수를 통해 구매 소요 기간의 분산을 계산한다.
-- 분위수, 상품별 구매 소요 기간의 분산 계산
SELECT A.RNK_GRP
, A.product_id
, VARIANCE(days_since_prior_order) AS var_days
FROM product_repurchase_quantile A
LEFT JOIN order_products_prior2 B
ON A.product_id = B.product_id
GROUP BY 1, 2
ORDER BY 1;
우리의 가정은 고객이 자주 재구매하는 상품은 그렇지 않은 상품보다 일정한 주기를 가질 것이라는 점,
이를 확인하기 위해 지금까지
- 재구매율에 따라 상품을 10가지 그룹으로 분할 완료,
- 각 분위수의 상품별 구매 소요 기간의 분산 계산 완료하였다.
이제 각 분위 수의 상품 소요 기간 분산의 중위수를 계산하여
분위수별 구매소요기간에 차이가 존재하는지 확인할 수 있다.
보통 분산의 중위수 비교할 때 MEDIAN() 함수를 이용하나, MySQL에서는 이 함수 제공 안하기 때문에
avg() 함수를 이용해 평균으로 대체한다.
* 그외 방식은 아래와 같다. *
-- 1) 사용자 정의 함수 만들기 (PL/SQL)
-- 2) Python 또는 R이랑 연동해서 DB에 Python으로 데이터를 가져와서 통계처리
SELECT RNK_GRP
, AVG(var_days) avg_var_days
FROM
(SELECT A.RNK_GRP
, A.product_id
, VARIANCE(days_since_prior_order) AS var_days
FROM product_repurchase_quantile A
LEFT JOIN order_products__prior B
ON A.product_id = B.product_id
LEFT JOIN orders C
ON B.order_id = C.order_id
GROUP BY 1, 2) A
GROUP BY 1
ORDER BY 1, 2;
이로써 분위수에 따라 재구매주기의 분산 차이를 살펴보면서
SQL로도 기초 통계 분석을 할 수 있다는 것을 배울 수 있다.
'Database' 카테고리의 다른 글
| PostgreSQL 기초 실습 및 개념 (0) | 2023.10.13 |
|---|---|
| MySQL - UK Commerce 데이터를 이용한 리포트 작성 (0) | 2023.10.12 |
| MySQL - 상품 리뷰 데이터를 이용한 리포트 작성 (1) | 2023.10.10 |
| MySQL 기본구문 - 분석을 위한 조건을 정확하게! 필터 역할 'WHERE' (0) | 2023.10.10 |
| MySQL - 자동차 매출 데이터를 이용한 리포트 작성 (1) | 2023.10.05 |
