SQL - 식료품 배송 데이터 분석

이번 글에서는 instacart 식료품 배송 데이터를 이용한 분석 실습 방법에 대해 알아보자.

이전 글과 이어지는 내용이니, 데이터 준비를 위해 아래 글을 참조하기 바란다.

SQL - MySQL 데이터 준비

 

 

 

 

 

0. 사용 Schema 지정

USE instacart;

• instacart 스키마를 사용

 

 

 

 

 

1. 상품별 주문 건수

가장 잘 팔리는 상품들과 덜 팔리는 상품들을 알기 위해 상품별 주문 건수를 쿼리해보자.

주문번호와 상품명을 같이 사용하려면 order_products__prior와 products 테이블을 JOIN해야 한다.

SELECT 
	B.product_name
    , COUNT(DISTINCT A.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
;

• 제품별로 쿼리 결과 표시
• 제품별 고유 주문 횟수를 표시
• order_products__prior 테이블로 부터 데이터를 가져옴
• "LEFT JOIN" 구문: "order_products__prior" A 테이블과 "products" B테이블을 "product_id"를 기준으로 JOIN한다.
• "GROUP BY 1": "product_name" 컬럼을 기준으로 그룹핑
• "ORDER BY 2 DESC": 주문 횟수("F")를 내림차순으로 정렬하여 결과를 표시

 

 

 

 

 

2. 첫 구매로 많이 선택되는 상품 Top 10

어떤 상품에 유입 고객들이 많은 지 분석하기 위한 쿼리를 해보자.

SELECT
	A.*
	, ROW_NUMBER() OVER(ORDER BY F_1st DESC) 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
) A
LIMIT 10
;

• 서브쿼리 “A”의 모든 컬럼을 표시
• ROW_NUMBER()을 사용해 "F_1st"를 내림차순으로 정렬하여 순위를 매기고 RNK 컬럼으로 표시
• 서브쿼리 "A"
  • 제품아이디를 컬럼으로 표시
  • "add_to_cart_order"가 1인 경우를 세어 각 제품별로 첫 번째로 추가된 횟수("F_1st")를 계산후 “F_1st” 컬럼으로 표시
  • "order_products__prior" 테이블에서 데이터를 가져옴
• "LIMIT 10": 쿼리결과에서 상위 10개 행만 조회

 

 

 

 

 

3. 시간대별 주문 건수

판촉 활동을 하기 위해 가장 많은 구매가 일어나는 시간대를 파악해보는 쿼리를 진행해보자

SELECT
	order_hour_of_day
    , COUNT(DISTINCT order_id) F
FROM orders
GROUP BY 1
ORDER BY 1
;

• 그 날의 주문 시간대인 “order_hour_of_day” 컬럼 표시
• "COUNT(DISTINCT order_id)"를 사용해 각 시간대별로 고유한 주문의 수를 세어 “F” 컬럼으로 표시
• “orders” 테이블에서 데이터 참조
• "GROUP BY 1": "order_hour_of_day" 컬럼을 기준으로 그룹핑
• "ORDER BY 1": "order_hour_of_day" 컬럼을 기준으로 결과를 정렬

 

 

 

 

 

4. 주문 건당 평균 구매 상품 수 (UPT, Unit per Transaction)

1개의 주문당 평균 얼마나 많은 상품들을 사는지 확인해보는 쿼리를 진행해보자

SELECT
	COUNT(product_id) /COUNT(DISTINCT order_id) UPT
FROM order_products__prior
;

위의 SQL 쿼리는 "order_products__prior" 테이블에서 전체 주문에서 제품 수와 고유 주문 수를 나누어 평균 제품 수를 계산합니다. 쿼리의 내용을 간략하게 요약하면 다음과 같습니다:

• "COUNT(product_id) / COUNT(DISTINCT order_id)”로 주문 건당 평균 구매 상품 수를 계산하고 “UPT" 컬럼으로 표시
• “order_products__prior” 테이블에서 데이터를 가져옴

 

 

 

 

 

5. 부서별 재구매 횟수가 10이상인 제품의 재구매율 Top 10

회사의 물품 재고 관리를 위해 부서별 재구매 횟수가 10이상인 제품들의 재구매율을 계산하여 Top 10 목록을 선정하고자 한다. 그에 대한 쿼리는 다음과 같다.

SELECT *
FROM (
	SELECT
		*
		, ROW_NUMBER() OVER(PARTITION BY department ORDER BY RET_RATE DESC) RNK
	FROM (
		SELECT
			department
			, A.product_id
			, SUM(reordered) / COUNT(*) AS RET_RATE
		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
	) B
	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
;

• 서브쿼리 “BASE”로 부터 데이터를 가져옴
  • 모든 컬럼을 선택하고 WHERE절 조건을 만족하는 행만 표시
  • "ROW_NUMBER() OVER(PARTITION BY department ORDER BY RET_RATE DESC)"을 통해 부서별로 재주문율이 높은 순서로 순위를 부여하고 “RNK" 컬럼으로 표시
  • 서브쿼리 “B”로 부터 데이터를 가져옴
    • department 컬럼 표시
    • “A” 테이블에서 product_id 컬럼표시
    • “SUM(reordered) / COUNT(*)”를 통해 재구매율을 구하고 “RET_RATE” 컬럼으로 표시
    • reordered은 재구매 여부를 0가 1로 구분하고 있어서 전체 합계로 전체 재구매 횟수를 계산가능
    • COUNT(*)는 전체주문 개수
    • "LEFT JOIN" 구문 1: "order_products__prior" A 테이블과 "products" B테이블을 "product_id"를 기준으로 JOIN한다.
    • "LEFT JOIN" 구문 2: "order_products__prior" A 테이블과 "departments" C 테이블을 "department_id"를 기준으로 JOIN한다.
    • “GROUP BY 1, 2”를 통해 부서별, 제품별로 그룹핑
    • WHERE IN 을 사용하여 서브쿼리 “A”를 만족하는 행으로 추림
    • “product_id” 컬럼중 합계가 10이상인 것만 선택
    • 서브쿼리 “A”로 부터 데이터를 가져옴
      • “product_id” 컬럼을 표시
      • “SUM(reordered)”로 재구매횟수를 구하고 “합계” 컬럼으로 표시
      • "order_products__prior" 테이블에서 데이터를 가져옴
      • “GROUP BY 1”을 통해 제품별로 그룹핑
• “RNK”가 10이상인 행만 필터링해서 선택

 

 

 

 

 

6. 주문 건수로 구매자 분석 (10분위 분석)

사용자별로 주문한 주문의 수를 계산하고, 주문 수를 기준으로 순위를 매기는 쿼리를 먼저 작성해보자

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"에서 전체 컬럼 선택
• “ROW_NUMBER() OVER(ORDER BY F DESC)”를 사용해 주문 건수("F")에 따라 내림차순으로 순위를 매긴 뒤 “RNK” 컬럼으로 표시
• 서브쿼리 "A"
  • “user_id” 컬럼 선택
  • “COUNT(DISTINCT order_id)”를 통해 고유한 주문의 수를 세어 “F” 컬럼으로 표시
  • "orders" 테이블에서 데이터를 가져옴
  • "GROUP BY 1"을 통해 고객별로 그룹핑

 

전체 구매자 수가 3159명 이므로 CASE WHEN 구문으로 10분위수를 세팅하고 임시 데이터 테이블로 생성하자

※ 아래 코드는 전체 데이터 개수가 변하게 되면 10분위수 조건인 상수값들을 수정해야하므로 자동화에 불리하다.

실무적인 면에서는 수식으로 특정 조건을 만족하는 테이블에서 데이터의 분위수를 나누는 쿼리를 짜는 것이 좋지만 코드가 길어지고 복잡하므로 여기서는 간단히 상수로만 구현하겠다.

CREATE TEMPORARY TABLE 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 orders
GROUP
BY 1) A) B
;

• “user_quantile” 임시 테이블 생성
• 서브쿼리 “B”로부터 전체 컬럼 표시
• CASE WHEN 절
  • 데이터의 개수에 따라 10분위수로 나누고 “quantile” 컬럼으로 표시
• 서브쿼리 “B”로부터 데이터를 가져옴
• 서브쿼리 “A”로부터 전체 컬럼 표시
• “ROW_NUMBER() OVER(ORDER BY F DESC)”을 통해 고객별 주문 수("F")를 내림차순으로 정렬하고 순서를 매김
• 서브쿼리 “A”
  • “user_id” 컬럼 표시
  • “COUNT(DISTINCT order_id)”로 고유 주문 수를 카운트하고 “F” 컬럼으로 표시
  • orders 테이블로부터 데이터를 가져옴
  • “GROUP BY 1”을 통해 고객 아이디로 그룹핑

 

전체 주문 수 확인

SELECT SUM(F) FROM user_quantile;

• “user_quantile” 테이블로 부터 고유 주문 수(”F”)를 카운트하여 전체 주문수를 구한다.

전체 주문 수는 3220개이다.

 

분위수별 주문 건수 & 비율 확인

SELECT
	quantile AS 분위수
    , SUM(F) AS 주문건수
    , SUM(F) / 3220 AS 비율
FROM user_quantile
GROUP BY 1
;

• “quantile”컬럼을 “분위수”로 표시
• "SUM(F)"를 통해 분위수별 총 주문 건수를 구하고 “주문건수”로 표시
• "SUM(F) / 3220"를 통해 분위수별 총 주문 건수를 전체 주문 건수로 나눠서 “비율”로 표시
• “user_quantile” 임시 테이블로 부터 데이터를 가져옴
• "GROUP BY 1": "분위수" 컬럼을 기준으로 그룹핑

 

 

 

 

 

7. 재구매율 (Retention Rate)

주문건수가 10이하인 상품은 제외하고, 상품별 재구매율을 살펴보자

새롭게 생성한 컬럼에 조건을 주어 필터링 할 때는 WHERE 절이 아니라 HAVING 절을 사용해야한다.

SELECT
	A.product_id
    , B.product_name
	, SUM(reordered) / SUM(1) AS reordered_rate
    , COUNT(DISTINCT order_id) AS F
FROM order_products__prior A
LEFT
JOIN products B
ON A.product_id = B.product_id
GROUP BY A.product_id, B.product_name
HAVING COUNT(DISTINCT order_id) > 10
ORDER BY reordered_rate DESC
;

• 서브쿼리 “A”의 “product_id” 컬럼 표시
• 서브쿼리 “B”의 “product_name” 컬럼 표시
• “SUM(reordered) / SUM(1)”을 통해 재구매율을 계산하고 “reordered_rate” 컬럼으로 표시
• “COUNT(DISTINCT order_id)”로 제품별 고유 주문 횟수를 집계하고 “F” 컬럼으로 표시
• “order_products__prior” 테이블로부터 데이터를 가져옴
• "LEFT JOIN"을 사용하여 "order_products__prior" A 테이블과 "products" B테이블을 “product_id”를 기준으로 조인한다.
• "GROUP BY A.product_id, B.product_name"을 사용하여 제품 ID와 제품 이름으로 결과를 그룹핑
• "HAVING COUNT(DISTINCT order_id) > 10"을 사용하여 주문 건수가 10보다 큰 제품만을 필터링
• "ORDER BY reordered_rate DESC"를 사용하여 재주문율("reordered_rate")을 내림차순으로 정렬

※ 왜 WHERE가 아닌 HAVING을 써야하는가?

• 쿼리 우선 순위가 GROUP BY보다 높기 때문에 WHERE절은 FROM에서 가져온 테이블에서 존재하는 컬럼만 조회가 된다.
• 물론 서브쿼리를 사용하면 WHERE 절을 사용해도 되지만 코드가 불필요하게 길어지므로 비추천하다.
• 쿼리 우선 순위
  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
• WHERE: FROM에서 가져온 기존 테이블의 컬럼에 조건 적용 시 사용
• HAVING: GROUP BY로 새롭게 생성한/그룹핑한 테이블에 조건 적용 시 사용

 

 

 

 

 

8. 분산 분석

식료품의 경우 유통기한이 있어 재구매기간이 다른 소모품보다 빠른 편이다.

그렇다면 고객이 자주 재구매하는 상품은 그렇지 않은 상품보다 일정한 주기를 가질 것이라는 가설을 세울 수 있다.

재구매빈도가 높은 상품과 아닌 상품사이에 재구매까지의 평균 기간이 통계적으로 유의미한 차이가 있는 지 알아보려면…

두 그룹의 평균 차이의 통계적 유의미성을 분산으로 비교할 수 있는 T-Test를 시행해 볼 수 있다.

먼저 분위수별 재구매 소요 기간의 분산을 구하려면 다음과 같은 정보를 결합해 구해야 한다.

• 상품별 분위수 테이블
• 주문 소요 시간: orders 테이블
• 주문 번호 상품번호: order_products__prior

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_RATE DESC) RNK
	FROM (
		SELECT 
			product_id
			, SUM(reordered) / COUNT(*) AS RET_RATE
		FROM order_products__prior
		GROUP BY 1
	) B
) A 
GROUP BY 1, 2
;

• 재구매 상품을 분위수로 나누는 "product_repurchase_quantile" 임시 테이블 생성
• 서브쿼리 “A”에서 “product_id” 컬럼 선택
• CASE WHEN 절
  • 데이터의 개수에 따라 10분위수로 나누고 “RNK_GRP” 컬럼으로 표시
• 서브쿼리 “A”
  • 서브 쿼리 “B”에서 모든 컬럼 선택
  • “ROW_NUMBER() OVER(ORDER BY RET_RATE DESC)”을 사용해 재구매율(”RET_RATE”) 컬럼을 내림차순으로 정렬 후 순서를 매기고 “RNK” 컬럼으로 표시
• 서브쿼리 “B”
  • “product_id” 컬럼 선택
  • “SUM(reordered) / COUNT(*)”로 재구매율을 계산하고 “RET_RATE” 컬럼으로 표시
  • “order_products__prior” 테이블로부터 데이터를 가져옴
  • “GROUP BY 1”을 사용해 제품별로 그룹핑
• “GROUP BY 1, 2”를 사용해 종합 쿼리 결과를 제품별, 분위수별로 그룹핑

 

SELECT 
	RNK_GRP
	, AVG(VAR_DAYS) AVG_VAR_DAYS
FROM (
	SELECT 
		A.RNK_GRP
		, A.PRODUCT_ID
		, VARIANCE(days_since_prior_order) 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
	) BASE
GROUP BY 1
ORDER BY 1
;

• “RNK_GRP” 컬럼 선택
• “AVG(VAR_DAYS)”를 사용해 구매일자에 대한 분산 값(“VAR_DAYS”)열 분위수별로 평균으로 나타내고 “AVG_VAR_DAYS” 컬럼으로 표시
• 서브쿼리 “BASE”
  • A 테이블에서 “RNK_GRP” 컬럼 표시
  • A 테이블에서 “PRODUCT_ID” 컬럼 표시
  • VARIANCE(days_since_prior_order)를 사용해 재주문까지 소요일(“days_since_prior_order”)의 분산을 계산하고 “VAR_DAYS” 컬럼으로 표시
  • “product_repurchase_quantile” A 테이블에서 데이터를 가져옴
  • "LEFT JOIN"을 사용하여 “product_repurchase_quantile” A 테이블과 “order_products__prior” B 테이블을 “product_id”를 기준으로 JOIN한다.
  • "LEFT JOIN"을 사용하여 “order_products__prior” B 테이블과 “orders” C 테이블을 “order_id”를 기준으로 한번 더 JOIN한다.
  • “GROUP BY 1,2”를 사용해 순서 그룹과 제품 ID로 그룹핑
• “GROUP BY 1”를 사용해 순서 그룹으로 전체 쿼리 그룹핑
• “ORDER BY 1”를 사용해 순서 그룹으로 정렬