SQL 실무 : 매출 누계, 전년동기 증감율(YoY) 계산

오늘 살펴볼 내용은 매출 데이터를 SQL로 어떻게 핸들링하는지에 대한 부분 입니다. 사실 회사에서는 SQL, Python 등과 같은 프로그래밍 언어가 아닌 엑셀을 이용해서도 매출 관련 데이터 지표를 관리하고 분석을 많이 하곤 합니다. 그렇지만 엑셀의 경우 분석할 수 있는 데이터의 양이 제한되어 있기 때문에 대용량 데이터를 분석할 때 적합하지 못합니다. 그렇기 때문에 지금부터는 SQL을 활용해 데이터를 추출하는 방법을 알아보도록 하겠습니다.

 

오늘 우리가 살펴볼 주제는 다음과 같습니다.

1. 매출 누계 구하기
2. 월별 매출의 전년대비 증감율(YoY) 구하기 

 

해당 주제들은 실제 현업에서 매출 데이터를 관리하는 담당자라면 굉장히 자주 마주치게 되는 데이터이니 유심히 봐주시기 바랍니다!

 

1. 매출 누계 구하기

 

기업의 첫번째 존재 목적은 돈을 벌어 이윤을 창출하는 것이기 때문에 매출 지표는 가장 기본이 되는 매우 중요한 지표라 할 수 있습니다. 만약 KPI 지표 중 매출액과 관련된 지표가 있다면 당연히 달성해야하는 매출 목표가 있겠죠? 그렇기 때문에 매출 데이터를 관리하는 담당자는 특정 시점에 매출이 얼마나 누적되었는지 확인해 목표 달성률이 어느 정도인지 확인할 필요가 있습니다.

 

아래 테이블은 2014년 1월 1일부터 1월 10일까지 기간의 주문자 정보와 매출액 정보를 담고 있습니다. 해당 테이블을 가지고 일자별 매출과 누계 매출을 함께 구해보겠습니다. (모든 데이터를 보여주면 테이블의 ROW가 늘어나는 관계로 일부 데이터만 예시로 넣었습니다.)

 

dt	order_id	user_id	purchase_amount
2014-01-01	1	rhwpvvitou	13900
2014-01-01	2	hqnwoamzic	10616
2014-01-02	3	tzlmqryunr	21156
2014-01-02	4	wkmqqwbyai	14893
2014-01-08	21	zosbvlylpv	13999
2014-01-09	23	zzgauelgrt	16475
2014-01-09	24	qrzfcwecge	6469
2014-01-10	25	njbpsrvvcq	16584
2014-01-10	26	cyxfgumkst	11339

 

일자별 매출을 구하기 위해서는 집계함수인 SUM()과 GROUP BY를 이용해 구해주면 되고, 누계 매출은 윈도우 함수를 사용하여 구해보도록 하겠습니다.

 

SELECT dt
     , SUM(purchase_amount) AS total_amount
     , SUM(SUM(purchase_amount)) OVER(ORDER BY dt ROWS UNBOUNDED PRECEDING) AS cum_amount
FROM purchase_log
GROUP BY dt;

 

여기서는 날짜별 매출 컬럼은 total_amount, 누계 매출 컬럼은 cum_amount 라는 별칭을 만들어서 붙여줬는데요.

SQL을 공부하시면서 윈도우 함수가 아직 익숙하지 않으신 분들이라면 누적 매출을 구하는 OVER(ORDER BY dt ROWS UNBOUNDED PRECEDING) 부분이 낯설거라고 생각됩니다. 

 

이 부분을 조금 풀어서 설명해보면 다음과 같습니다. 윈도우 함수를 처음 보시거나 자주 사용해보지 않으면 이해하기가 어려운 부분입니다. 이해를 돕기 위해 아래 윈도우 함수에 대해 개념을 잡을 수 있는 링크를 남겨놓도록 하겠습니다.

OVER() : 윈도우 함수 사용 시 뒤에 반드시 필수적으로 포함되어야 함
ORDER BY 컬럼명 : 기존 OREDR BY와 마찬가지로 특정 컬럼명 기준으로 정렬 순서 지정이 필요할 때 사용
ROWS : 현재행을 기준으로 윈도우에 몇 개의 행을 포함하는지
UNBOUNDED PRECEDING : 윈도우 시작 위치가 첫 번째 ROW(즉, 첫 번째 ROW 값부터 누적하겠다는 의미) 

 

📺 [YouTube] WINDOW 함수를 이용한 누적 값 구하기 & WINDOW 함수의 WINDOWING 절에 대한 소개 

 

✏️ 실행결과

 

dt	total_amount	cum_amount
2014-01-01	24516	24516
2014-01-02	36049	60565
2014-01-03	53029	113594
2014-01-04	29299	142893
2014-01-05	48256	191149
2014-01-06	29440	220589
2014-01-07	47679	268268
2014-01-08	19760	288028
2014-01-09	22944	310972
2014-01-10	27923	338895

 

위 실행 결과를 통해 일자별 매출 뿐만 아니라 누계 매출도 함께 계산이 된 것을 보실 수 있습니다. 여기서는 매출액에 대해서만 집계를 해봤지만 조금 더 시야를 넓혀보면 누적 구매건수, 구매자수 등 다양한 지표들에 응용해보실 수 있습니다.

 

2. 월별 매출의 전년대비 증감율(YoY) 구하기

 

두 번째로는 월별 매출 데이터에 대한 전년대비 증감율(YoY)을 구해보도록 하겠습니다. 전년대비 증감율은 왜 구하는 것일까요? 그 이유는 단순히 매출액 데이터만 봐서는 인사이트를 찾기가 쉽지 않기 때문 입니다. 그러므로 인사이트를 얻기 위해서는 전년대비 증감율(YoY)을 계산해 당해 년/월/일의 매출액이 어느 정도 수준인지 파악하고, 그에 맞는 대응책을 찾을려고 하는 것 이지요.

 

그럼 전년대비 증감율(YoY)를 계산해보기 위해 2017년부터 2019년도까지 3개년 매출 데이터가 들어 있는 Sales 테이블이 있다고 가정해보겠습니다. 

 

Seq	Member Code	Order No	Sales Date	Store Code	Item Code	Amount
1	999003139	20170102B3794	2017-01-01	BH7637	CDBE622331	536125
2	999003139	20170102B3794	2017-01-01	BH7637	MCAD175132	148348
3	999003139	20170102B3794	2017-01-01	BH7637	CDAE880618	25763
4	999003139	20170102B3794	2017-01-01	BH7637	OFAF575948	36064
5	999003139	20170102B3794	2017-01-01	BH7637	MCAD379156	176378
6	999093158	20170102B39298	2017-01-02	BK5539	MCAD932054	381894
7	999093158	20170102B39298	2017-01-02	BK5539	MCAD381520	297851
8	999093158	20170102B39298	2017-01-02	BK5539	OFAF131100	1665
9	999093158	20170102B39298	2017-01-02	BK5539	CDAE880618	74102
10	999046689	20170103A1429	2017-01-03	AK1414	CDBE642807	372121

 

증감율을 구하기 위해서는 우선 대상이 되는 기준이 있어야 하기 때문에 연/월별 매출액을 구하고, 해당 매출액의 YoY를 계산하는 쿼리를 만들어 보도록 하겠습니다.

 

WITH sales_month AS(
SELECT `sales date`
     , date_format(`sales date`,'%Y') year
     , date_format(`sales date`,'%m') month
     , SUM(amount) total_amt
FROM sales
GROUP BY `sales date`
)
SELECT month
     , SUM(CASE WHEN year = '2017' THEN total_amt END) AS '2017년'
     , SUM(CASE WHEN year = '2018' THEN total_amt END) AS '2018년' 
     , ROUND(SUM(CASE WHEN year = '2018' THEN total_amt END) / SUM(CASE WHEN year = '2017' THEN total_amt END),3) * 100 AS 'YoY1'
     , SUM(CASE WHEN year = '2019' THEN total_amt END) AS '2019년'
     , ROUND(SUM(CASE WHEN year = '2019' THEN total_amt END) / SUM(CASE WHEN year = '2018' THEN total_amt END),3) * 100.0 AS 'YoY2'
FROM sales_month
GROUP BY month;

 

먼저 WITH 구문을 이용해 일자별 기준으로 GROUP BY를 통해 매출을 집계하고, Year, Month 컬럼을 추가하는 임시 테이블을 하나 생성해줍니다. 그리고 해당 임시 테이블을 활용해 월별로 해당하는 연도(2017~2019년)의 매출을 계산해주고 그에 대한 증감율을 계산해주면 아래와 같은 결과를 확인 하실 수 있습니다.

 

✏️ 실행결과

 

month	2017년	2018년	YoY1	2019년	YoY2
1	23256352	19636051	84.4	32639428	166.2
2	19176631	29044295	151.5	27241489	93.8
3	19614054	25283806	128.9	31669894	125.3
4	21484546	25492913	118.7	35762604	140.3
5	26004631	23268282	89.5	34334093	147.6
6	33240270	19127254	57.5	36985625	193.4
7	17008731	23488135	138.1	36144647	153.9
8	24260731	19375675	79.9	39581486	204.3
9	35451319	17620577	49.7	39154439	222.2
10	21592711	23822707	110.3	33611450	141.1

 

오늘은 '매출'을 주제로 실무에서 바로 활용할 수 있는 SQL 쿼리를 살펴보았는데요. 앞으로도 실무에서 빈번하게 사용하는 데이터 분석 지표들을 SQL로 어떻게 추출할 수 있을지 같이 알아보도록 하겠습니다.

 

작성된 내용 중 잘못된 부분이나 궁금한 사항이 있다면 언제든지 피드백 부탁 드립니다.

 

감사합니다.