LEAD와 LAG - 삼성전자 등락률 구하기

지난글에서는 삼성전자 주가를 가져와서 DB에 담아봤습니다.(이후에는 다른 종목의 주가 정보도 모두 가져올 계획입니다.)

  - https://sweetquant.tistory.com/298

파이썬 - 주가 이력 데이터 받아오기

오늘은 삼성전자 주가를 이용해 분석함수 중에 하나인 LEAD와 LAG를 간단히 살펴보겠습니다. 

'평생 필요한 데이터 분석'에서는 분석함수를 매우 길게 설명하지만 LEAD와 LAG에 대한 설명은 없습니다. 책에서는 RANK와 같은 순위 분석함수를 통해 분석대상에 대해 집중 설명합니다. 분석대상을 잘 이해한다면 다른 분석함수를 응용하는 것은 어렵지 않기 때문입니다.

LEAD와 LAG는 조회 결과 중에 몇 건 이전의 값 또는 몇 건 이후의 값을 가져오는 역할을 합니다. 이전 또는 이후의 값을 가져오므로 이전과 이후를 판단할 수 있는 ORDER BY를 OVER절에 적어주어야 합니다.

그런데.!!! 그런데!!! MySQL의 경우는 OVER절에 ORDER BY를 생략해도 LEAD와 LAG가 정상 동작합니다. 오라클에서는 반드시 OVER절에 ORDER BY를 적어주어야 합니다. 가능하면 OVER절의 ORDER BY를 적어주는 것이 좋습니다. LEAD와 LAG를 통해 가져오는 값을 명확히 알 수 있기 때문입니다.

LEAD와 LAG를 익히기 위해서는 아래 내용을 먼저 외웁니다.
 - LEAD: 다음값
 - LAG: 이전값

다시 한번, LEAD는 다음값, LAG는 이전값입니다. 그런데 다음이나 이전이라는 것은 어떤 기준으로 정렬된 데이터에서만 판단 가능합니다. 정렬된 데이터에서 다음값은 다음(아래)에 출력된 값이고, 이전값은 이전(위쪽)에 출력된 값을 뜻합니다. 저는 보통 'LEAD = 다음값'만 외워서 사용합니다. LEAD와 ORDER BY만 적절히 조합하면 LAG와 같은 결과를 만들수 있기 때문입니다.

먼저, LEAD의 개념을 잡기 위해 아래 SQL을 실행해봅니다.

[SQL-1]
SELECT  T1.STK_CD ,T1.STK_NM ,T2.DT ,T2.CLSE_PRC
        ,LEAD(T2.DT,1) OVER(ORDER BY T2.DT ASC) NEXT_DT
FROM    DB_DTECH.STOCK_KRX T1
        INNER JOIN DB_DTECH.PRICE_DT T2
           ON (T2.STK_CD = T1.STK_CD)
WHERE   T1.STK_NM = '삼성전자'
AND     T2.DT <= STR_TO_DATE('20210813','%Y%m%d')
AND     T2.DT >= STR_TO_DATE('20210807','%Y%m%d')
ORDER BY T2.DT ASC;
[결과]
STK_CD   STK_NM         DT           CLSE_PRC    NEXT_DT      
======== ============== ============ =========== ============ 
005930   삼성전자       2021-08-09   81500.000   2021-08-10   
005930   삼성전자       2021-08-10   80200.000   2021-08-11   
005930   삼성전자       2021-08-11   78500.000   2021-08-12   
005930   삼성전자       2021-08-12   77000.000   2021-08-13   
005930   삼성전자       2021-08-13   74400.000   NULL

위 SQL의 이해를 돕기 위해 결과를 그려보면 아래 [그림-1]과 같습니다. 

[그림-1]

[그림-1]에서 1번 부분에 해당하는 좌측의 결과는 분석함수인 LEAD를 제외한 SQL의 결과입니다. 이처럼 분석함수가 사용될때는, 분석함수를 제회한 결과가 먼저 만들어지고, 이에 대해 분석함수가 처리된다고 이해하는 것이 중요합니다.

2번 부분은 분석함수가 처리된 컬럼입니다. LEAD를 사용했으므로 다음값을 가져옵니다. '다음값'이라는 것은 LEAD의 OVER절 안의 ORDER BY에 따라 결정됩니다. 다시 한번 !! "LEAD의 다음값은 OVER절 안의 ORDER BY에 따라 결정된다." 외워놓으시면 됩니다. LEAD(T2.DT,1)을 사용했으므로 실제 가져와서 보여주는 값는 다음 데이터의 DT값이 됩니다. 그러므로 다음 데이터의 DT 값이 NEXT_DT에 표시가 되는 것입니다.
이때, LEAD(T2.DT,2)를 사용했다면 다음, 다음 값이 출력이 되었을 것입니다. LEAD(T2.DT, 3)이라면 아시겠죠? 세 건 다음 값인, 다음, 다음, 다음 값이 출력이 됩니다.
[그림-1]에서 마지막 로우의 DT는 '2021-08-13'입니다. 조회된 데이터 중에 '2021-08-13'보다 큰 데이터가 없으므로 LEAD의 결과는 NULL이 됩니다.

[SQL-1]에서는 LEAD를 사용해 다음 일자(DT) 값을 가져왔습니다. 이번에는 LEAD를 사용해 이전 일자 값도 가져와 보도록 하겠습니다. 아래 [SQL-2]의 BFOR_DT에 해당하는 부분입니다.

[SQL-2]
SELECT  T1.STK_CD ,T1.STK_NM ,T2.DT ,T2.CLSE_PRC
        ,LEAD(T2.DT,1) OVER(ORDER BY T2.DT ASC) NEXT_DT
        ,LEAD(T2.DT,1) OVER(ORDER BY T2.DT DESC) BFOR_DT
FROM    DB_DTECH.STOCK_KRX T1
        INNER JOIN DB_DTECH.PRICE_DT T2
           ON (T2.STK_CD = T1.STK_CD)
WHERE   T1.STK_NM = '삼성전자'
AND     T2.DT <= STR_TO_DATE('20210813','%Y%m%d')
AND     T2.DT >= STR_TO_DATE('20210807','%Y%m%d')
ORDER BY T2.DT ASC;
[결과]
STK_CD   STK_NM         DT           CLSE_PRC    NEXT_DT      BFOR_DT       
======== ============== ============ =========== ============ ============ 
005930   삼성전자        2021-08-09   81500.000   2021-08-10   NULL         
005930   삼성전자        2021-08-10   80200.000   2021-08-11   2021-08-09   
005930   삼성전자        2021-08-11   78500.000   2021-08-12   2021-08-10   
005930   삼성전자        2021-08-12   77000.000   2021-08-13   2021-08-11   
005930   삼성전자        2021-08-13   74400.000   NULL         2021-08-12

위 SQL에서, NEXT_DT와 BFOR_DT 모두 LEAD를 사용하고 있지만, OVER절의 ORDER BY를 다르게 사용하므로 나오는 결괏값이 다른 것을 알 수 있습니다.
NEXT_DT는 DT를 ASC(오름차순)했으므로 현재 로우보다 DT값이 큰 값이 다음값이 됩니다. 반면에 BFOR_DT는 DT를 DESC(내림차순) 했으므로 현재 로우보다 DT값이 작은 값이 다음값이 됩니다.
이처럼 LEAD는 조회 결과의 정렬 순서에 따라 다음값이 판단되는 것이 아니라, OVER절의 ORDER BY에 따라 판단된다는 점을 잘 기억하시기 바랍니다.

이번에는 LAG를 사용해 [SQL-2]의 LEAD와 완전히 같은 결과를 만들어보겠습니다. 아래 [SQL-3]과 같이 OVER절의 ORDER BY만 역으로 사용하면 됩니다.

[SQL-3]
SELECT  T1.STK_CD ,T1.STK_NM ,T2.DT ,T2.CLSE_PRC
        ,LEAD(T2.DT,1) OVER(ORDER BY T2.DT ASC) NEXT_DT
        ,LEAD(T2.DT,1) OVER(ORDER BY T2.DT DESC) BFOR_DT
        ,LAG(T2.DT,1) OVER(ORDER BY T2.DT DESC) NEXT_DT_2
        ,LAG(T2.DT,1) OVER(ORDER BY T2.DT ASC) BFOR_DT_2
FROM    DB_DTECH.STOCK_KRX T1
        INNER JOIN DB_DTECH.PRICE_DT T2
           ON (T2.STK_CD = T1.STK_CD)
WHERE   T1.STK_NM = '삼성전자'
AND     T2.DT <= STR_TO_DATE('20210813','%Y%m%d')
AND     T2.DT >= STR_TO_DATE('20210807','%Y%m%d')
ORDER BY T2.DT ASC;
[결과]
STK_CD   STK_NM         DT           CLSE_PRC    NEXT_DT      BFOR_DT       NEXT_DT_2    BFOR_DT_2     
======== ============== ============ =========== ============ ============ ============ ============ 
005930   삼성전자        2021-08-09   81500.000   2021-08-10   NULL         2021-08-10   NULL         
005930   삼성전자        2021-08-10   80200.000   2021-08-11   2021-08-09   2021-08-11   2021-08-09   
005930   삼성전자        2021-08-11   78500.000   2021-08-12   2021-08-10   2021-08-12   2021-08-10   
005930   삼성전자        2021-08-12   77000.000   2021-08-13   2021-08-11   2021-08-13   2021-08-11   
005930   삼성전자        2021-08-13   74400.000   NULL         2021-08-12   NULL         2021-08-12

LEAD와 LAG에 대해서는 이해했을 것이라 생각합니다. 이제 LEAD를 이용해 삼성전자의 일별 등락률을 구해보도록 하겠습니다. 일별 등락률의 계산 공식은 아래와 같습니다.
 - 일별 등락률 = (오늘 종가 - 어제 종가) / 어제 종가 * 100
​
일별 등락률을 구하기 위해서는 조회된 일별 주가에 어제 종가도 같이 추가를 해야 합니다. 아래와 같이 LEAD를 사용하면 간단히 처리할 수 있겠죠.

[SQL-4]
SELECT  T1.STK_CD ,T1.STK_NM ,T2.DT ,T2.CLSE_PRC
        ,LEAD(T2.CLSE_PRC,1) OVER(ORDER BY T2.DT DESC) BFOR_CLSE_PRC
FROM    DB_DTECH.STOCK_KRX T1
        INNER JOIN DB_DTECH.PRICE_DT T2
           ON (T2.STK_CD = T1.STK_CD)
WHERE   T1.STK_NM = '삼성전자'
AND     T2.DT <= STR_TO_DATE('20210813','%Y%m%d')
AND     T2.DT >= STR_TO_DATE('20210807','%Y%m%d')
ORDER BY T2.DT ASC;
[결과]
STK_CD   STK_NM         DT           CLSE_PRC    BFOR_CLSE_PRC   
======== ============== ============ =========== =============== 
005930   삼성전자        2021-08-09   81500.000   NULL            
005930   삼성전자        2021-08-10   80200.000   81500.000       
005930   삼성전자        2021-08-11   78500.000   80200.000       
005930   삼성전자        2021-08-12   77000.000   78500.000       
005930   삼성전자        2021-08-13   74400.000   77000.000

 

 

LEAD를 사용해 조회된 데이터 로우별로 어제 종가(BFOR_CLSE_PRC)를 가져온 것을 알 수 있습니다.
이제 이 값을 사용해 등락률을 계산하면 됩니다. 바로 계산하면 SQL이 좀 지저분해지므로, [SQL-4]의 결과를 인라인 뷰로 처리합니다.

[SQL-5]
SELECT  T3.*
        ,ROUND((T3.CLSE_PRC - T3.BFOR_CLSE_PRC) / T3.BFOR_CLSE_PRC * 100,2) CHG_RT # 등락률
FROM    (
SELECT  T1.STK_CD ,T1.STK_NM ,T2.DT ,T2.CLSE_PRC
        ,LEAD(T2.CLSE_PRC,1) OVER(ORDER BY T2.DT DESC) BFOR_CLSE_PRC
FROM    DB_DTECH.STOCK_KRX T1
        INNER JOIN DB_DTECH.PRICE_DT T2
           ON (T2.STK_CD = T1.STK_CD)
WHERE   T1.STK_NM = '삼성전자'
AND     T2.DT <= STR_TO_DATE('20210813','%Y%m%d')
AND     T2.DT >= STR_TO_DATE('20210807','%Y%m%d')
ORDER BY T2.DT ASC
) T3;
[결과]
STK_CD   STK_NM         DT           CLSE_PRC    BFOR_CLSE_PRC   CHG_RT   
======== ============== ============ =========== =============== ======== 
005930   삼성전자        2021-08-09   81500.000   NULL            NULL     
005930   삼성전자        2021-08-10   80200.000   81500.000       -1.60    
005930   삼성전자        2021-08-11   78500.000   80200.000       -2.12    
005930   삼성전자        2021-08-12   77000.000   78500.000       -1.91    
005930   삼성전자        2021-08-13   74400.000   77000.000       -3.38

 

오늘은 여기까지입니다. 생각한 내용은 훨씬 더 있는데... 내용이 너무 길어져서 여기까지 정리하고 다음에 이어서 쓰도록 하겠습니다. 감사합니다.