[Sqlite] YoY, MoM, DoD 분석하기

Sqlite를 이용해서 시계열 분석 중 YoY, MoM, DoD 등 구간비교를 하는 방법에 대해서 정리해 보도록 하겠습니다.

 

특정 시간 구간의 데이터를 이전 시간의 구간 데이터와 비교함으로써 많은 이점을 얻을 수 있는데 계절성등을 확인할 때 유용하게 사용할 수 있습니다.

실제로 많은 데이터들이 과거 같은시점, 전 달의 같은 날짜 등과 같이 1년, 1달, 혹은 1일 주기로 데이터를 비교하며 증감 추이를 신중하게 관찰하는 것을 알 수 있습니다.
각각을 YoY(Year over Year, 전년 대비 증감률), MoM(Month over Month, 전월 대비 증감률), DoD(Day over Day) 전일 대비 증감률이라는 용어로 구분해서 부르고 있죠.

계절성(seasonality)은 일정한 간격을 두고 규칙적으로 반복되는 패턴을 의미합니다.

특히나 사람들이 년단위로 주기적으로 구매하는 옷이나 음식, 여가활동등에서 그 계절성이 뚜렷하게 나타나는 경향이 있습니다.

Sqlite를 이용해서 계절성을 활용하기 위해서 lag라는 함수를 사용합니다.

sales 테이블에서 sales_month에는 연도-월의 Date 정보가 저장되어 있으며, 월별 데이터 별도로 구분하기 위해서 이 중에서 월 데이터를 별도로 추출해야 합니다. 이 때 strftime 함수를 이용합니다.

SELECT sales_month, sales
,lag(sales_month) over (partition by strftime('%m', sales_month)
                       order by sales_month
                       ) as prev_month
,lag(sales) over (partition by strftime('%m', sales_month)
                 order by sales_month
                 ) as prev_month
FROM sales
WHERE kind_of_business = 'Book stores'
;

strftime 함수의 첫번째 인자 '%m'은 두번째 인자로 전달하는 sales_month라는 데이터를 포맷을 월 데이터만 출력하도록 하라 라는 함수입니다.

이렇게 포맷을 변경한 뒤 partition by를 실행하면 각 월별로 데이터가 묶이게 되고 이는 sales_month를 기준으로 정렬된 데이터이기 때문에 그룹 중 lag를 통해서 참조되는 하나 앞의 데이터는 전년도 같은 달의 데이터가 되는 것입니다.

이를 출력하면 각 월별로 sales 데이터와 1년전 날짜와 그 때의 sales 데이터가 나란히 출력됨을 확인할 수 있습니다.

이를 조금 더 응용하여 전년 대비 매출의 절댓값 차이와 비율을 구해보면 다음과 같습니다.

SELECT sales_month, sales
,sales - lag(sales_month) over (partition by strftime('%m', sales_month)
                               order by sales_month
                               ) as absolute_diff
,(sales * 1.0 / lag(sales) over (partition by strftime('%m', sales_month)
                                 order by sales_month) - 1) * 100
                                 as pct_diff
FROM sales
WHERE kind_of_business = 'Book stores'
;

위와 같이 나타내면 작년 같은 월 대비 sales 차이 및 이를 퍼센트로 비교할 수 있습니다.  기본적으로 정수 데이터를 나눠주기 때문에 결과도 정수로 나오게 됩니다.  퍼센트값을 구하기 위해서는 1.0을 곱해서 double 타입으로 자동변환을 시켜주어야 정확한 데이터를 구할 수 있습니다.