SQL을 활용하여 구매 주기 확인하기_MySQL

안녕하세요!

SQL을 활용하여 구매주기를 구할 수 있습니다.
구매주기를 구하는 방법을 찾다가 발견한 강의에서 배웠습니다.
제가 사용한 데이터셋은 Kaggle에 있는 US E-commerce 2020입니다.

우선 구매 주기를 알기 위해서는 구매를 2회 이상 완료해야 한다는 조건이 있습니다.
첫 번째 구매로부터 다음 구매까지의 기간을 확인하는 것이기 때문입니다.

2회를 초과하면 전체 구매 횟수를 나누게 되어 평균 구매 주기로 볼 수 있습니다.
SQL을 활용한 쿼리를 보겠습니다.

- 전체 쿼리

WITH orders as (
  SELECT customer_id
       , CASE WHEN DATE_ADD(min_dt, INTERVAL 1 DAY) <= max_dt THEN 'Y' ELSE 'N' END reorder
       , DATEDIFF(max_dt, min_dt) dt_diff
       , CASE WHEN cnt - 1 = 0 OR DATEDIFF(max_dt, min_dt) = 0 THEN 0
         ELSE DATEDIFF(max_dt, min_dt) / (cnt - 1) END order_cycle
  FROM (
    SELECT customer_id
         , MAX(order_date) max_dt
         , MIN(order_date) min_dt
         , COUNT(DISTINCT order_id) cnt
    FROM records
    GROUP BY 1) t
)

SELECT ROUND(AVG(order_cycle)) avg_order_cycle
FROM orders
WHERE order_cycle > 0

- 세부 설명
1. 먼저 records 테이블에서 customer_id 별로 마지막 구매일과 최초 구매일, 그리고 주문 횟수를 구합니다.
이 쿼리는 FROM 절에서 서브쿼리로 사용되어 새로운 테이블의 역할을 할 것입니다.

SELECT customer_id
     , MAX(order_date) max_dt
     , MIN(order_date) min_dt
     , COUNT(DISTINCT order_id) cnt
FROM records
GROUP BY 1

2. 위에서 customer_id, 마지막 구매일, 최초 구매일, 주문횟수를 구하는 테이블을 만들었습니다.
이 테이블에서 다시 customer_id, 1) reorder 컬럼, 2) dt_diff 컬럼, 3) order_cycle 컬럼을 구할 것입니다.
(최소 1일 이후에 구매한 것을 재구매로 볼 것입니다.)

1) CASE WHEN, DATE_ADD 를 활용하여 최초 구매일에서 1일 지났을 때가 마지막 구매일보다 작으면
재구매를 했음을 표시(Y, N) 하는 구문을 작성합니다.
(재구매 여부를 표시하기 위함이므로 쓰지 않아도 무관합니다.)

2) DATEDIFF 함수로 나타나는 것은 마지막 구매일과 최초 구매일의 일수 차이를 나타낸 것입니다.
(이 또한 쓰지 않아도 구매주기의 결과에는 무관합니다.)

3) CASE WHEN, DATEDIFF 를 활용하여 구문을 작성합니다.
주문 횟수 - 1 = 0 이거나 마지막 구매일 - 최초 구매일 = 0 일 경우
주문 횟수가 1 또는 마지막 구매일과 최초 구매일이 같다는 의미이기에 0으로 반환합니다.
그렇지 않으면 마지막 구매일에서 최초 구매일의 일수 차이를 주문횟수 - 1 로 나눈 결과를 반환합니다.
주문횟수에서 1을 빼는 이유는 최초 구매를 제외하기 때문입니다.

SELECT customer_id
     , CASE WHEN DATE_ADD(min_dt, INTERVAL 1 DAY) <= max_dt THEN 'Y' ELSE 'N' END reorder
     , DATEDIFF(max_dt, min_dt) dt_diff
     , CASE WHEN cnt - 1 = 0 OR DATEDIFF(max_dt, min_dt) = 0 THEN 0
       ELSE DATEDIFF(max_dt, min_dt) / (cnt - 1) END order_cycle
FROM (
    SELECT customer_id
         , MAX(order_date) max_dt
         , MIN(order_date) min_dt
         , COUNT(DISTINCT order_id) cnt
    FROM records
    GROUP BY 1) t

3. WITH 문을 사용하여 1, 2에서 작성한 쿼리를 orders 라는 이름의 테이블로 생성합니다.

WITH orders as (
  SELECT customer_id
      , CASE WHEN DATE_ADD(min_dt, INTERVAL 1 DAY) <= max_dt THEN 'Y' ELSE 'N' END reorder
      , DATEDIFF(max_dt, min_dt) dt_diff
      , CASE WHEN cnt - 1 = 0 OR DATEDIFF(max_dt, min_dt) = 0 THEN 0
        ELSE DATEDIFF(max_dt, min_dt) / (cnt - 1) END order_cycle
  FROM (
    SELECT customer_id
        , MAX(order_date) max_dt
        , MIN(order_date) min_dt
        , COUNT(DISTINCT order_id) cnt
    FROM records
    GROUP BY 1) t
  )

4. 그리고 전체 고객의 평균 구매 주기를 보고 싶다면 아래와 같이 쿼리를 작성하고
WHERE 절에 order_cycle > 0 인 경우라는 조건을 주어 집계되도록 합니다.
결과값을 ROUND 함수로 소수자리 없이 반올림하였습니다.

SELECT ROUND(AVG(order_cycle)) avg_order_cycle
FROM orders
WHERE order_cycle > 0

참고 : https://www.boostcourse.org/ds102/lecture/373476?isDesc=false

기초 데이터 분석을 위한 핵심 SQL