[sql] 작대비 매출 비교

우리는 기업을 평가할때 매출을 중요 평가 요소 중 하나로 본다. 그러나 매출을 평가할때는 절대적인 매출의 크기보다는 상대적인 매출의 크기가 더 중요하다. 우리는 매출액의 비교를 통해 기업이 성장했는지, 또는 침체기에 있는지 판단하곤한다.

 

이때, 비교대상도 중요하다. 오늘 매출과 전일자의 매출을 비교할수도있지만, 오늘은 대형 프로모션을 진행했고 어제는 어떠한 프로모션도 없었다면 해당비교는 크게 의미가 없을수도 있다. 각기 다른 맥락속에서 산출된 매출액이기에 비교 대상으로 적절하지 않기 때문이다.

 

이런점을 고려해서 매출 비교를 할때는 많은 경우 작년 대비 동월 매출 비교를 하곤한다. 작년 동월에는 기업이 비슷한 맥락속에 있었을 것이라 가정하는 것이다. 그래서 이번 게시글에서는 매출 비교의 기본인 작년대비(aka 작대비) 동월 매출 비교를 구하는 쿼리를 알아보고자한다.

---

아래와 같은 월별 매출액 테이블이 주어졌을때, 어떻게 작대비 동월 매출 비교를 할수있을까?

 

처음에는 self join을 이용해서 동월 매출 비교를 진행하려했지만,  lag() 함수와 window 함수를 이용하면 좀더 가독성있는 쿼리를 작성할 수 있어 해당방법을 소개한다. 해당 쿼리에서 고려해야할 사항은 3가지이며, 1번이 작대비 매출 비교의 핵심이다.

 

① 1년전 동월 매출 구하기 ( lag함수 + window 함수)

② 작대비 차액 및 성장률 계산

③ 작대비 동월 데이터가 없다면 제외

 

postgres에는 lag()라는 함수가 존재하는데, 해당 함수를 이용하면 현재 행 이전의 행에 대한 값을 가져올 수 있다. 함수를 사용할때는 값을 가져올 칼럼의 이름, 행의 위치, 그리고 데이터 정렬기준이 필요하다. 해당정보는 "lag('칼럼명', '행의 위치') over(order by '정렬기준')" 와 같은 형태로 작성해주면 된다.

 

 예를들어, "lag(month_day, 12) over(order by month_day asc)" 라고 작성했다면,

1. month_day(월 칼럼)을 오름차순으로 정렬한 뒤에 

2. 현재행 기준으로 12개 이전행의 일자(년월)값을 가져오겠다는 의미이다.

 

이는 1년전의 일자(년월)을 가져오겠다는 말과 동치이다.

 

그리고 "lag(sum_amount, 12) over(order by month_day asc)" 라고 작성한다면

 

1. month_day(월 칼럼)을 오름차순으로 정렬한 뒤에 
2. 현재행 기준으로 12개 이전행의 매출값을 가져오겠다는 의미이다.

이는 1년전 일자(년월)의 매출값을 가져오겠다는 말과 동치이다. 이를 모두 이용해서 쿼리를 작성하면 아래와같이 작성할 수 있다.

select month_day, sum_amount as curr_amount,
	lag(month_day, 12) over(order by month_day asc) as prev_month_1year,
	lag(sum_amount, 12) over(order by month_day asc) as prev_amount_1year
from temp_01;

 

쿼리를 실행하면, 아래와 같이 1년전의 일자와 매출값이 우측에 붙여진것을 확인할 수 있다 (null로 표현된 부분은 12개 이전행의 데이터가 없기때문에 발생한다). 

 

이렇게 출력된 테이블을 이용해서 작대비 매출 차액과 성장률 또한 구해볼 수 있다. 계산식은 아래와같이 쉽게 작성할 수 있다.

(*매출 성장률:  (현재 매출액 - 1년전 매출액) / 1년전 매출액)

select *,
	curr_amount - prev_amount_1year as diff_amount,
	round(curr_amount/prev_amount_1year - 1, 2) as prev_growth_pct
from temp_02;

 

그리고 위 테이블 형태를보면, 1년전 데이터가 없는 경우 prev_month_1year 칼럼과 prev_amount_1year 칼럼이 null 처리된것을 확인할수 있다. 이 경우는 작대비 동월 매출 데이터가 불가능한 데이터이므로, 조건문으로 데이터에서 제외해준다.

select *,
	curr_amount - prev_amount_1year as diff_amount,
	round(curr_amount/prev_amount_1year - 1, 2) as prev_growth_pct
from temp_02
where prev_month_1year is not null
;

 

최종적으로 완성된 쿼리는 아래와 같다.

-- 1. 월별매출 구하기
with 
temp_01 as (
select date_trunc('month', order_date)::date as month_day
	, sum(amount) as sum_amount
from nw.orders a
	join nw.order_items b on a.order_id = b.order_id
group by date_trunc('month', order_date)::date
),
-- 2. 1년전 동월 매출 구하기
temp_02 as(
select month_day, sum_amount as curr_amount,
	lag(month_day, 12) over(order by month_day asc) as prev_month_1year,
	lag(sum_amount, 12) over(order by month_day asc) as prev_amount_1year
from temp_01
)
-- 3. 작대비 동월 매출 비교(차액, 성장률)
select *,
	curr_amount - prev_amount_1year as diff_amount,
	round(curr_amount/prev_amount_1year - 1, 2) as prev_growth_pct
from temp_02
where prev_month_1year is not null
;