[PostgreSQL] 사용자 로그데이터(Log Data) 매출분석(2) - DECILE분석 (사용자 그룹화 하기)

해당 포스팅은 아래 포스팅에 이어서 진행합니다.

ysyblog.tistory.com/146

[PostgreSQL] 사용자 로그데이터(Log Data)분석(1) - 사용자들의 특징 찾기(사용자 그룹화, 카테고리별

 

Decile 분석(Ntile)

-Decile분석이란 데이터를 10단계로 분할해서 중요도를 파악하는 분석입니다.(해당 데이터에서 중요도라 함은 구매액이 될것입니다.)

- 같은 수로 데이터 그룹을 나누어야 하는데, 이때 쓰는 함수가 Ntile() 입니다.

NTILE(숫자) OVER (PARTITION BY 조건1 ORDER BY 조건2)

with user_purchase as(
	select customerid
		,sum(price) as amount
	from online_sales
	where country = 'United Kingdom'
	Group by customerid
)
, decile as(
select customerid
	,amount
	,ntile(10) over (order by amount desc) as decile
from user_purchase)
select * 
from decile

- 구입액에 따라 사용자들은 1분위 부터 10분위 까지 나뉘어진다.

이제 위 정보를 활용하여 분위별로 구매액을 집계하고, 구성비와 구성비누계를 계산하겠습니다.

with user_purchase as(
	select customerid
		,sum(price) as amount
	from online_sales
	where country = 'United Kingdom'
	Group by customerid
)
, decile as(
select customerid
	,amount
	,ntile(10) over (order by amount desc) as decile
from user_purchase)
, decile_amount as (
	select decile
	,round(sum(amount)::numeric,2) as decile_amount
	,round(avg(amount)::numeric,2) as avg_amount
	,round(sum(sum(amount)::numeric) over (order by decile),2) as cumulative_amount
	,round(sum(sum(amount)::numeric) over(),2) as total_amount
from decile
group by decile
)
select decile
	,decile_amount
	,avg_amount
	,round(100.0 * decile_amount / total_amount,2) as total_ratio
	,round(100.0 * cumulative_amount / total_amount,2) as cumulative_ratio
from decile_amount;

 

해당 포스팅은 다음의 책을 활용하여 작성하였습니다.

- 가사키 나가토, 다미야 나오토,  『데이터 분석을 위한 SQL 레시피』, 한빛미디어, p186~232