[SQL] 식품 배송 데이터 분석 - 구매자, 상품 분석 등

1. 구매자 분석
* 10분위 분석
- 고객별로 주문 건수를 계산한 뒤, 주문 건수를 기준으로 각 고객이 어떤 그룹에 속하는지 구한다. 다음으로 각 분위 위수별 주문 건수의 합을 구하면, 전체 주문 건이 어떤 그룹에 얼마나 집중되어 있는지 계산할 수 있다.

1) 주문 건수에 따른 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;

2) 전체 고객 수를 계산하여 10%씩 나누고 임시 테이블 생성
- ex) 전체 고객 수가 3159명이면 1분위는 1~316명까지, 2분위는 317~632명까지 지정하는 식으로 반복

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) a;

3) 앞서 만든 테이블을 기반으로 분위별 주문 비율 구하기

select quantile, sum(f)/3220 f
from user_quantile
group by 1;

 

2. 상품 분석
* 상품별 재구매율과 주문 건수
- 주문 건수가 10건 이하인 상품은 제외
+) sum(1)은 count(1)과 결과가 같으며 이는 각 row마다 1씩 합한다는 의미

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 product_id
having count(distinct order_id) > 10
order by reorder_rate desc;

쿼리 결과

3. 다음 구매까지의 소요 기간과 재구매 관계
- '고객이 자주 재구매하는 상품은 그렇지 않은 상품보다 일정한 주기를 가질 것이다'라는 가정을 세우고 확인
- 재구매율이 높은 순서대로 상품을 10개 분위로 나눈 뒤 각 그룹에서의 구매 소요 기간의 분산을 구함

1) 상품별 재구매율 구한 뒤, 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(reordered)/sum(1) ret_ratio
from order_products__prior
group by 1) a) a
group by 1,2;

2) 각 분위별 재구매 소요 기간의 분산을 구함

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;

select a.rnk_grp,
a.product_id,
variance(days_since_prior_order) 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;

쿼리 결과

3) 각 분위 수의 상품별 재구매 소요 기간 분산의 중위 수를 계산
- MySQL에서는 median 함수를 제공하지 않아 평균으로 대체

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__prior2 b
on a.product_id = b.product_id
group by 1,2) a
group by 1
order by 1;

출처: 'SQL로 맛보는 데이터 전처리 분석'