PostgreSQL - 주식 월평균 전년대비 쿼리

앞서, 유니슨 주식의 일별 가격을 토대로 이평선을 구현한 쿼리를 구현해봤다.(http://augustines.tistory.com/78)

주식의 월봉을 전년도와 비교해보는 쿼리를 만들어보자.  생각해보니, 월봉은 아니다. 봉이라는 개념이 월 시초가와 월 시종가로 이루어지니... 어쨌든 월평균의 이동을 전년도와 비교해보는 것도 의미는 있을듯 하다. 일단 데이터는 아래와 같은 데이터가 있다. 이전 이평선을 구할 때 사용했던 stock_price 테이블에 2015년도 데이터만 가공해서 추가하였다.

INSERT INTO stock_price
VALUES
    ('2015-01-01',  'unison', 3200)
  , ('2015-01-02',  'unison', 3310)
  , ('2015-01-03',  'unison', 3250)
  , ('2015-01-04',  'unison', 3410)
  , ('2015-01-05',  'unison', 3430)
  , ('2015-01-08',  'unison', 3410)
  , ('2015-01-09',  'unison', 3430)
  , ('2015-01-10',  'unison',  3450)
  , ('2015-01-11',  'unison', 3510)
  , ('2015-01-12',  'unison',  3390)
  , ('2015-01-15',  'unison',  3380)
  , ('2015-01-16',  'unison', 3300)
  , ('2015-01-17',  'unison', 3290)
  , ('2015-01-18',  'unison',  3320)
  , ('2015-01-19',  'unison', 3380)
  , ('2015-01-22',  'unison',  3380)
  , ('2015-01-23',  'unison', 3390)
  , ('2015-01-24',  'unison',  3400)
  , ('2015-01-25',  'unison', 3410)
  , ('2015-01-26',  'unison',  3420)
  , ('2015-01-29',  'unison', 3460)
  , ('2015-01-30',  'unison',  3470)
  , ('2015-01-31',  'unison', 3490)
  , ('2015-02-01',  'unison',  3500)
  , ('2015-02-02',  'unison', 3550)
  , ('2015-02-03',  'unison', 3560)
;

먼저, 2014년과 2015년의 데이터를 집계하고, 월마다 Group by 를 적용해서 월 평균 가격을 계산한다. 월 평균 가격을 계산할 때, SUM 함수 내부에 case 식을 사용해서 2014년도와 2015년 데이터를 압축하면, 2014년, 2015년의 월 평균 가격을 각각의 컬럼으로 출력할 수 있다.

with
daily_stock as (
	SELECT
		dt
		, substring(dt, 1, 4) as year
		, substring(dt, 6, 2) as month
		, substring(dt, 9, 2) as date
		, SUM(price) as price
	FROM stock_price
	GROUP BY dt
) SELECT 
	month
	, SUM(CASE year WHEN '2014' THEN price END) as month_2014
	, SUM(CASE year WHEN '2015' THEN price END) as month_2015
	, ROUND(SUM(CASE year WHEN '2015' THEN price END) / SUM(CASE year WHEN '2014' THEN price END) * 100 , 2) AS rate
FROM daily_stock
GROUP BY month
ORDER BY month;

데이터가 1월, 2월로 한정했기 때문에 두개의 레코드 밖에 없지만 12월까지 데이터를 만들면 작년 대비 월봉의 변화를 확인할 수 있다. 데이터 만들기가 참 귀찮다. 다음엔 실제의 주식 데이터를 크롤링 해서 만들어봐야 겠다.