SQL에서 이동평균 구현하는 법

시계열 분석을 통해 장기적인 트렌드를 살펴보고 싶으신가요? 이동 평균의 개념과 이를 SQL에서 구현하는 법을 알고 싶으신가요? 그렇다면 이 글을 읽는 것을 추천드립니다. 이 글을 통해 윈도우 함수를 사용하여 이동 평균을 구하는 법에 관해 살펴보고자 합니다.

 

이동 평균이란?

이동 평균은 데이터의 트렌드를 분석하는데 자주 사용되는 개념입니다. 이동 평균은 종종 움직이는 평균이라고도 불리는데, 그 이유는 시계에 따라 특정 숫자의 앞과 뒷 숫자의 평균을 구하기 때문입니다. 이동 평균의 핵심은 기존 데이터 값의 정적인 평균값을 살펴보기보다는 그런 정적인 평균값들이 시간에 따라 어떤 식으로 변화를 보이는지 확인하는 데 있습니다.

이동 평균을 구하면 단순 평균 값을 보는 것보다 장기적인 트렌드를 읽어내는데 용이합니다. 그래서 해당 개념이 시계열 분석을 할 때 자주 언급되곤 합니다. 이동 평균은 재무 분석 시 자주 사용되므로, 일별 주식 데이터를 가지고 이동 평균이 무엇인지 설명드리고자 합니다.

아래는 우리가 예시로 사용할 stock_price 테이블입니다.

date	price
2020-01-07	1320
2020-01-08	1300
2020-01-09	1300
2020-01-10	1300
...	...
2020-06-24	1086
2020-06-25	1095
2020-06-26	1067
2020-06-27	1067
2020-06-28	1076
2020-06-29	1067
2020-06-30	1067

이 테이블은 2개의 열로 구성되어 있습니다(date 와 price 열). 이렇게 특정 값이 특정 시간과 연관된 것을 시계열 데이터라고 부릅니다. 해당 테이블은 각 날짜마다의 주가를 나타내고 있습니다. 위 테이블에 저장된 주가들을 그래프로 그대로 나타내 보면 아래와 같습니다.

이동 평균을 본격적으로 다루기 전에, 우리가 어떤 데이터를 만지고 있는지 살펴보도록 하겠습니다.

1. stock_price 테이블은 2020년 1월 7일부터 2020년 6월 30일까지의 데이터를 가지고 있습니다.
2. 주가는 대략적으로 1,000부터 ~ 1,400까지의 값 사이에 분포하고 있습니다. (그래프의 y축 참고)
3. 주가의 변동이 심했던 구간이 존재합니다. 2월 달에 주가가 치솟았었고, 6월 달도 마찬가지입니다.

 

그럼 본격적으로 주가의 이동 평균을 구해보도록 하겠습니다. 이동 평균을 이용해 그래프를 나타내면 기존 그래프와 어떻게 다른지 함께 살펴보도록 하죠. 이번 예시에서는 주가의 3일 이동 평균을 구할 것입니다. 그러기 위해서는 특정 날짜의 주가와 이틀 전 주가(1일 전 주가와 2일 전 주가)들의 평균을 구하면 됩니다. 그림으로 이동 평균을 구하는 법을 보시면 좀 더 쉽게 이해가 되실 겁니다.

1월 9일의 이동 평균은 해당 일과 이틀 전의 주가들을 가지고 구합니다. 그래서 1,300(1월 9일 주가), 1,300(1월 8일 주가), 1,320(1월 7일 주가)을 더한 후 3으로 나누었습니다. 고로, 1월 9일의 이동 평균은 1,306.66입니다. 이런 식으로 나머지 날짜들도 구해주면 됩니다. 6월 30일의 이동 평균을 구해보도록 할까요? 해당 날자의 3일 이동 평균은 1,070입니다. 그 이유는 6월 30일의 주가(1,067)와 1일 전 주가(1,067)와 2일 전 주가(1,076)를 더한 후 3으로 나눈 값이 그 값이기 때문입니다.

이런 식으로 구한 주가의 3일 평균을 기존 주가 그래프와 나란히 두면 아래와 같은 그래프가 형성됩니다.

빨간색 그래프가 우리가 방금 구한 3일 이동 평균의 그래프입니다. 파란색 그래프는 각 날짜의 주가를 나타낸 그래프이고요. 빨간색 그래프가 파란색 그래프보다 좀 더 부드럽고 뾰족한 부분이 없다는 점을 발견하셨나요? 이렇게 그래프를 부드럽게 해주는 것이 바로 이동 평균을 분석에 활용하는 이유입니다. 다시 말해, 이동 평균은 데이터에서 잡음을 없애주죠. 데이터의 잡음이 덜하면, 그만큼 시계열 분석 시 트렌드를 읽어내기 용이하기 하답니다.

방금 살펴본 예시는 3일에 대한 이동 평균이었지만, 여러분이 원하는 기간만큼의 이동 평균을 구할 수도 있습니다. 7일 이동 평균을 구할 수도 있고, 10일, 30일 등 이동 평균의 기본적인 개념만 이해하고 있다면 어떤 기간의 이동 평균을 구하든지 큰 어려움이 없을 것입니다.

 

이동 평균의 쓰임

앞에서 말했듯이 이동 평균은 데이터 내의 장기간 및 단기간 트렌드를 읽어낼 때 주로 사용합니다. 그래서 주가와 같이 주로 재무 분야에서 많이 활용됩니다. 만약 오늘의 주가가 지금까지의 이동 평균보다 높다면 주가는 상향세 있다고 판단하고, 만약 이동 평균보다 낮다면 하향세에 있다고 봅니다. 이런 상향 트렌드를 통해 증권 매매업자가 주식을 사고파는 결정을 내릴 수 있도록 합니다. 이외에도 이동 평균을 적용하는 비즈니스 케이스는 다양합니다.

1. 매출 분석 : 이동 평균을 통해 일별 및 주별 매출 속 튀는 값들을 부드럽게 만들어 줄 수 있습니다.
2. COVID 19 확진자 분석: 이동 평균을 통해 시간이 지남에 따라 확진 추세가 상향세 혹은 하향세인지 판단할 수 있습니다.
3. 웹 트래픽 분석: 아동 평균을 통해 웹 페이지가 고객들의 관심을 지속적으로 받고 있는지 확인할 수 있습니다.

 

SQL로 이동평균 구현하는 법

이제 이동 평균이 무엇인지 알았으니 이를 SQL에서 구하는 법에 관해 배워보도록 하겠습니다. SQL에서 이동 평균을 구하고 싶다면 윈도우 함수를 사용하면 됩니다. 윈도우 함수는 특정 범위의 행을 가로질러 그 행 안에 있는 값들을 집계할 수 있도록 도와주는 특별한 SQL 함수입니다.

데이터를 집계해주는 부분에선 GROUP BY와 유사하지만, 윈도우 함수는 기존 행을 없애지 않은 상태에서 집계 값을 구할 수 있습니다. 하지만, 이동 평균을 구하기 위해서는 윈도우 함수에다가 집계 함수가 실행되어야 할 행의 특정 범위를 제한해줘야 합니다. 이를 창(frame) 혹은 윈도우를 설정한다고 말합니다. 만약, 3일 이동 평균을 구하고 싶다면 해당 날짜와 이전 이틀 날짜만 평균값으로 계산 될 수 있도록 그들의 앞뒤를 '창'으로 막는 것이죠. 그리고, 윈도우 함수가 행을 가로질러 그 행 안에 있는 값들을 집계한다고 말했는데, 이는 각 행마다 창을 설정한 후 그 범위 내에서만 계산을 한다는 의미입니다. 그래서 윈도우 함수는 테이블의 마지막 행에 다다를 때까지 매 행마다 새로이 실행됩니다.

윈도우 함수와 창을 이용해 3일 이동 평균을 구하는 쿼리문은 다음과 같습니다.

SELECT
 *,
 AVG(price) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM stock_price;

쿼리문을 뜯어서 살펴보도록 하죠. 첫째, 우리는 OVER 절을 사용함으로써 윈도우 함수를 실행한다는 것을 알 수 있습니다. 둘째, 우리의 창의 범위는 3입니다. 3일 이동 평균을 구하고 싶으니까요. 이는, 각 행마다 자기 자신과 이전 2개 행에 관한 평균을 구해야 한다는 의미입니다. 우리는 창을 설정하고자 ROW 키워드를 사용했습니다. ROWS BETWEEN 2 PRECEIDING AND CURRENT ROW 라는 문장을 통해 윈도우 함수의 창이 3이라는 것을 말해주었습니다. 해당 문장을 번역하면 "이전 2개의 행과 현재 행 사이" 정도로 나타낼 수 있는데, 이게 곧 우리가 바라는 창의 범위를 말해주고 있습니다. 셋째, 앞에서도 말했듯이 각 행마다 창이 생깁니다. 각 행마다 창이 설정되기 때문에, 그 범위 내에서만 계산을 할 수 있습니다. 아래 그림을 통해 우리 예시 속 1월 9일(초록색 영역)과 6월 28일(파란색 영역)의 창이 어떤 식으로 제한되는지 보실 수 있습니다.

해당 그림에서 몇 가지 언급드리고 싶은 것이 있습니다.

첫째, 날짜 간의 공백이 없는 것이 중요합니다. 각 날짜마다 자기 자신과 이틀 전의 값의 평균을 구하는 것이기 때문에 날짜에 공백이 생기면 정확한 이동 평균을 구할 수 없게 되고, 분석 자체가 말이 안 됩니다. 날짜에 공백이 있는데 어떻게 N일 이동 평균이라고 부를 수 있겠습니까?

둘째, OVER절 안의 ORDER BY는 날짜를 시간 순서대로 정렬시켜 줍니다. 이를 실행하지 않는다면, 날짜별로 정렬이 되지 않은 데이터베이스에서는 정확한 N일 이동 평균을 구할 수 없습니다. 따라서 여러분이 이동 평균을 구한다면, 반드시 OVER절에 ORDER BY를 사용하여 날짜를 제대로 정렬시켜 주세요. 

이제 SQL에서 이동 평균을 구하는 법을 아시겠죠? 해당 쿼리문은 여러분이 그대로 가져가 활용할 수 있습니다. 그저 테이블 명, 열 이름, 창의 범위를 여러분이 희망하는 대로 바꿔만 주시면 됩니다. 아무래도 윈도우 함수가 이동 평균을 구하는데 아주 핵심적인 역할을 하다보니, 윈도우 함수를 배우지 않은 분께는 이동 평균을 구하는 방법이 쉽지 않을 수 있습니다. 

 

특정 기간 동안의 이동평균 구하기

지금까지는 3일 이동 평균을 구해보았습니다. 3일 이외에도 다른 N일 이동 평균을 구할 수 있습니다. N의 숫자가 커질수록, 그래프는 완만해집니다. N의 숫자가 작아질수록, 그래프는 기존 값의 그래프와 비슷한 형상을 가지게 됩니다. 그래서 똑같은 데이터를 다룬다 하더라도, 그것으로 2일 이동 평균을 구하느냐 혹은 30일 이동 평균을 구하느냐에 따라 그래프의 모양이 확연히 달라집니다. 얼마나 확연한 차이가 있는지 직접 구해보도록 합시다.

SELECT
 *,
 AVG(price) OVER(ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS 2day_moving_average,
 AVG(price) OVER(
 ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS 30day_moving_average
FROM stock_price;

그리고 각각의 이동 평균을 그래프로 나타내 보도록 하죠.

초록색 그래프는 30일 이동 평균을 나타내는 반면, 빨간색 그래프는 2일 이동 평균을 나타냅니다. 2일 이동 평균 그래프는 거의 로우 데이터를 그래프로 나타낸 파란색 그래프와 별반 차이가 없습니다. 하지만 초록색 그래프는 훨씬 부드럽고 장기간 트렌드를 읽어내기에 훨씬 수월합니다. N일 이동 평균에서 N의 숫자가 커질수록 그래프는 부드러워집니다. 그렇다면 이상적인 N값은 무엇일까요? 사실 이건 답이 없습니다. 이은 여러분의 비즈니스 상황과 필요에 따라 달라지는 부분이니까요.

 

7일 이동 평균 구하기

이번에는 COVID 19의 확진자 수 데이터를 통해 확진자 수의 7일 이동 평균을 구해볼 것입니다. 아래 confirmed_covid 테이블은 특정 나라의 일자별 확진자 수를 담고 있습니다. 테이블을 보시면서 어떻게 하면 확진자 수의 7일 평균을 구할 수 있을지 고민해보세요.

country	date	confirmed
...	...	...
Croatia	2020-02-20	0
Croatia	2020-02-21	0
Croatia	2020-02-22	0
Croatia	2020-02-23	0
Croatia	2020-02-24	0
Croatia	2020-02-25	1
Croatia	2020-02-26	2
Croatia	2020-02-27	0
Croatia	2020-02-28	2
Croatia	2020-02-29	1
...	...	...
Croatia	2020-03-12	0
Croatia	2020-03-13	13
Croatia	2020-03-14	6
Croatia	2020-03-15	11
Croatia	2020-03-16	8
...	...	...
Croatia	2020-07-18	116
Croatia	2020-07-19	92
Croatia	2020-07-20	25
Croatia	2020-07-21	52
Croatia	2020-07-22

확진자 수의 7일 이동 평균을 구하는 쿼리문은 다음과 같습니다.

SELECT
 *,
 AVG(confirmed) OVER(
 ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS 7day_moving_avgerage
FROM confirmed_covid;

우리는 7일 이동 평균을 구하고 싶기 때문에 창의 범위를 현재 행과 이전 6개의 행으로 정의하였습니다.

확실히 이동 평균의 장점이 보이지 않나요? 튀는 부분을 부드럽게 만들어줌으로써 확진자 수의 장기적인 트렌드를 보다 확인하기 쉽도록 만들어 주었습니다. 이것이 바로 이동 평균을 분석에 활용하는 이유라는 것을 잊지 마세요.

 

원문 : What Is a Moving Average?