5일, 20일 가격 이동 평균 구하기

이동 평균선은 일정 기간에 주가를 산술 평균한 값을 모두 연결해 선으로 표현한 것이다.

보통 5일, 20일, 60일, 120일 범위로 이동 평균선을 보여준다. 

01. 현재 날짜로부터 5일, 20일 이동 평균값 구하기

stock 테이블에서 symbol = 'MSFT"인 일별 주가 데이터에서 현재 날짜로부터 5일 이동 평균값과 20일 이동 평균값을 구한다. a 테이블 기준으로 5일 범위의 b 테이블과 20일 범위의 c 테이블을 LEFT OUTER JOIN한다. 구체적인 조건은 a  테이블을 기준으로 b 테이블에서는 5일 이전의 a 테이블의 현재 날짜까지의 평균값을 계산하고, c테이블에서는 20일 날짜 이전부터 a 테이블의 현재까지의 평균값을 계산한다. SELECT 문에는 기준이 되는 a 테이블에서 date, symbol, close 값을 가져오고, b와 c 테이블에서는 구간의 최소 날짜, 최대 날짜와 평균가를 표시한다.

SELECT

     a.date,

     a.symbol,

     a.[close],

     '' AS '---',

    MIN(b.date) AS day5_start,

    MAX(b.date) AS day5_end,

    AVG(b.[close]) AS day5_close,

     '' AS '---',

    MIN(c.date) AS day20_start,

    MAX(c.date) AS day20_end,

    AVG(c.[close]) AS day20_close

FROM stock AS a

     LEFT OUTER JOIN stock AS b ON a.symbol = b.symbol AND a.date <= dateadd(day, 5, b.date) AND a.date >=b.date

     LEFT OUTER JOIN stock AS c ON a.symbol = c.symbol AND a.date <= dateadd(day, 20, c.date) AND a.date >=c.date

WHERE a.symbol ='MSFT'

GROUP BY a.date, a.symbol, a.[close]

ORDER BY a.date

결과를 보면 5일, 20일 평균값을 계산하므로 기준 테이블 a에서 최소 5일 이상의 데이터를 가지는 행부터 살펴봐야 정상인지 판단할 수 있다.

LEFT OUTER JOIN의 ON 조건에서 a.date <= dateadd(day, 5, b.date) AND a.date >=b.date는

a일부터 a+5일 사이의 날짜를 검색하는 조건을 의미한다. 

 

02. 데이터 오차 방지를 위해 일별 순위를 부여하고 평균 검색하기

위 결과는 정확할까? 전일 대비 상승한 종목을 검색하거나 N일 이상 상승한 종목을 검색할 때 주말이나 공휴일이 포함되면 -1일로 조인할 경우 조인 조건에 맞지 않아 데이터 누락이 발생할 수 있다. 이럴 때는 데이터에 순위를 부여해 누락 없이 전일 데이터를 비교하는 방법을 사용해야 한다. 

WITH cte_avg AS (

SELECT

   ROW_NUMBER() OVER(PARTITION BY symbol ORDER BY date ASC) AS num,

   CONVERT(nvarchar(10), date, 120) AS date,

  symbol,

  CONVERT(DECIMAL(18,2), [close]) AS [close]

FROM stock

WHERE symbol = 'MSFT'

GROUP BY date, symbol, [close]

)

SELECT

     a.date,

      a.symbol,

     a.[close],

    '' AS '---',

    MIN(b.date) AS day5_start,

    MAX(b.date) AS day5_end,

    AVG(b.[close]) AS day5_close,

     '' AS '---',

    MIN(c.date) AS day20_start,

    MAX(c.date) AS day20_end,

    AVG(c.[close]) AS day20_close

FROM cte_avg AS a

     LEFT OUTER JOIN cte_avg AS b ON a.symbol = b.symbol AND a.num <= (b.num + 5) AND a.num >=b.num

     LEFT OUTER JOIN cte_avg AS c ON a.symbol = c.symbol AND a.num <= (c.num + 20) AND a.num >=c.num

WHERE a.symbol ='MSFT'

GROUP BY a.date, a.symbol, a.[close]

ORDER BY a.date

CTE 테이블을 만들 때 ROW_NUMBER()를 사용해 우선 순위를 부여했고 이때 symbol 그룹별로 순위를 부여하기 위해 PARTITION BY문을 함께 사용했다. CTE 테이블 밖의 SELECT 문에 a 테이블을 기준으로 LEFT OUTER JOIN을 했다. 이번에는 조인 조건이 ROW_NUMBER에 부여된 num이다. 

 

03. 엑셀에 데이터를 옮긴 뒤 이동 평균선 확인하기