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

이번 포스팅에서는 매출데이터를 시계열 분석해보겠습니다.

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

ysyblog.tistory.com/143

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

 

날짜별 매출 분석

날짜별로 매출과 평균구매액을 집계하는 쿼리입니다.

select date
	,count(*) as purchase_count
	,sum(amount) as total_amount
	,avg(amount) as avg_amount
from sale_data
group by date
order by date;

이동평균 분석

날짜별 추이를 보기 위한 이동평균 분석입니다.(7일 기준)

- seven_day_avg_2는 이동평균을 조금더 정확히 표현한 것입니다. 7일이 되기 전에는 7일 평균을 낼 수 없으므로 null 처리합니다.

select date
	,sum(amount) as total_amount
	,avg(sum(amount)) over(order by date rows between 6 preceding and current row) as seven_day_avg
	,case when 7 = count(*) over(order by date rows between 6 preceding and current row)
	then avg(sum(amount)) over(order by date rows between 6 preceding and current row)
	end as seven_day_avg_2
from sale_data
group by date
order by date;

 

월별 누계매출 집계

월별 누계매출을 집계하기 위해 년/월을 날짜에서 추출합니다. 여기서 주의해야할 점은 date타입이 date이기 때문에 text로 형변환을 해주어야 substring함수가 작동됩니다.

select date
	, substring(date::text, 1, 7) as year_months -- 연월 추출
	,sum(amount) as total_amount
	,sum(sum(amount)) over(partition by substring(date::text,1, 7) order by date rows unbounded preceding)
	as agg_amount
from sale_data
group by date
order by date;

월별로 매출이 누적되며, 월이 바뀌면 초기화 후 다시 누적된다.

With 문을 사용하여 좀더 가독성 좋게 만들 수 있습니다.

with day_sales as(
	select
		date
		,substring(date::text, 1, 7) as year_months -- 연월 추출
		,sum(amount) as total_amount
	from sale_data
	group by date
)
select *
	,sum(total_amount) over(partition by year_months order by date rows unbounded preceding)
from day_sales
order by date;

 

Z차트를 위한 데이터 집계

매출데이터는 보통 계절에 따라 변동하는 경우가 있는데, Z차트는 계절변동의 영향을 배제하고 트렌드를 분석하는 방법입니다.

Z차트의 특징

- Z차트는 월매출, 매출누계, 이동년계 3가지 지표가 필요합니다.

- 월차 매출이 일정하면 매출누계는 직선이 되며, 그래프의 기울기가 급해진다면 최근 매출이 상승하고 있다는것이며, 완만해지고 있으면 최근 매출이 감소하고 있는 것입니다.

- 데이터에는 대부분 2019년 데이터밖에 없으므로 이동년계는 코드만 구현을 해 놓았습니다.(2018년 데이터가 없기 때문에 매출누계와 이동누계는 같습니다.)

with day_sales as(
	select
		date
		,substring(date::text, 1, 7) as year_months -- 연월 추출
		,substring(date::text, 1, 4) as years
		,substring(date::text, 6, 2) as months
		,substring(date::text, 9, 2) as days
		, sum(amount) as amount
	from sale_data
	group by date
	order by date
)

, month_sales as (
	select year_months,years, months, sum(amount) as total_amount
	from day_sales
	group by year_months, years, months	
)

 , cumulative as (
 	select year_months
 		,years
 		,months
 		,total_amount
 		,sum(case when years='2019' then total_amount end) 
        	over(order by years, months rows unbounded preceding) as agg_amount -- 2019년의 매출누계
 		,sum(total_amount) 
        	over(order by years, months rows between 11 preceding and current row) as year_avg_amount -- 이동년계집계

 	from month_sales
 	order by years, months
 )
 select year_months
 	,total_amount
 	,agg_amount
 	,year_avg_amount
 from cumulative
 order by year_months;

 

매출과 관련한 지표들

매출과 관련된 지표는 여러가지가 있습니다. 여기서는 주문총계, 당월매출, 누적매출, 전월대비 매출 상승률 등을 알아볼 것입니다.

with day_sales as(
	select
		date
		,substring(date::text, 1, 7) as year_months -- 연월 추출
		,substring(date::text, 1, 4) as years
		,substring(date::text, 6, 2) as months
		,sum(amount) as amount
		,count(order_id) as orders
	from sale_data
	group by date
	order by date
)

, month_sales as (
	select year_months,years, months
		,sum(orders) as orders
		,avg(amount) as avg_amount
		,sum(amount) as month_amount
	from day_sales
	group by year_months, years, months	
)

select year_months
 	,orders
 	,avg_amount
 	,month_amount
	,sum(month_amount) over(partition by years order by months rows unbounded preceding) as agg_amount -- 누적매출
	,lag(month_amount,1) over(order by year_months) as last_month-- 지난달 매출
	,round(100.0 * month_amount / lag(month_amount,1) over(order by year_months),1)-100 as rate --지난달 대비 매출 비율
 from month_sales
 order by year_months;

 

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

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