[SQLITE] 일목균형표 구하기

일목 균형표 구하는 방법에 대해 구현해본다.

전환선 ( 파란색)은 (9주기 high + 9주기 low)/2
기준선 (빨간색) 은 (26주기 high + 26주기 low)/2
선행 스팬 A (구름의 녹색 경계)은 (전환선 + 기준선)/2
선행스팬 B (구름의 빨간색 경계)은 (52주기 high + 52주기 low)/2
후행 스팬 (녹색)은 지난 26개월동안 표시된 종가입니다.

SQLITE 에서는 함수의 개념이 없어 스칼라 쿼리로 작성하여 본다.

SELECT reg_date
        , future_cd
        , OPEN
        , High 
        , Low 
        , CLOSE
        , Adj_Close 
        , Volume
        , (SELECT MAX(HIGH) FROM (SELECT * FROM futures_info FI WHERE FI.REG_DATE <= MFI.REG_DATE ORDER BY FI.REG_DATE DESC LIMIT 9)) HIGH_9
        , (SELECT MIN(LOW) FROM (SELECT * FROM futures_info FI WHERE FI.REG_DATE <= MFI.REG_DATE ORDER BY FI.REG_DATE DESC LIMIT 9)) LOW_9
        , (SELECT MAX(HIGH) FROM (SELECT * FROM futures_info FI WHERE FI.REG_DATE <= MFI.REG_DATE ORDER BY FI.REG_DATE DESC LIMIT 26)) HIGH_26
        , (SELECT MIN(LOW) FROM (SELECT * FROM futures_info FI WHERE FI.REG_DATE <= MFI.REG_DATE ORDER BY FI.REG_DATE DESC LIMIT 26)) LOW_26
        , (SELECT MAX(HIGH) FROM (SELECT * FROM futures_info FI WHERE FI.REG_DATE <= MFI.REG_DATE ORDER BY FI.REG_DATE DESC LIMIT 52)) HIGH_52
        , (SELECT MIN(LOW) FROM (SELECT * FROM futures_info FI WHERE FI.REG_DATE <= MFI.REG_DATE ORDER BY FI.REG_DATE DESC LIMIT 52)) LOW_52
        , row_number() OVER (PARTITION BY future_cd ORDER BY future_cd, reg_date DESC) RN
FROM futures_info MFI
WHERE future_cd = 'MNQ=F'
--AND reg_date >= strftime('%Y-%m-%d','now');
AND reg_date >= date('now', '-4 month')

일목균형표를 구하기 위하여 필요한 데이터 추출

9, 26, 52 기간동안의 고가, 저가를 구해준다.

함수가 존재한다면 아래의 쿼리를 함수로 대체 한다.

, (SELECT MAX(HIGH) FROM (SELECT * FROM futures_info FI WHERE FI.REG_DATE <= MFI.REG_DATE ORDER BY FI.REG_DATE DESC LIMIT 9)) HIGH_9

해당 조회 일자의 데이터 보다 작은 날짜 (FI.REG_DATE <= MFI.REG_DATE) 해당일만큼( LIMIT 9) 데이터를 가져와 MAX 값을 구한다.

**** 실행 화면 ****

SELECT reg_date
        , future_cd
        , OPEN
        , High 
        , Low 
        , CLOSE
        , Adj_Close 
        , Volume
        , RN
        , (HIGH_9 + LOW_9) / 2 conversion_line
        , (HIGH_26 + LOW_26) / 2 base_line
        , ((HIGH_9 + LOW_9) / 2 + (HIGH_26 + LOW_26) / 2) / 2 leading_span_1
        , (high_52 + low_52) / 2 leading_span_2
        , lead(((HIGH_9 + LOW_9) / 2 + (HIGH_26 + LOW_26) / 2) / 2 , 26) OVER (PARTITION BY future_cd ORDER BY future_cd, reg_date DESC ) LEADING_1
        , lead((high_52 + low_52) / 2 , 26) OVER (PARTITION BY future_cd ORDER BY future_cd, reg_date DESC ) LEADING_2
FROM (
        SELECT reg_date
                , future_cd
                , OPEN
                , High 
                , Low 
                , CLOSE
                , Adj_Close 
                , Volume
                , (SELECT MAX(HIGH) FROM (SELECT * FROM futures_info FI WHERE FI.REG_DATE <= MFI.REG_DATE ORDER BY FI.REG_DATE DESC LIMIT 9)) HIGH_9
                , (SELECT MIN(LOW) FROM (SELECT * FROM futures_info FI WHERE FI.REG_DATE <= MFI.REG_DATE ORDER BY FI.REG_DATE DESC LIMIT 9)) LOW_9
                , (SELECT MAX(HIGH) FROM (SELECT * FROM futures_info FI WHERE FI.REG_DATE <= MFI.REG_DATE ORDER BY FI.REG_DATE DESC LIMIT 26)) HIGH_26
                , (SELECT MIN(LOW) FROM (SELECT * FROM futures_info FI WHERE FI.REG_DATE <= MFI.REG_DATE ORDER BY FI.REG_DATE DESC LIMIT 26)) LOW_26
                , (SELECT MAX(HIGH) FROM (SELECT * FROM futures_info FI WHERE FI.REG_DATE <= MFI.REG_DATE ORDER BY FI.REG_DATE DESC LIMIT 52)) HIGH_52
                , (SELECT MIN(LOW) FROM (SELECT * FROM futures_info FI WHERE FI.REG_DATE <= MFI.REG_DATE ORDER BY FI.REG_DATE DESC LIMIT 52)) LOW_52
                , row_number() OVER (PARTITION BY future_cd ORDER BY future_cd, reg_date DESC) RN
        FROM futures_info MFI
        WHERE future_cd = 'MNQ=F'
        --AND reg_date >= strftime('%Y-%m-%d','now');
        AND reg_date >= date('now', '-4 month')
    ) MFI

설명 :

- 기준이 되는 데이터를 사용하여 다음과 같은 항목을 구해온다.

CONVERSION LINE -> 전환선 ((HIGH_9 + LOW_9) /2)

BASE_LINE -> 기준선 ((HIGH_26 + LOW_26) /2)

LEADING_SPAN_1 -> ((((HIGH_9 + LOW_9) /2) + ((HIGH_26 + LOW_26) /2)) /2)
LEADING_SPAN_2 -> ((HIGH_25 + LOW_25) / 2)

이와 같이 실행시 현재의 데이터는 26일 이후에 화면에 그려지게 되므로 26일 전의 데이터가 오늘의 데이터가 됨

LEAD 함수로 26일 전의 데이터를 현재로 옮겨오면 현재의 데이터가 맞게 됩니다.

LEAD(((HIGH_9 + LOW_9) / 2 + (HIGH_26 + LOW_26) / 2) / 2 , 26)
LEAD((high_52 + low_52) / 2 , 26)

**** 실행화면 ****