[sql] 이동평균

주식시장에서는 기술적 분석을 할때 '이동평균선(moving average)'를  기본적인 도구로 사용한다. 이동평균선은 최근 N일치의 평균가격을 구한 값으로 주가 흐름을 파악하기 위해 사용된다.

 

아래 사진을 보면 차례대로 5일, 20일, 60일, 그리고 120일 이동평균선이 표기되어있다. 이는 각 일자의 최근 N일 동안의 평균주가를 이어놓은 선이라고 보면 되겠다. 예를들어, 5일 이동평균선은 '5일간의 종가 합계 / 5일' 로 계산하며, 이 평균 주가를 이어놓은 선이 '5일 이동 평균선'이다.

 

KOSPI 200 TR

 

sql에서도 윈도우함수(window function)을 이용하면 위와 같은 이동평균을 구할수 있다. 실습 테이블은 주가 테이블 대신 매출테이블을 이용한다.

먼저, 이동평균 매출을 구하기전에 아래와같이 월별 총 매출을 구해서 서브쿼리로 작성한다. 주문일자가 담겨있는 orders 테이블과 매출정보가 담겨있는 order_items 테이블을 join해서 필요한 정보를 가져왔다.

 

with 
tmp as (
	select date_trunc('day', order_date)::date as d_day,
		round(sum(amount)) as sum_amount
	from nw.orders o
		left join nw.order_items oi on o.order_id = oi.order_id 
	group by date_trunc('day', order_date)::date
	order by 1
)

 

월별 총 매출

 

 

그 다음, 위에서 구한 서브쿼리를 이용해서 이동평균 매출을 구한다.

 

이동평균매출은 avg(매출) over(orderby rows between ① N-1일 preceding and ②현재행) 의 형태로 작성해주면된다. 이는 매출의 평균을 구할건데, 평균의 범위를 어떻게 설정할건지 윈도우함수(over 이후 부분)로 지정한 형태이다.

 

5일 이동평균 매출은 해당일자를 포함해서 최근 5일치의 매출 평균치를 구하는 것이므로, (현재행 일자 - 4일) ~ (현재행 일자) 동안의 매출을 평균내줘야 한다. 예를들어, 7월 12일의 5일 이동평균을 구하고싶다면 아래에서 빨간 박스의 영역 매출 평균을 구한것이 5일 이동평균 매출이 되겠다.

 

 

이를 이용해서 5일 이동평균을 구하는 쿼리는 아래와 같다. 만약 7일 이동평균을 구하고싶다면 4대신 6을 기입하면 된다.

select *,
	round(avg(sum_amount) over(order by d_day rows between 4 preceding and current row)) as m_avg_5days
from tmp 
;