[sql] 카테고리별 매출 계산 (ABC 분석, 팬차트, 히스토그램)

 

 

 

카테고리별 매출 소계 집계

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 분석

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;

 

팬차트

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 -- 계층범위(금액범위를 계층수로 나눈 것)
		,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;