[PostgreSQL] 매출을 파악하기 위한 데이터 추출1

데이터 확인

 

1. 날짜별 매출 집계

• 시간의 흐름에 따라 일자별로 총 매출, 구매 횟수, 평균 매출을 구한다

SELECT dt
	, COUNT(*) AS purchase_cnt
	, SUM(purchase_amount) AS total_amount
	, AVG(purchase_amount) as avg_amount 
FROM purchase_log
group by dt
order by dt

 

 

2. 이동평균을 사용한 날짜별 추이

• 날짜별 매출을 위에서 구했는데, 이를 시각화한 결과에 주기성이 나타난다면 이동평균을 확인해본다.
• 날짜별 매출과 7일 이동평균을 집계해본다. 이때 날짜별로 변동을 파악하는 것도 중요하므로 날짜별 추이와 이동평균을 함께 표현하는 것이 좋다.
• seven_day_avg : 단순히 현재 날짜로부터 7일을 카운트해서 계산하는데, 첫 6일 동안은 정확히 7일의 이동평균은 아니다.
• 정확히 7일 데이터만 보고 싶을 때는 strict_day_avg를 사용한다.

select dt
	, sum(purchase_amount) as total_amount
	, avg(sum(purchase_amount)) over (order by dt rows between 6 preceding and current row) as seven_day_avg
	, case when 7 = count(*) over (order by dt rows between 6 preceding and current row) 
		  then avg(sum(purchase_amount)) over (order by dt rows between 6 preceding and current row) end as strict_seven_day
from purchase_log
group by 1
order by 1
;

 

 

3. 당월 매출 누계

🎯 월별로 목표를 설정하는 현장에서는 날짜별 매출뿐만 아니라, 해당 월에 어느정도 매출이 누적되었는지 확인해야한다.

• 날짜별로 매출을 집계하고, 해당 월의 누계를 구해보자.

1. 연-월 추출하기 : substring(substr)
2. 날짜별 매출 구하기
3. 월별 매출 구하기 : 윈도우 함수 사용
   각 행의 날짜별 매출합을 합산하되, 연-월 내의 파티션에서(dt를 기준으로 정렬하고)
   ROWS UNBOUNDED PRECEDING 으로 현재 행부터 이전 모든 행까지를 포함하여 누적 합계를 계산

-- 당월 매출 누계 
select dt
	, substring(dt, 1, 7) as year_month
	, sum(purchase_amount) as total_amount
	, sum(sum(purchase_amount)) over (partition by substring(dt, 1, 7) order by dt rows unbounded preceding) as agg_amount
from purchase_log 
group by dt
order by dt

 

 

👉 좀 더 깔끔하게 쿼리를 짜려면 다음과 같이 with 구문을 사용한다.

• year, month, day를 각각 추출하고 concat()으로 합쳐서 표현
• 윈도우 함수로 sum을 하되, partition by에 year, month 둘 다 넣어주기!

with
daily_purchase as (
	select dt
		, substring(dt, 1, 4) as year
		, substring(dt, 6, 2) as month
		, substring(dt, 9, 2) as date
		, sum(purchase_amount) as purchase_amount
		, count(order_id) as orders
	from purchase_log
	group by 1	
)
select dt
	, concat(year, '-', month) as year_month
	, purchase_amount
	, sum(purchase_amount) over (partition by year, month order by dt rows unbounded preceding) as agg_amount
from daily_purchase
order by dt

 

 

✨ TIP. 성능이 조금 떨어지더라도 가독성과 재사용성을 중시해서 쿼리를 작성하는 경우가 많다고 한다.

 

 

4. 월별 매출의 작대비 (전년 동월 대비 상승/하락세)

🎯 일차, 월차, 연차 매출 리포트처럼 다양한 시점의 리포트를 만들게 된다.

🎯 전년 동월과 월별 매출 추이를 추출해보자.

1. 위에서 사용한 daily_purchase 테이블을 활용한다.
2. 연도별로 각각 월별 매출합을 계산해야하기에, groupby 기준은 month로 두되 case when 구문을 사용하여 연도를 구분하여 집계한다.
3. 전년 동월 대비를 비교하기 위해서 100 * (2015년 월별 합/2014년 월별 합)으로 rate를 구한다.

with
daily_purchase as (
	select dt
		, substring(dt, 1, 4) as year
		, substring(dt, 6, 2) as month
		, substring(dt, 9, 2) as date
		, sum(purchase_amount) as purchase_amount
		, count(order_id) as orders
	from purchase_log
	group by 1	
)
select month
	, sum(case year when '2014' then purchase_amount end) as amount_2014
	, sum(case year when '2015' then purchase_amount end) as amount_2015
	, 100.0 
		* sum(case year when '2015' then purchase_amount end)
		/ sum(case year when '2014' then purchase_amount end) as rate
from daily_purchase
group by month
order by month;

 

 

✨ 인사이트 도출 : 매출이 늘어났다고 해도, 계절의 영향인지 이벤트의 영향을 받았는지 알 수 없다.

단, 작년과 비교하여 계절 트렌드로 매출이 늘어난 경우라도 전년 대비 떨어졌다면 성장이 둔화되었다고 판단할 수 있다.

 

 

5.  Z차트로 업적의 추이 확인하기

🎯 고객에게 제공하는 서비스나 제품 중 계쩔에 따라 매출이 변동하는 경우가 있다.

🎯 Z차트는 월차매출, 매출누계, 이동년계 라는 3개의 지표로 구성되어 계절 변동의 영향을 배제하고 트렌드를 분석하는 방법이다.

 

 

1. 월차매출 : 매출 합계를 월별로 집계
2. 매출누계 : 해당 월의 매출에 이전월까지의 매출 누계 합
   → 곡선으로 그래프를 그려 기울기를 보고 완만(매출감소), 급함(매출상승) 파악
3. 이동년계 : 해당 월의 매출에 과거 11개월의 매출을 합한 값
   → 작년과 올해의 매출이 일정하다면 이동년계가 직선이 됨, 오른쪽 위로 올라가면 매출이 상승하는 경향이 있다는 뜻
   → 그래프에 표시되지 않은 과거 1년 동안 매출이 어떤 추이를 가지는지 읽어낼 수 있음

1. 구매 로그를 기반으로 월별 매출 집계
2. 각 월의 매출에 대해 누계 매출과 이동년계 계산

with
daily_purchase as (
	select dt
		, substring(dt, 1, 4) as year
		, substring(dt, 6, 2) as month
		, substring(dt, 9, 2) as date
		, sum(purchase_amount) as purchase_amount
		, count(order_id) as orders
	from purchase_log
	group by 1	
),
monthly_amount as(
	select year
		, month 
		, sum(purchase_amount) as amount
	from daily_purchase
	group by 1, 2
),
calc_index as (
	select year, month
		, amount
		-- 2015년 누계 매출 집계 
		, sum(case when year='2015' then amount end) over (order by year, month rows unbounded preceding) as agg_amount
		-- 이동년계 집계 
		, sum(amount) over (order by year, month rows between 11 preceding and current row) as year_avg_amount
	from monthly_amount
	order by 1, 2
)
-- 2015년 데이터만 압축
select concat(year, '-', month) as year_month
	, amount, agg_amount, year_avg_amount
from calc_index
where year = '2015'
order by 1;