[PostgreSQL] 카테고리별 매출 파악(ABC분석, 매출변화율, 도수분포표(히스토그램))

이번포스팅에서는 카테고리별 매출을 파악하는 것을 해보겠습니다.

 

- 분석할 데이터와 데이터를 DB에 import하는 방법은 아래 링크에서 보실 수 있습니다.

ysyblog.tistory.com/143

[PostgreSQL] CSV File를 Table에 삽입(Import)하기.

 

- 매출 시계열 분석은 아래 링크에서 보실 수 있습니다.

ysyblog.tistory.com/144

[PostgreSQL] 매출데이터 시계열 분석하기(이동평균분석, 월별, Z차트분석)

 

월별 카테고리별 매출

먼저 월별 각 카테고리별 매출을 집계하는 쿼리입니다.

select 
	substring(date::text, 6, 2) as months
	,category
	, sum(amount) as total_amount
	
from sale_data
group by months, category
order by months, category

ROLLUP를 사용해서 월별 매출합계도 동시에 구할 수 있습니다.

select 
	substring(date::text, 6, 2) as months
	,category
	, sum(amount) as total_amount

from sale_data
group by ROLLUP(months, category)
order by months, category

 

ABC분석

- 재고관리나 매출 중요도에 따라 상품을 나누고 전략을 만들때 활용

- A등급 : 상위 0% ~ 70%, B등급 : 상위 70% ~ 90%, C등급 : 상위 90% ~ 100%

- 데이터를 작성하는 방법은 매출높은 순서대로 정렬한다음 합계를 집계한 후 각 항목이 차지하는 비율(구성비)을 계산합니다. 그리고 계산한 카테고리의 구성비로 구성비누계를 구합니다.

with month_sales as (	
	select 
		category
		, sum(amount) as amount
	from sale_data
	where date between '2019-01-01' and '2019-01-31'
	group by category
)
, sales_ratio as (
	select 
		category
		,amount
		,round(100.0 * amount / sum(amount) over(),2) as compositon_ratio -- 구성비
		,round(100.0 * sum(amount) over(order by amount desc rows between unbounded preceding and current row)
		/ sum(amount) over(),2) as cumulative_ratio
	from month_sales
)
select *
	,case when cumulative_ratio between 0 and 70 then 'A'
	when cumulative_ratio between 70 and 90 then 'B'
	ELSE 'C' 
	END as abc_analysis
from sales_ratio
order by amount desc;

 

카테고리별 매출 변화율

월별로 카테고리별 매출이 어떻게 변화하는지 보여주는 쿼리입니다.

이를 활용하여 팬차트를 만들 수도 있습니다.(팬차트란 어떤 시점을 100%로 하고, 이후의 숫자변동을 확인할 수 있게 해주는 그래프입니다.)

기준이 되는 매출이 가장 첫 월의 매출이므로 "first_value" 함수 사용

with
day_category_sales as(
	select
		date
		,category
		,substring(date::text, 1, 7) as year_month -- 연월 추출
		,substring(date::text, 1, 4) as year
		,substring(date::text, 6, 2) as month
		,substring(date::text, 9, 2) as day
		,sum(amount) as amount
	from sale_data
	group by date,category
)
,month_category_sales as(
	select
		concat(year, '-', month) as year_month
		,category
		,sum(amount) as amount
	from day_category_sales
	group by year,month,category
)
select
	year_month
	,category
	,amount
	,first_value(amount) 
		over(partition by category order by year_month, category rows unbounded preceding) as base_amount
	,round(100.0 * amount / first_value(amount) 
		over(partition by category order by year_month, category rows unbounded preceding),2) as rate
from month_category_sales
order by year_month, category;

 

상품의 가격분포(도수분포표)

상품의 가격분포가 어떻게 되어있는지 확인하는 도수분포표를 만드는 차례입니다. 이를 활용하여 히스토그램을 만들 수 있습니다.

먼저 가격마다 계층을 구해주겠습니다.

with stats as(
	select
	--계급판정 로직이 <계급상한미만>이 적용되어 최대값은 마지막 계급의 범위를 넘어가기 때문에 1을 더해서 이를 방지한다.
		max(price)+1 as max_price
		,min(price) as min_price
		,max(price)+1 - min(price) as range_price
		,10 as bucket_num -- 계급 수
	from sale_data
)
,sales_bucket as (
	select price
		,min_price
		,price - min_price as diff
		,1.0 * range_price / bucket_num as bucket_range -- 계층범위(금액범위를 계층수로 나눈 것)
		,Floor(1.0 * (price-min_price) / (1.0 * range_price/bucket_num)) + 1 as bucket --계층판정
	from stats, sale_data
)
select *
from sales_bucket
order by price;

참고로 PostgreSQL에서는 width_bucket함수를 활용해서 계층을 판정할 수 있습니다.

with stats as(
	select
		max(price) as max_price
		,min(price) as min_price
		,max(price) - min(price) as range_price
		,30 as bucket_num -- 계급 수
	from sale_data
)
,sales_bucket as (
	select price
		,min_price
		,price - min_price as diff
		,1.0 * range_price / bucket_num as bucket_range -- 계층범위(금액범위를 계층수로 나눈 것)
		,WIDTH_BUCKET(price, min_price, max_price, bucket_num) as bucket
	from stats, sale_data
)
select *
from sales_bucket
order by price;

 

이제 계층의 상한값과 하한값을 정하여 도수분포표를 만듭니다.

with stats as(
	select
	--계급판정 로직이 <계급상한미만>이 적용되어 최대값은 마지막 계급의 범위를 넘어가기 때문에 1을 더해서 이를 방지한다.
		max(price)+1 as max_price
		,min(price) as min_price
		,max(price)+1 - min(price) as range_price
		,10 as bucket_num -- 계급 수
	from sale_data
)
,sales_bucket as (
	select price
		,min_price
		,price - min_price as diff
		,1.0 * range_price / bucket_num as bucket_range -- 계층범위(금액범위를 계층수로 나눈 것)
		,WIDTH_BUCKET(price, min_price, max_price, bucket_num) as bucket
	from stats, sale_data
)
select bucket
	,min_price + bucket_range * (bucket -1) as lower_limit
	,min_price + bucket_range * bucket  as upper_limit
	,count(price) as num_purchase
	,sum(price) as total_amount
from sales_bucket
group by bucket, min_price, bucket_range
order by bucket;

 

하지만 이렇게 소수점까지해서 계층을 구분하는것은 직감적이지 않습니다. 따라서 고정값을 기반으로 임의이 계층너비로 변경할 수 있게 하겠습니다.

- 최대값을 8000000으로 정하고  계층개수는 8개로 하겠습니다.

with stats as(
	select
	--계급판정 로직이 <계급상한미만>이 적용되어 최대값은 마지막 계급의 범위를 넘어가기 때문에 1을 더해서 이를 방지한다.
		8000000 as max_price
		,0 as min_price
		,8000000 as range_price
		,8 as bucket_num -- 계급 수
	from sale_data
)
,sales_bucket as (
	select price
		,min_price
		,price - min_price as diff
		,1.0 * range_price / bucket_num as bucket_range -- 계층범위(금액범위를 계층수로 나눈 것)
		,WIDTH_BUCKET(price, min_price, max_price, bucket_num) as bucket
	from stats, sale_data
)
select bucket
	,min_price + bucket_range * (bucket -1) as lower_limit
	,min_price + bucket_range * bucket  as upper_limit
	,count(price) as num_purchase
	,sum(price) as total_amount
from sales_bucket
group by bucket, min_price, bucket_range
order by bucket;

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

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