[Sqlite] 이동평균(moving avg) 계산하기

데이터베이스를 시계열 분석을 하게 되면 이동평균을 계산하는 상황이 많이 발생합니다. 이동평균을 구하면 데이터의 노이즈를 줄여주어 경향성을 파악하는데 도움이 많이 되기 때문이죠.

다음은 Postgre에서 이동평균을 구하는 하나의 예제 sql문입니다. 이를 sqlite 문법을 이용해서 구현할 때 어떤 차이점이 있는지 확인해 보도록 하겠습니다.

1. Postgre

SELECT a.sales_month
  ,a.sales
  ,avg(b.sales) as moving_avg
  ,count(b.sales) as records_count
FROM sales a
JOIN sales b on a.kind_of_business = b.kind_of_business
  and b.sales_month between a.sales_month - interval '11 months' and a.sales_month
  and b.kind_of_business = 'Women''s clothing stores'
WHERE a.sales_month >= '1993-01-01' 
GROUP BY 1,2
ORDER BY 1
;

2. Sqlite

SELECT a.sales_month
  ,a.sales
  ,avg(b.sales) as moving_avg
  ,count(b.sales) as records_count
FROM sales a
JOIN sales b on a.kind_of_business = b.kind_of_business
  and b.sales_month between datetime(a.sales_month, '-12 months') and a.sales_month
  and b.kind_of_business = 'Women''s clothing stores'
WHERE a.sales_month >= '1993-01-01' 
GROUP BY 1,2
ORDER BY 1
;

데이터베이스는 sales라는 테이블을 가지고 있고 sales_month에 해당하는 월로부터 앞에 1년(12개월)치 데이터의 평균값을 구하고자 하는 것입니다. 여기서는 self-JOIN을 이용해서 기능이 구현되었습니다. 데이터가 '1992-01-01'부터 존재하기 때문에 1993년 이전의 데이터는 온전한 12개 데이터의 평균을 구할 수 없습니다. 따라서 분석을 '1993-01-01'이후부터 시작합니다.

Postgre에서는 interval이라는 함수를 사용할 수 있지만 Sqlite에서는 사용할 수 없어 대신 datetime을 이용합니다. 여기에서 주의할 것은 interval을 이용할 경우 11 month를 빼 주지만 datetime을 이용하 경우에는 '-12 months'라고 작성해야 함을 주의해야 합니다.

3. 윈도우 Frame절 이용하기
또 다른 방법으로는 윈도우 frame절을 이용해서 아래와 같이 구현할 수도 있습니다.

SELECT sales_month
,avg(sales) over (order by sales_month 
                 rows between 11 preceding and current row
                 ) as moving_avg
,count(sales) over (order by sales_month 
                   rows between 11 preceding and current row
                   ) as records_count
FROM sales
WHERE kind_of_business = 'Women''s clothing stores'
;