Chat-GPT4 기능을 활용해 Python으로 MySQL 스키마 비교 프로그램을 만들어봤습니다. (30분 안걸렸네요.) 저는, GPT-4를 사용하면서 아이언맨이 자비스와 대화하면서 뭔가를 만들어 가는 것이 불가능한 일이 아니구나라는 생각이 들었습니다. 정말... 놀라운 생산성이네요.. 제가 직접 코딩한게 하나도 없네요. 어떻게 만들것인지를 대화를 통해 정의해주고, 제가 아는 정보를 약간 알려주고, 만들어준 코드를 카피해서 실행만 했네요.
GPT-4를 잘 다루려면 자신이 프로그램 언어를 잘 이해하고 있어야 합니다. 또한, 자신이 해당 분야에 대해 잘 알고 있어야 GPT를 효율적으로 사용할 수 있습니다. 개인적인 생각은 GPT-4가 나왔으니, 프로그래밍을 할 필요가 없는게 아니라, 프로그래밍을 더욱 잘해야 할거 같습니다. GPT-4 역시 에러가 나는 코드를 만들어내므로 이를 확인하고 대처할 수 있어야 합니다. 어쨋든 이정도 생산성이라면, 간단한 코드 대부분은 GPT-4가 대체해줄 가능성이 크기 때문에, 우리는 그보다 좋은 실력과 창의력을 가지고 있을 필요가 있습니다.
실제 삼성전자의 주가를 이용하여 TR(True Range)를 구해보면 아래 엑셀표와 같습니다. 삼성전자의 2023년 8월 10일 TR은 1,100원입니다. 그리고 2023년 8월 10일 ATR(20)은 TR의 최근 20일 평균이므로 1,475원입니다.
ATR(20) 값인 1475원을 사용하여 손절선을 설정합니다. 터틀 트레이딩에서는 ATR(20)의 2배를 사용한다고 합니다. 손절금액을 더 크게 설정하려면 배수를 높이면 됩니다. ATR(20)을 이용해 아래와 같이 손절률을 계산할 수 있습니다.(2배 사용)
손절률 = (현재가 - (현재가 - (ATR20 * 2))) / 현재가 * 100
삼성전자의 2023년 8월 10일 종가(현재가)가 68,000이므로 위 공식에 대입해 보면 아래와 같이 손절률이 나옵니다.
(68000 - (68000 - (1475*2)))/68000 * 100 = 4.33%
다음 단계가 매우 중요합니다. 손절률인 4.33%를 사용하여 매수할 수량(금액)을 결정해야 합니다. 먼저 감내 가능한 손실 금액을 결정해야 합니다. 즉, 얼마까지 손실이 허용되는지, 얼마까지 손실이 발생하면 반드시 손절할 것인지를 결정해야 합니다. 감내 가능한 손절 금액은 개인마다 다릅니다. 예를 들어, 한 번의 거래에서 최대 50,000원까지 손실을 감수할 수 있다면, 아래 공식을 사용해 매수 가능한 수량을 계산할 수 있습니다.
매수수량 = 손실가능금액 / (ATR20 * 2)
위 공식을 사용해, 삼성전자의 23년 8월 10일 기준으로 손실이 50,000원까지 나도 된다고 가정하면, 아래와 같이 매수 수량을 구할 수 있습니다.
50000 / (1475 * 2) = 17
삼성전자의 2023년 8월 10일 종가가 68,000원입니다. 68,000원으로 17주를 사면 총 1,156,000 원이 필요합니다.
삼성전자 17주를 산 후에 -4.33%로 Stop-Loss를 설정합니다. 그러면, 약 50,000원 정도 손실이 되면 자동 손절이 이루어지게 됩니다.
이번에는 주식을 어떤 요일에 사는게 좋은지, 과거 데이터를 간단히 집게해보도록 하겠습니다.
설명에 앞서 다음과 같은 사항을 기억해 주세요.
- 본 글은 주식 투자를 권장하거나, 어떤 종목을 추천하기 위한 글이 아닙니다.
- 본 글을 참고해 발생한 손실에 대해서는 누구도 책임지지 않습니다.
- 데이터의 정확성은 검토했지만, 다양한 이유로 일부 데이터가 부정확할수도 있으니 양해바랍니다.
년도별로 요일별 평균 수익률을 차트로 그려보면 다음과 같습니다.
각 요일에 종가(Close-Price)에 사서 다음날 시가(Open-Price)에 매도했다고 가정합니다.
월요일이 높은 년도도 있지만, 2011년에는 화요일이 월등합니다. 월요일은 마이너스이고요.
그리고 2015년에는 수요일이 높은 편으로 보입니다.
데이터를 더 갖다가 살펴볼 필요가 있어 보입니다. 예를 들어, 주식 시장의 전체 분위기가 좋으면 주초에 주로 오르고, 시장 분위기가 나쁘면, 주초가 별로인가.. 라는 추측이 듭니다. 나중에 지수 데이터를 연결해서 살펴봐야 할거 같네요.
위 차트를 년도 상관없이, 요일별로 평균을 구해보면 아래와 같습니다. 전체 평균을 봤을때는 화요일이 가장 좋네요.!
여기서는 각 요일별 종가에 매수한 후에, 다음날 시가에 바로 판 것을 가정했습니다.
이러한 집계 기준을 어떻게 정하는지에 따라 결과가 많이 달라질거 같습니다.
예를 들어, 월요일 종가가 아닌 시가에 샀다고 가정할 수도 있고, 사고 나서 일주일을 보유했다고 가정할 수도 있고요.
그리고 한국 시장이 아니라, 미국이나 중국이라면? 또 다른 결과가 나올까요? 나중에 기회가 되면 해봐야겠습니다.
이상입니다.!
역시 SQL로 간단히 구할 수 있습니다.
- 데이터 추출 기준: 2010년부터 2023년 8월까지의 한국 주식 시장의 주가 데이터
- 데이터 추출 방법: SQL
WITH W01 AS(
SELECT T1.STK_CD ,T1.DT
,T1.C_PRC B_PRC # BUY when Close price
,DAYOFWEEK(T1.DT) WK_NO
,CASE DAYOFWEEK(T1.DT)
WHEN 2 THEN 'MON'
WHEN 3 THEN 'TUE'
WHEN 4 THEN 'WED'
WHEN 5 THEN 'THU'
WHEN 6 THEN 'FRI' END WK
,LEAD(T1.O_PRC) OVER(PARTITION BY T1.STK_CD ORDER BY T1.DT ASC) S_PRC # SELL when next open Price
FROM HIST_DT T1
WHERE T1.DT >= '20100101'
AND T1.DT <= '20141231'
)
SELECT DATE_FORMAT(T1.DT,'%Y') YY
,T1.WK
,AVG(ROUND((T1.S_PRC - T1.B_PRC) / T1.B_PRC * 100, 2)) AVG_PROF
,COUNT(*) SAMP_CNT
FROM W01 T1
GROUP BY DATE_FORMAT(T1.DT,'%Y'), T1.WK
ORDER BY DATE_FORMAT(T1.DT,'%Y'), MAX(T1.WK_NO)
When is the time of day that working people are most interested in the stock market?
I think it's probably not during the morning hours when the stock market opens.
The stock market that opens after waiting overnight! You can't resist checking it out in the morning, can you?
When I check the stocks that are going up in the morning, my heart flutters. Oh, I should buy before they leave me behind!
When I look at the stocks that fall in the morning, I have a somewhat greedy thought. Oh! It fell, it's the price I've been waiting for. I should buy it!
Whether it's because the price dropped or went up, it seems like the "I have to buy it!" circuit in my brain is highly activated by morning.Not everyone may agree, but it is true that stock trading is most active in the morning when the stock market opens.
So, is it a good choice to buy stocks at the morning price, precisely the opening price (Open-Price)?
Let's find out through simple data aggregation.
Before explaining, I want to clarify that this is not an article recommending stock investment or suggesting any specific stock.
Please remember that no one is responsible for any losses incurred in the stocks invested in as a result of this article.
We have verified the accuracy of the data. However, please understand that there may be some inaccuracies due to errors that may have occurred during the data collection process or at the time of data collection.
Let's use SQL to calculate the average profit rate for the following two trade cases:
Case 1: Buy at today's open price and sell at today's close price.
Case 2: Buy at today's close price and sell at tomorrow's open price.
Actually, we can't buy and sell stocks every day like in the example above, right? However, if we aggregate the data, we can find some useful information for our investment.
If we use the daily stock price data I have (Korea) and aggregate it by year, the following results are obtained:
Overall, it can be seen that Case2 [Buy at today's closing price and sell at tomorrow's opening price] has a much better average return rate than Case1. Buying at today's opening price and selling at today's closing price, like in Case1, results in mostly negative returns on average.
This test is completely meaningless for those who invest in value. If you approach investing from a perspective of investing in really good companies, it doesn't matter whether you buy in the morning or in the evening. However, I think this is useful information to refer to when buying stocks.
Conclusion! Buy at the closing price!
The above result can be easily implemented with the following SQL. Are you all studying SQL?
WITH W01 AS(
SELECT T1.STK_CD ,T1.DT ,T1.C_PRC ,T1.O_PRC
,LEAD(T1.O_PRC) OVER(PARTITION BY T1.STK_CD ORDER BY T1.DT ASC) AF_O
,LEAD(T1.C_PRC) OVER(PARTITION BY T1.STK_CD ORDER BY T1.DT ASC) AF_C
FROM HIST_DT T1
WHERE T1.DT >= '20210101'
AND T1.DT <= '20211231'
ORDER BY T1.DT DESC
)
,W02 AS(
SELECT T1.*
,ROUND((T1.C_PRC - T1.O_PRC) / T1.O_PRC * 100,1) `OPEN_CLOSE`
,ROUND((T1.AF_O - T1.C_PRC) / T1.C_PRC * 100,1) `CLOSE_OPEN`
FROM W01 T1
)
SELECT DATE_FORMAT(T1.DT,'%Y')
,AVG(`OPEN_CLOSE`) `CASE1_OPEN_CLOSE`
,AVG(`CLOSE_OPEN`) `CASE2_CLOSE_OPEN`
,COUNT(*) CNT
FROM W02 T1
GROUP BY DATE_FORMAT(T1.DT,'%Y');
실제, 위의 예처럼 매일매일 사고 팔수는 없겠죠? 그래도 데이터를 집계해보면, 우리가 투자에 참고할만한 정보가 나온답니다.
제가 보유한 일별 주가 데이터(한국)를 사용해 년별로 집계해보면 아래와 같은 결과가 나옵니다.
전체적으로 Case2[오늘 종가(Close Price) Buy - 내일 시가(Open Price) Sell]가 Case1보다 평균 수익률이 훨씬 좋은 것을 알 수 있습니다.Case1과 같이 오늘시가에 사서 오늘종가에 파는 경우는 평균적으로 마이너스 수익률인 경우가 대부분입니다.
가치투자를 하시는 분들께는 이런 테스트는 완전히 무의미합니다. 정말 좋은 기업에 투자 관점을 가지고 접근하시는 거라면 아침에 사든, 저녁에 사든 중요하지 않으니까요. 하지만, 자신이 주식을 살때 한 번쯤은 참고할 만한 정보라고 생각합니다.
결론! 종가에 사자!
위 결과는 아래의 SQL로 간단하게 구현할 수 있답니다. 다들, SQL 공부 하고 계시죠?
WITH W01 AS(
SELECT T1.STK_CD ,T1.DT ,T1.C_PRC ,T1.O_PRC
,LEAD(T1.O_PRC) OVER(PARTITION BY T1.STK_CD ORDER BY T1.DT ASC) AF_O
,LEAD(T1.C_PRC) OVER(PARTITION BY T1.STK_CD ORDER BY T1.DT ASC) AF_C
FROM HIST_DT T1
WHERE T1.DT >= '20210101'
AND T1.DT <= '20211231'
ORDER BY T1.DT DESC
)
,W02 AS(
SELECT T1.*
,ROUND((T1.C_PRC - T1.O_PRC) / T1.O_PRC * 100,1) `오늘시가_오늘종가`
,ROUND((T1.AF_O - T1.C_PRC) / T1.C_PRC * 100,1) `오늘종가_내일시가`
FROM W01 T1
)
SELECT DATE_FORMAT(T1.DT,'%Y')
,AVG(`오늘시가_오늘종가`) `CASE1_오늘시가_오늘종가`
,AVG(`오늘종가_내일시가`) `CASE2_오늘종가_내일시가`
,COUNT(*) CNT
FROM W02 T1
GROUP BY DATE_FORMAT(T1.DT,'%Y');
An index is a data object (structure) that collects some data to quickly find data within a table. Indexes are essential for improving SQL performance. To maximize SQL performance, you need the optimal index.
To create the optimal index, the following skills are required:
Ability to understand the physical structure of the index
Ability to break down and understand complex SQL
Ability to predict how the created index will be used
Understanding of the internal processing methods (NESTED LOOPS, MERGE, HASH) of JOINs
Experience in creating and applying indexes in real-world situations, in addition to the above skills.
To fully understand indexes, it is recommended to search for more materials along with books related to DB performance.
The reason for studying indexes is not to create them, but to create SQL that uses indexes efficiently. Creating indexes is the next step.
Profiling shows performance-related information about the executed SQL. When profiling is enabled through SET PROFILING and SQL is executed, profiling information is collected. The collected profile information can be viewed through the show profile command.
Please refer to the following commands for profiling:
Turn on profiling: SET PROFILING = 1;
Turn off profiling: SET PROFILING = 0;
Profile retrieval:
Detailed profile of the last executed SQL: SHOW PROFILE;
Execution time of profiled SQL: SHOW PROFILES;
Detailed profile of a specific SQL: SHOW PROFILE FOR QUERY [Profile ID]
The profile ID of a SQL executed through SHOW PROFILES can be obtained.
Detailed profile retrieval by specific element:
show profile cpu for query [Profile ID];
show profile block IO for query [Profile ID];
show profile memory for query [Profile ID];
show profile source for query [Profile ID];
The process of setting up profiling, running SQL, and checking the profiling information collected for the SQL is as follows.
SET PROFILING = 1; # Turn on profile
# SQL execute
SELECT COUNT(*)
FROM MYTUNDB.T_ORD_BIG T1
WHERE T1.ORD_YMD LIKE '201701%';
SHOW PROFILE;
# profile result
# starting 0.000077
# ...
# statistics 0.008959
# preparing 0.000035
# executing 0.205336
# ...
# cleaning up 0.000031
SET PROFILING = 0; # Turn off profile
The Need for Continuous SQL Performance Improvement
Improving SQL performance yields significant benefits even in the short term. However, to maintain stable system performance, it is necessary to conduct long-term SQL performance improvement work.
In the figure below, it can be seen that the performance of the malicious SQL was dramatically improved by tuning it in the early stages of the project. However, as the system continues to add or change features, SQL is modified, leading to performance degradation again. Therefore, continuous SQL performance improvement is necessary.
현재(2023년 1월) 기준으로 위의 레퍼런스를 살펴보면, 해시 조인과 관련된 힌트로, HASH_JOIN과 NO_HASH_JOIN이 있는 것을 알 수 있습니다. 하지만, Description을 보면 해당 힌트는 MySQL 8.0.18 에서만 사용할 수 있는 힌트입니다.
저는 현재 8.0.22 를 사용하고 있습니다. 이처럼 8.0.18 이후 버젼에서는 해시 조인을 컨트를하려면 BNL이나 NO_BNL이란 힌트를 사용해야 합니다.
위 레퍼런스에는, BNL 힌트의 설명으로 아래와 같이 정리되어 있습니다.
- Prior to MySQL 8.0.20: affects Block Nested-Loop join processing;
- MySQL 8.0.18 and later: also affects hash join optimization;
- MySQL 8.0.20 and later: affects hash join optimization only
BNL은 원래 Block Nested-Loop 를 뜻합니다. NL(Nested-Loops) 조인을 처리할 때, 조인 버퍼를 사용해 NL 조인 성능을 향상시키는 기능입니다. MySQL 8 이전 버젼은 해시 조인을 지원하지 않았기 때문에, BNL 기능으로 해시 조인을 어느 정도 대체했던거 같습니다.
어찌되었든, 8.0.2 부터는 BNL 힌트가 해시 조인 최적화에 영향을 준다니 혼란스럽습니다. 아마도 8.0.2부터는 본연의 BNL(Block Nested-Loops) 기능 자체는 없어진거 같습니다. 그냥 BNL 힌트를 제거하고 HASH_JOIN 힌트를 살려줬으면 혼란이 없지 않았을까 생각이 듭니다만, 제가 모르는 다양한 이유가 있겠죠.
그러면, BNL 힌트를 사용해 해시 조인을 한번 테스트해볼까 합니다. 생각보다? 아니, 생각만큼 힌트가 잘 안먹습니다.
먼저, 저희가 테스트할 테이블인 M_CUS와 T_ORD_BIG에는 어떤 인덱스가 있는지 먼저 살펴봅니다.
인덱스는 조인의 내부적인 처리 방법(NL, HASH, Sort Merge)에 영향을 끼치는 중요 요소입니다.
MySQL에서 인덱스는 'show index from 테이블명' 명령어를 통해 쉽게 확인할 수 있습니다.
위와 같이 인덱스가 구성된 상태에서 아래와 같이 SQL을 실행해 봅니다. 실행 계획을 보면, M_CUS와 T_ORD_BIG을 NL 방식으로 조인하고 있습니다. 조인 조건으로 사용된 CUS_ID 컬럼의 인덱스는 M_CUS에만 있으므로, M_CUS를 후행 집합으로 NL 처리하고 있습니다.
EXPLAIN ANALYZE
SELECT T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID
-> Table scan on <temporary> (actual time=0.003..0.015 rows=72 loops=1)
-> Aggregate using temporary table (actual time=187.347..187.376 rows=72 loops=1)
-> Nested loop inner join (cost=31569.86 rows=39462) (actual time=1.043..126.440 rows=19800 loops=1)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462) (actual time=0.973..91.609 rows=19800 loops=1)
-> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID) (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=19800)
이제, NL이 아닌, 해시 조인으로 위 SQL이 처리되도록 하기 위해 아래와 같이 BNL 힌트를 적용합니다.
실행계획을 확인해보면, 힌트가 무시되고 NL 조인이 여전히 사용중인 것을 알 수 있습니다.
힌트를 잘 못 준건 아닐까란 생각에, /*+ BNL(T1) */ 이나, /*+ BNL(T1, T2) */, /*+ BNL(T2, T1) */ 등 다양하게 시도해봤으나 해시 조인이 발생하지는 않습니다.
아마도 MySQL의 옵티마이져가 생각했을때, 현재 SQL과 테이블, 인덱스 구성에서는 해시 조인이 비효율적이라고 판단한 것이겠죠.
EXPLAIN ANALYZE
SELECT /*+ BNL(T2) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary> (actual time=0.002..0.014 rows=72 loops=1)
-> Aggregate using temporary table (actual time=120.311..120.340 rows=72 loops=1)
-> Nested loop inner join (cost=31569.86 rows=39462) (actual time=0.240..80.108 rows=19800 loops=1)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462) (actual time=0.223..58.354 rows=19800 loops=1)
-> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID) (cost=0.25 rows=1) (actual time=0.000..0.001 rows=1 loops=19800)
이 경우, T2(T_ORD_BIG)쪽에는 CUS_ID에 대한 인덱스가 없으므로 해시 조인이 발생할 가능성이 높습니다. (FULL SCAN 방식으로 NL을 처리할 수는 없으니까요.)
BNL 힌트 없이, JOIN_ORDER 만으로 조인 순서를 변경하자 해시 조인이 사용된 것을 알 수 있습니다.
EXPLAIN ANALYZE
SELECT /*+ JOIN_ORDER(T1, T2) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary> (actual time=0.002..0.014 rows=72 loops=1)
-> Aggregate using temporary table (actual time=106.843..106.871 rows=72 loops=1)
-> Inner hash join (t2.CUS_ID = t1.CUS_ID) (cost=1598243.65 rows=355158) (actual time=0.386..67.943 rows=19800 loops=1)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=13816.34 rows=39462) (actual time=0.250..55.218 rows=19800 loops=1)
-> Hash
-> Table scan on T1 (cost=9.25 rows=90) (actual time=0.058..0.081 rows=90 loops=1)
이번에는 조인 순서를 T2->T1으로 변경하고 BNL 힌트까지 추가해봅니다. 혹시나 JOIN_ORDER와 BNL을 같이 써야만 해시 조인이 사용되는지 의심되서 입니다.
하지만, 다시 NL 조인이 사용되는 것을 볼 수 있습니다. 아무래도 M_CUS의 PRIMARY 인덱스 사용의 우선 순위가 높은거 아닌가 싶습니다.
EXPLAIN ANALYZE
SELECT /*+ JOIN_ORDER(T2, T1) BNL(T2, T1) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary> (actual time=0.002..0.011 rows=72 loops=1)
-> Aggregate using temporary table (actual time=93.163..93.184 rows=72 loops=1)
-> Nested loop inner join (cost=31569.86 rows=39462) (actual time=0.236..62.395 rows=19800 loops=1)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462) (actual time=0.219..44.639 rows=19800 loops=1)
-> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID) (cost=0.25 rows=1) (actual time=0.000..0.000 rows=1 loops=19800)
끝으로, 실제 성능과 상관없이, 죽었다 깨어나도 T2->T1 순서로 조인 순서를 유지하면서, 해시 조인을 만들어 보고 싶다면, 조인 조건의 강제 변화를 고려할 수 있습니다.
아래와 같이 T1.CUS_ID를 TRIM 처리해서 인덱스를 사용못하도록 합니다. 그리고 JOIN_ORDER 힌트만 사용해서 T2 먼저 접근하도록 합니다. BNL 힌트를 주지 않았지만, 해시 조인이 발생한 것을 알 수 있습니다.
(아래와 같이 조인 조건절을 일부러 변형해 해시 조인을 유도하는 것은 절대 좋은 방법이 아니죠.)
EXPLAIN ANALYZE
SELECT /*+ JOIN_ORDER(T2, T1) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (TRIM(T1.CUS_ID) = T2.CUS_ID) -- > T1.CUS_ID를 일부러 TRIM 처리
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary> (actual time=0.002..0.024 rows=72 loops=1)
-> Aggregate using temporary table (actual time=280.777..280.815 rows=72 loops=1)
-> Inner hash join (trim(t1.CUS_ID) = t2.CUS_ID) (cost=372923.03 rows=3551580) (actual time=141.649..239.391 rows=19800 loops=1)
-> Table scan on T1 (cost=0.00 rows=90) (actual time=0.078..0.119 rows=90 loops=1)
-> Hash
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462) (actual time=0.243..48.248 rows=19800 loops=1)
지금까지 살펴본 바로는, 해시 조인을 사용하는 힌트인 BNL이 그렇게 잘 작동하는거 같지는 않습니다. 조인을 처리할 수 있는 막강한 PRIMARY 인덱스가 있다면, 아마도 해시 조인보다 NL 조인을 선호하는 것은 아닌가 생각이 듭니다.
BNL 힌트는 잘 작동하지 않지만, NO_BNL 힌트는 또 잘 먹는거 같드라고요.
아래와 같이 해시 조인이 자동으로 작동했던, JOIN_ORDER(T1, T2)를 적용한 후에 NO_BNL까지 힌트를 걸어주면 NL 방식으로 조인이 처리되어 매우 느린 것을 알 수 있습니다.
EXPLAIN ANALYZE
SELECT /*+ JOIN_ORDER(T1, T2) NO_BNL(T2) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Group aggregate: max(t1.CUS_NM), sum(t2.ORD_AMT), count(0) (actual time=187.159..6557.740 rows=72 loops=1)
-> Nested loop inner join (cost=1598243.65 rows=355158) (actual time=16.554..6530.982 rows=19800 loops=1)
-> Index scan on T1 using PRIMARY (cost=9.25 rows=90) (actual time=0.060..0.328 rows=90 loops=1)
-> Filter: (t2.CUS_ID = t1.CUS_ID) (cost=13816.34 rows=3946) (actual time=24.775..72.503 rows=220 loops=90)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=13816.34 rows=39462) (actual time=0.290..61.939 rows=19800 loops=90)
본 글에서는 BNL을 이용해 해시 조인이 발생한 케이스는 나오지 않았으나, 인덱스의 구성, 쿼리 조건의 변경에 따라 BNL 힌트가 작동이 될 수도 있다고 생각합니다. 어쨋든, 8.0.2 부터는 BNL 힌트를 사용해 해시 조인을 유도할 수 있다는 점과, 힌트를 준다고 해도, 무조건 해시 조인이 발생하지 않는다는 점을 알고 있으면 될거 같습니다.
EXPLAIN
SELECT T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2 ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
결과를 보면 아래와 같습니다. T2(T_ORD_BIG)가 위에 있습니다. MySQL의 경우, 아래와 같은 결과에서는 위쪽에 있는 단계가 먼저 처리됩니다. 다시 말해 T2를 먼저 접근해 T1과 조인이 처리되고 있습니다.
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
---- ------------- ------- ------------ -------- --------------- -------------- --------- ------------------- ------- ---------- ----------------------------------------
1 SIMPLE T2 None range T_ORD_BIG_X1 T_ORD_BIG_X1 6 None 39462 100.0 Using index condition; Using temporary
1 SIMPLE T1 None eq_ref PRIMARY PRIMARY 162 mytundb.T2.CUS_ID 1 100.0 None
위 SQL의 실행계획을 EXPLAIN FORMAT = TREE를 사용해 TREE 형태로 살펴보면 아래와 같습니다. T2를 T_ORD_BIG_X1 인덱스로 접근한 후, T1을 NL 조인 방식으로 처리하고 있는 것을 알 수 있습니다.
EXPLAIN FORMAT = TREE
SELECT T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2 ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary>
-> Aggregate using temporary table
-> Nested loop inner join (cost=31569.86 rows=39462)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462)
-> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID) (cost=0.25 rows=1)
위 SQL의 조인 순서를 변경해봅니다. T1을 먼저 접근해 T2쪽으로 조인을 하도록 처리해보는 거죠.
아래와 같이 JOIN_ORDER라는 옵티마이져 힌트를 사용할 수 있습니다.
EXPLAIN
SELECT /*+ JOIN_ORDER(T1, T2) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2 ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
아래와 같이 실행계획을 보면, T1부터 접근해서 조인이 처리되는 것을 알 수 있습니다. 힌트가 작동한거죠.
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------- ---------- -------------------------------------------------------------------
1 SIMPLE T1 None ALL PRIMARY None None None 90 100.0 Using temporary
1 SIMPLE T2 None range T_ORD_BIG_X1 T_ORD_BIG_X1 6 None 39462 10.0 Using index condition; Using where; Using join buffer (hash join)
TREE 형태로 실행계획을 살펴보면 아래와 같습니다.
실행계획을 잘 살펴보면, T1을 먼저 처리하는 방식으로 조인 순서가 바뀌면서, Inner hash join이 발생한 것을 알 수 있습니다. 바뀐 조인 순서에 따라 옵티마이져가HASH 조인으로 변경을 한 것입니다.
EXPLAIN FORMAT = TREE
SELECT /*+ JOIN_ORDER(T1, T2) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2 ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary>
-> Aggregate using temporary table
-> Inner hash join (t2.CUS_ID = t1.CUS_ID) (cost=1598243.65 rows=355158)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=13816.34 rows=39462)
-> Hash
-> Table scan on T1 (cost=9.25 rows=90)
끝으로 주의할 점이 있습니다. 옵티마이져 힌트에서, JOIN_ORDER의 테이블을 정의할 때 반드시 콤마(,)로 구분을 해야 합니다. 아래와 같이 스페이스로만 두 테이블을 분리하면 힌트가 작동하지 않습니다.
EXPLAIN FORMAT = TREE
SELECT /*+ JOIN_ORDER(T1 T2) */ # 잘못된 힌트, T2과 T2를 콤마(,)로 분리해야함!
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2 ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary>
-> Aggregate using temporary table
-> Nested loop inner join (cost=31569.86 rows=39462)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462)
-> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID) (cost=0.25 rows=1)
'트레이드 전략 - 위꼬리가 긴 하루'의 마지막 글입니다. 제목에 비해 별 내용이 없는데라고 생각하실 분들도 있을듯 싶네요. 다양한 방법의 하나로 봐주시면 감사하겠습니다.
앞에서 2020년 10월 한달 데이터에 대해서 위꼬리 전략을 적용했을때, TE(Trading Edge)를 구해보니 좋지 않았습니다. 여기서는 기간 조건을 제거하고 모든 데이터에 대해 위꼬리 전략을 적용해봅니다. 간단합니다. 앞의 글의 SQL에서 일자 조건만 제거하면 됩니다. (불행히도, 현재 사용하는 데이터에는 2019년 부터 2021년까지의 데이터만 존재합니다.) 아래와 같이 SQL을 작성하고 실행해봅니다.
WITH R01 AS(
SELECT T2.STK_CD ,T2.DT BUY_DT ,T2.C_PRC BUY_PRC ,T2.H_L ,T2.UP_TAIL ,T2.H_L_CHG ,T2.UP_TAIL_RT
,T3.DT SELL_DT ,T3.C_PRC SELL_PRC
,ROUND((T3.C_PRC - T2.C_PRC) / T2.C_PRC * 100,2) PROF_RT # 3일후 매도시 수익률
FROM (
SELECT T1.STK_CD
,T1.DT
,T1.C_PRC
,T1.H_PRC - T1.L_PRC H_L # 고가-저가 길이
,T1.H_PRC - GREATEST(T1.C_PRC,T1.O_PRC) UP_TAIL # 위꼬리 길이
,ROUND((T1.H_PRC - T1.L_PRC) / T1.L_PRC * 100,2) H_L_CHG # 위꼬리 등락률
,ROUND((T1.H_PRC - GREATEST(T1.C_PRC,T1.O_PRC))
/ (T1.H_PRC - T1.L_PRC) * 100,2) UP_TAIL_RT # 위꼬리 비율
,T1.STK_DT_NO
FROM MYSTKDB.HISTORY_DT T1
WHERE 1=1
# AND T1.DT >= STR_TO_DATE('20201001','%Y%m%d') # 일자 조건 제거
# AND T1.DT < STR_TO_DATE('20201101','%Y%m%d') # 일자 조건 제거
) T2
INNER JOIN MYSTKDB.HISTORY_DT T3 # 위꼬리 발생 3일 후 주가 데이터를 조인
ON (T3.STK_CD = T2.STK_CD AND T3.STK_DT_NO = T2.STK_DT_NO + 3)
WHERE T2.H_L_CHG >= 10 # 고가-저가 등락률이 10% 이상
AND T2.UP_TAIL_RT >= 90 # 위꼬리 비율이 90% 이상
ORDER BY PROF_RT DESC
)
SELECT T2.*
,ROUND((T2.W_PROF_RT * T2.W_RT) - (ABS(T2.L_PROF_RT)* T2.L_RT),2) TE
FROM (
SELECT DATE_FORMAT(T1.BUY_DT, '%Y') YY # --> 년별
,COUNT(*) TRD_CNT
,SUM(CASE WHEN T1.PROF_RT >= 1 THEN 1 END) W_CNT
,ROUND(AVG(CASE WHEN T1.PROF_RT >= 1 THEN T1.PROF_RT END),2) W_PROF_RT
,ROUND(SUM(CASE WHEN T1.PROF_RT >= 1 THEN 1 END) / COUNT(*) * 100, 2) W_RT
,SUM(CASE WHEN T1.PROF_RT < 1 THEN 1 END) L_CNT
,ROUND(AVG(CASE WHEN T1.PROF_RT < 1 THEN T1.PROF_RT END),2) L_PROF_RT
,ROUND(SUM(CASE WHEN T1.PROF_RT < 1 THEN 1 END) / COUNT(*) * 100, 2) L_RT
FROM R01 T1
GROUP BY DATE_FORMAT(T1.BUY_DT, '%Y') # --> 년별
) T2
ORDER BY T2.YY ASC
;
YY TRD_CNT W_CNT W_PROF_RT W_RT L_CNT L_PROF_RT L_RT TE
------ --------- ------- ----------- ------- ------- ----------- ------- --------
2019 333 176 8.68 52.85 157 -3.67 47.15 285.70
2020 382 166 8.90 43.46 216 -6.75 56.54 5.15
2021 291 132 8.16 45.36 159 -5.60 54.64 64.15
결과를 보기 좋게, 년별로 집계헀습니다. 결과를 보면, 2019년에는 TE가 매우 높았으나, 2020년에는 다소 낮은 것을 알 수 있습니다. 비록 3년간의 데이터지만, 년별로 TE를 보면 마이너스가 아니기 때문에, 해당 전략은 좀 더 다듬고 연구해 볼 필요가 있지 않나 생각이 듭니다. 아래와 같은 내용을 추가로 고려해 볼 필요가 있습니다.
종목의 주가(저가주를 제외할까? 저가주만 노릴까?)
거래량(거래량이 폭발적이었는가)
추세(이평선이 상승 추세인가?)
시장의 추세(지수가 상승 중인가?)
손절과 익절
보유 기간을 늘리면? 줄이면?
위꼬리의 비율을 조절한다면?
위와 같은 내용을 추가하는 과정에서 너무 과최적화가 되지 않도록 주의가 필요합니다. 조건이 많아질수록 이도 저도 아닌 결과가 나오는 경우가 많드라고요. 적절하게 조건을 추가해서 더 좋은 TE가 만들어지도록 노력을 해보고, 가능성이 있다면, 파이썬으로 정밀하게 트레이딩을 시뮬레이션 해봐야겠죠. 그리고, TE가 높다고 해서 항상 수익이 아니죠. 마이너스. 구간이 언제나 존재합니다. 그러한 구간에 대한 대응 전략과, 생존 전략이 없다면 트레이드는 금기가 아닐까 싶습니다.
준비한 글은 여기까지입니다. 마지막으로 한 번더!^^;;; 절대 주식 투자나 종목 매매를 권장하는 글이 아닙니다. 기술적인 설명을 위한 글이며, 이 글을 참고해 발생한 투자 손실에는 그 누구도 절대 책임지지 않습니다.
이번에는 앞에서 구한 2020년 10월의 트레이드 데이터를 집계처리합니다. 데이터 집계를 통해, 승리할 확률과, 승리했을때의 수익률을 구할 수 있습니다. 반대로 패배의 확률도 알 수 있죠. 데이터 집계를 통해 TE(Trading Edge)를 구할 수 있으며, 이를 통해 과연 사용할 만한 전략인지 고민해볼 수 있습니다.
설명에 앞서.. 또 한 번 거듭 말씀드립니다. 여기서 소개하는 트레이드 전략은 매우 위험하며, 절대 주식 투자나 종목 매매를 권장하는 글이 아닙니다. 기술적인 설명을 위한 글이며, 이 글을 참고해 발생한 투자 손실에는 그 누구도 절대 책임지지 않습니다.
먼저 승리와 패배를 정의해야 합니다. 아래와 같이 정의합니다.
승리: 매도시 1% 이상 수익
패배: 매도시 1% 미만 수익 또는 손실
아래와 같이 SQL을 구현합니다. 앞에 글에서 사용한 SQL을 그대로, WITH R01 AS로 묶은 다음에, R01에 대해 집계처리하면 됩니다. 집계 처리 과정에서 CASE를 사용해 승리와 패배를 구분합니다.
WITH R01 AS(
SELECT T2.STK_CD ,T2.DT BUY_DT ,T2.C_PRC BUY_PRC ,T2.H_L ,T2.UP_TAIL ,T2.H_L_CHG ,T2.UP_TAIL_RT
,T3.DT SELL_DT ,T3.C_PRC SELL_PRC
,ROUND((T3.C_PRC - T2.C_PRC) / T2.C_PRC * 100,2) PROF_RT # 3일후 매도시 수익률
FROM (
SELECT T1.STK_CD
,T1.DT
,T1.C_PRC
,T1.H_PRC - T1.L_PRC H_L # 고가-저가 길이
,T1.H_PRC - GREATEST(T1.C_PRC,T1.O_PRC) UP_TAIL # 위꼬리 길이
,ROUND((T1.H_PRC - T1.L_PRC) / T1.L_PRC * 100,2) H_L_CHG # 위꼬리 등락률
,ROUND((T1.H_PRC - GREATEST(T1.C_PRC,T1.O_PRC))
/ (T1.H_PRC - T1.L_PRC) * 100,2) UP_TAIL_RT # 위꼬리 비율
,T1.STK_DT_NO # --> 종목별일자 순번
FROM MYSTKDB.HISTORY_DT T1
WHERE 1=1
AND T1.DT >= STR_TO_DATE('20201001','%Y%m%d')
AND T1.DT < STR_TO_DATE('20201101','%Y%m%d')
) T2
INNER JOIN MYSTKDB.HISTORY_DT T3 # 위꼬리 발생 3일 후 주가 데이터를 조인
ON (T3.STK_CD = T2.STK_CD AND T3.STK_DT_NO = T2.STK_DT_NO + 3)
WHERE T2.H_L_CHG >= 10 # 고가-저가 등락률이 10% 이상
AND T2.UP_TAIL_RT >= 90 # 위꼬리 비율이 90% 이상
ORDER BY PROF_RT DESC
)
SELECT COUNT(*) TRD_CNT
,SUM(CASE WHEN T1.PROF_RT >= 1 THEN 1 END) W_CNT
,ROUND(AVG(CASE WHEN T1.PROF_RT >= 1 THEN T1.PROF_RT END),2) W_PROF_RT
,ROUND(SUM(CASE WHEN T1.PROF_RT >= 1 THEN 1 END) / COUNT(*) * 100, 2) W_RT
,SUM(CASE WHEN T1.PROF_RT < 1 THEN 1 END) L_CNT
,ROUND(AVG(CASE WHEN T1.PROF_RT < 1 THEN T1.PROF_RT END),2) L_PROF_RT
,ROUND(SUM(CASE WHEN T1.PROF_RT < 1 THEN 1 END) / COUNT(*) * 100, 2) L_RT
FROM R01 T1
;
TRD_CNT W_CNT W_PROF_RT W_RT L_CNT L_PROF_RT L_RT
--------- ------- ----------- ------- ------- ----------- -------
28 10 7.17 35.71 18 -4.91 64.29
위 SQL을 통해 얻은 결과의 정보를 정리해보면 아래와 같습니다.
TRD_CNT: 매매 횟수
W_CNT: 승리 횟수
W_PROF_RT: 승리시 평균 수익률
W_RT: 승률
L_CNT: 패패 횟수
L_PROF_RT: 패배시 평균 수익률
L_RT: 패율
위 결과를 통해, 승리시 평균적으로 7.17%의 수익이 났으며, 승률이 35.71%인 것을 알 수 있습니다. 반대로 패배시 평균 수익은 -4.91%이며, 패배할 확률이 64.29%나 됩니다. 이 내용을 가지고 '터틀 트레이딩'에서 설명하는 TE(Trading Edge)를 구해보면 아래와 같습니다.
TE가 마이너스(-59.62)라는 것은 트레이드를 할 수록 손해볼 확률이 높다는 뜻입니다. 하지만, 저희는 단지 2020년 10월에 대해서만 TE를 산출했습니다. 그러므로 가능한 많은 데이터에 위꼬리 전략을 적용해 TE를 구해볼 필요가 있습니다. 이와 관련해서는 다음 글에서 살펴보도록 하겠습니다.
끝으로, TE까지 구하는 SQL을 만들어보면 아래와 같습니다. 기존의 집계한 데이터를 한 단계 밑으로 인라인 뷰 처리한 후에 TE 고식을 적용합니다.
WITH R01 AS(
SELECT T2.STK_CD ,T2.DT BUY_DT ,T2.C_PRC BUY_PRC ,T2.H_L ,T2.UP_TAIL ,T2.H_L_CHG ,T2.UP_TAIL_RT
,T3.DT SELL_DT ,T3.C_PRC SELL_PRC
,ROUND((T3.C_PRC - T2.C_PRC) / T2.C_PRC * 100,2) PROF_RT # 3일후 매도시 수익률
FROM (
SELECT T1.STK_CD
,T1.DT
,T1.C_PRC
,T1.H_PRC - T1.L_PRC H_L # 고가-저가 길이
,T1.H_PRC - GREATEST(T1.C_PRC,T1.O_PRC) UP_TAIL # 위꼬리 길이
,ROUND((T1.H_PRC - T1.L_PRC) / T1.L_PRC * 100,2) H_L_CHG # 위꼬리 등락률
,ROUND((T1.H_PRC - GREATEST(T1.C_PRC,T1.O_PRC))
/ (T1.H_PRC - T1.L_PRC) * 100,2) UP_TAIL_RT # 위꼬리 비율
,T1.STK_DT_NO # --> 종목별일자 순번
FROM MYSTKDB.HISTORY_DT T1
WHERE 1=1
AND T1.DT >= STR_TO_DATE('20201001','%Y%m%d')
AND T1.DT < STR_TO_DATE('20201101','%Y%m%d')
) T2
INNER JOIN MYSTKDB.HISTORY_DT T3 # 위꼬리 발생 3일 후 주가 데이터를 조인
ON (T3.STK_CD = T2.STK_CD AND T3.STK_DT_NO = T2.STK_DT_NO + 3)
WHERE T2.H_L_CHG >= 10 # 고가-저가 등락률이 10% 이상
AND T2.UP_TAIL_RT >= 90 # 위꼬리 비율이 90% 이상
ORDER BY PROF_RT DESC
)
SELECT T2.*
,ROUND((T2.W_PROF_RT * T2.W_RT) - (ABS(T2.L_PROF_RT)* T2.L_RT),2) TE
FROM (
SELECT COUNT(*) TRD_CNT
,SUM(CASE WHEN T1.PROF_RT >= 1 THEN 1 END) W_CNT
,ROUND(AVG(CASE WHEN T1.PROF_RT >= 1 THEN T1.PROF_RT END),2) W_PROF_RT
,ROUND(SUM(CASE WHEN T1.PROF_RT >= 1 THEN 1 END) / COUNT(*) * 100, 2) W_RT
,SUM(CASE WHEN T1.PROF_RT < 1 THEN 1 END) L_CNT
,ROUND(AVG(CASE WHEN T1.PROF_RT < 1 THEN T1.PROF_RT END),2) L_PROF_RT
,ROUND(SUM(CASE WHEN T1.PROF_RT < 1 THEN 1 END) / COUNT(*) * 100, 2) L_RT
FROM R01 T1
) T2
;
TRD_CNT W_CNT W_PROF_RT W_RT L_CNT L_PROF_RT L_RT TE
--------- ------- ----------- ------- ------- ----------- ------- --------
28 10 7.17 35.71 18 -4.91 64.29 -59.62
지난 글에서는 2020년 10월 중에 위꼬리가 긴 데이터를 검색했습니다. 이번에는 위꼬리가 긴 종목을 매도했을 때에 수익률이 어떤지까지 연결해보려고 합니다. 매매를 하기 위해서는 매수(Buy) 시점, 매도(Sell) 시점을 정해야 합니다. 매수 시점은 앞에 글에서 구한 위꼬리가 나온 날의 종가입니다. 매도 시점은 간단하게 3일 후 종가로 하도록 하겠습니다. 지난 글의 SQL에 아래와 같이 3일 후 종가를 가져오도록 처리하면 됩니다. SQL이 좀 길어졌지만 겁먹지 말고 천천히 따라서 입력해보시기 바랍니다.
SELECT T2.STK_CD ,T2.DT BUY_DT ,T2.C_PRC BUY_PRC ,T2.H_L ,T2.UP_TAIL ,T2.H_L_CHG ,T2.UP_TAIL_RT
,T3.DT SELL_DT ,T3.C_PRC SELL_PRC
,ROUND((T3.C_PRC - T2.C_PRC) / T2.C_PRC * 100,2) PROF_RT # 3일후 매도시 수익률
FROM (
SELECT T1.STK_CD
,T1.DT
,T1.C_PRC
,T1.H_PRC - T1.L_PRC H_L # 고가-저가 길이
,T1.H_PRC - GREATEST(T1.C_PRC,T1.O_PRC) UP_TAIL # 위꼬리 길이
,ROUND((T1.H_PRC - T1.L_PRC) / T1.L_PRC * 100,2) H_L_CHG # 위꼬리 등락률
,ROUND((T1.H_PRC - GREATEST(T1.C_PRC,T1.O_PRC))
/ (T1.H_PRC - T1.L_PRC) * 100,2) UP_TAIL_RT # 위꼬리 비율
,T1.STK_DT_NO # --> 종목별일자 순번
FROM MYSTKDB.HISTORY_DT T1
WHERE 1=1
AND T1.DT >= STR_TO_DATE('20201001','%Y%m%d')
AND T1.DT < STR_TO_DATE('20201101','%Y%m%d')
) T2 INNER JOIN MYSTKDB.HISTORY_DT T3 # 위꼬리 발생 3일 후 주가 데이터를 조인
ON (T3.STK_CD = T2.STK_CD AND T3.STK_DT_NO = T2.STK_DT_NO + 3)
WHERE T2.H_L_CHG >= 10 # 고가-저가 등락률이 10% 이상
AND T2.UP_TAIL_RT >= 90 # 위꼬리 비율이 90% 이상
ORDER BY PROF_RT DESC
;
여기서 포인트는 바로 STK_DT_NO입니다. 해당 컬럼은 종목별 일자별 순번이 저장된 컬럼입니다. 해당 컬럼을 이용하면 해당 종목의 3일후 데이터를 가져올 수 있습니다. 날짜 계산을 통해서도 처리할 수 있겠지만 휴일 처리가 매우 복잡해집니다. 그러므로 이와 같은 종목별 순번을 사용하면 손쉽게 몇일 후, 몇일 전 데이터를 조인으로 처리할 수 있습니다(이와 같은 방법을 이미 아시는 분들도 있겠지만, 솔직히 소문내지 않고 저만 알고 싶은 방법입니다.) 위 SQL의 결과를 살펴보면 아래와 같습니다.
가장 위에 있는 '013700' 종목은 3일 후 수익률이 무려 26.44 %입니다. 행복한 결말이죠. 하지마 가장 아래에 있는 '289080'은 3일 후에 무려 15.17%를 손해봅니다. 불행한 결말이죠. 이처럼 주식 트레이딩에는 행복과 불행이 항상 공존합니다. 행복만 바라보고 트레이드를 한다면 불행의 쓰디쓴 맛을 보게 됩니다. 실제로도 그런지 차트를 통해 확인해보도록 하겠습니다. 이처럼 데이터 분석과 전략 생성을 위해서는 중간 중간 데이터 확인을 해야 합니다. 먼저 013700의 차트를 살펴보면 아래와 같습니다. 3일후 주가가 오른 것을 알 수 있습니다.
이번에는 289080의 차트를 살펴봅니다. 3일후 주가가 떨어진것을 알 수 있습니다.
오늘은 위꼬리가 발생한 종목의 3일후 주가를 연결하고 수익률까지 구해봤습니다. 다음 글에서는 데이터 집계를 통해 트레이딩 엣지(Trading Edge, TE)를 구해보도록 하겠습니다. 다시 한 번 말씀드리지만, 소개하는 트레이드 전략은 매우 위험합니다. 주식 차트의 봉 모양을 보고 매매를 한다는 것 자체가 매우 위험한 방법입니다. 절대 주식 투자나 종목 매매를 권장하는 글이 아닙니다. 기술적인 설명을 위한 글이며, 이 글을 참고해 발생한 투자 손실에는 그 누구도 절대 책임지지 않습니다.
최근 매매에 들어간 종목 세 개 정도가 계속해서 위꼬리가 나오네요. 결과적으로는 매우 않좋게 마무리가 되었습니다. ㅠㅠ. 세 종목이 다.!!!! 그래서, '위꼬리'를 신호로 삼아 매매를 하면 어떻게 될까를 분석해볼까 합니다. 더불어, 저의 경우에는 트레이드 전략을 어떻게 만들고 있는지도 간단히 참고하실 수 있을거 같습니다.
우선 여기서 소개하는 트레이드 전략은 매우 위험합니다. 주식 차트의 봉 모양을 보고 매매를 한다는 것 자체가 매우 위험한 방법입니다. 본격적인 설명에 앞서, 절대 주식 투자나 종목 매매를 권장하는 글이 아닙니다. 기술적인 설명을 위한 글이며, 이 글을 참고해 발생한 투자 손실에는 그 누구도 절대 책임지지 않습니다.
해당 DB에는 2019년부터 2021년말까지의 일별 주가 데이터가 존재합니다. 또한 일부 종목은 없을수도 있지만, 대부분의 종목은 있다고 보시면 됩니다.
먼저 '위꼬리 긴 하루'를 아래와 같이 정의합니다. (아래 내용은 각자 자유롭게 기준을 만들수 있겠죠.)
고가-저가 등락률이 10% 이상이면서, 위꼬리 길이가 고가-저가 길이의 90% 이상을 차지할 것
고가-저가 길이(H_L): 고가 - 저가
위꼬리 길이(UP_TAIL): 고가 - GREATEST(종가,시가)
고가-저가 등락률(H_L_CHG): (고가 - 저가) / 저가 * 100
위꼬리 길이 비율(UP_TAIL_RT): 위꼬리 길이 / 고가-저가 길이 * 100
2020년 10월 주가 데이터 중에서 위 조건에 맞는 데이터를 조회해봅니다. 아래와 같습니다.
SELECT T2.*
FROM (
SELECT T1.STK_CD
,T1.DT
,T1.C_PRC
,T1.H_PRC - T1.L_PRC H_L # 고가-저가 길이
,T1.H_PRC - GREATEST(T1.C_PRC,T1.O_PRC) UP_TAIL # 위꼬리 길이
,ROUND((T1.H_PRC - T1.L_PRC) / T1.L_PRC * 100,2) H_L_CHG # 위꼬리 등락률
,ROUND((T1.H_PRC - GREATEST(T1.C_PRC,T1.O_PRC))
/ (T1.H_PRC - T1.L_PRC) * 100,2) UP_TAIL_RT # 위꼬리 비율
FROM MYSTKDB.HISTORY_DT T1
WHERE 1=1
AND T1.DT >= STR_TO_DATE('20201001','%Y%m%d')
AND T1.DT < STR_TO_DATE('20201101','%Y%m%d')
) T2
WHERE T2.H_L_CHG >= 10 # 고가-저가 등락률이 10% 이상
AND T2.UP_TAIL_RT >= 90 # 위꼬리 비율이 90% 이상
;
STK_CD DT C_PRC H_L UP_TAIL H_L_CHG UP_TAIL_RT
-------- ------------ ----------- ----------- ----------- --------- ------------
001840 2020-10-06 5540.000 570.000 540.000 10.34 94.74
054450 2020-10-08 15450.000 2450.000 2250.000 16.07 91.84
007210 2020-10-12 2010.000 205.000 190.000 10.28 92.68
... 생략
가장 위에 있는 '001840'이란 종목이 2020년 10월 6일에 위꼬리가 만들어진 것으로 나왔습니다. 실제 그랬는지 차트를 통해 살펴봐야겠죠. 차트를 확인해 보니, 아래와 같이 10월 6일에 위꼬리가 긴 음봉이 만들어졌네요. 그 후, 4일 후 정도까지는 약간 올랐지만 쭉 하락세네요.
결과의 두 번째인 '054450'도 살펴보죠. 10월 8일에 위꼬리가 긴 양봉이 만들어 졌네요. 마찬가지로 잠깐 올랐다가 쭈욱 하락세네요.
이처럼 SQL만 이용해서, 특정 패턴의 차트를 찾아낼 수 있습니다. 오늘은 여기까지 살펴보고, 다음 글에 이어서 계속 설명하도록 하겠습니다. 감사합니다.
pymysql을 사용해, 가져온 주가 정보를 개인용 주식 DB에 insert까지 해보도록 하겠습니다.
먼저 아래를 참고해 테이블을 생성합니다.
CREATE TABLE MYTRDDB.KRX_HIST_DT(
STK_CD VARCHAR(40) NOT NULL
,DT DATE NOT NULL
,O_PRC DECIMAL(18,3)
,H_PRC DECIMAL(18,3)
,L_PRC DECIMAL(18,3)
,C_PRC DECIMAL(18,3)
,VOL DECIMAL(18,3)
,CHG_RT DECIMAL(18,3)
,PRIMARY KEY(STK_CD, DT)
);
간단히 dataframe 관련된 명령어 몇 개를 살펴봅니다.
# Dataframe의 Index 값 중에, 최대값, 최소값 가져오기.
df.index.min()
df.index.max()
# Dataframe의 Index 값을 loop로 순차적으로 가져오기.
# 가저온 Index 값을 이해용해 해당 로우의 데이터를 가져오기.
for ix in df.index:
print(ix)
print(df.loc[ix].시가)
print(df.loc[ix].고가)
최종 아래와 같은 소스코드를 통해 삼성전자(005930)의 주가 정보를 가져와, 우리가 위에서 만들었던 MYSTKDB.KRX_HIST_DT에 입력할 수 있습니다.
기존에 클래스내에 static으로 만들었던 함수는, static을 모두 제거했습니다.
get_stock_history에서 새로 만든 함수인, insert_hist_dt를 호출하도록 구성되어 있습니다.
get_stock_history에서는 pykrx를 통해, 데이터프레임에 주가 정보를 입력하고,
insert_hist_dt에서는 데이터프레임을 넘겨받아 주가 데이터를 insert합니다.
이때, 기존에 입력된 구간과 겹쳐지지 않도록 delete를 처리합니다.
import pymysql
from pykrx import stock
from datetime import datetime, timedelta
class MyCollectKRX:
def get_stock_master(self, _market):
stk_master = []
for stk_cd in stock.get_market_ticker_list(market=_market):
stk_nm = stock.get_market_ticker_name(stk_cd)
stk_master.append((stk_cd, stk_nm, _market))
return stk_master
def get_stock_history(self, _stk_cd, _fr_ymd, _to_ymd):
if _to_ymd == '':
_to_ymd = datetime.now().strftime('%Y%m%d')
if _fr_ymd == '':
_fr_ymd = (datetime.strptime(_to_ymd, '%Y%m%d') - timedelta(weeks=54)).strftime('%Y%m%d')
df = stock.get_market_ohlcv(_fr_ymd, _to_ymd, _stk_cd)
self.insert_hist_dt(_stk_cd, df)
def insert_hist_dt(self, _stk_cd, _df):
# MySQL 연결 객체 생성
myConn = pymysql.connect(user='???', password='???', host='localhost', port=3306,
charset='utf8', database='MYTRDDB')
myCursor = myConn.cursor()
_fr_ymd = _df.index.min()
_to_ymd = _df.index.max()
sql_del = "DELETE T1 FROM MYTRDDB.KRX_HIST_DT T1 WHERE T1.STK_CD = %s AND T1.DT BETWEEN %s AND %s"
myCursor.execute(sql_del, (_stk_cd, _fr_ymd, _to_ymd))
for ix in _df.index:
sql_ins = "INSERT INTO MYTRDDB.KRX_HIST_DT(STK_CD ,DT ,O_PRC ,H_PRC ,L_PRC ,C_PRC ,VOL ,CHG_RT)"
sql_ins = sql_ins + " VALUES(%s ,%s ,%s ,%s ,%s ,%s ,%s ,%s)"
myCursor.execute(sql_ins, (
_stk_cd, ix, _df.loc[ix].시가, _df.loc[ix].고가, _df.loc[ix].저가, _df.loc[ix].종가, _df.loc[ix].거래량, _df.loc[ix].등락률))
myConn.commit()
myCursor.close()
myConn.close()
if __name__ == '__main__':
myCollectKRX = MyCollectKRX()
stk_cd = '005930'
myCollectKRX.get_stock_history(_stk_cd=stk_cd, _fr_ymd='', _to_ymd='')
print('작업 완료')
제가 구성한 Trade Optimizer의 구성에 대해 간단히 설명을 해볼까 합니다. Trade Optimizer는 Python과 MySQL로 구성되어 있습니다.
Collector: 주식 관련 데이터를 수집합니다.
Trade Optimizer: 트레이딩을 위해 데이터를 정리 및 집계하고, 시뮬레이션을 수행합니다.
Telegram Bot: 만들어진, 시뮬레이션 결과를 텔레그램으로 전송합니다.
파이썬보다 SQL로 구성된 모듈이 더 많은 부분을 차지하고 있습니다. 파이썬이 종목별 상세한 시뮬레이션을 수행한다면, SQL로는 전체 데이터를 집계하거나 큰 계산, 최종 트레이딩 대상 선정 등의 작업을 처리합니다. 저 같은 경우, SQL을 통해 전체적인 트레이딩 전략 방향을 잡고, 파이썬으로 상세한 시뮬레이션을 처리합니다. 개인적으로 파이썬만큼 SQL을 잘 다루는 능력이 중요하다고 생각합니다.
Trade Optimizer 모듈의 경우, 시총 Top-500 종목에 대해, 트레이딩 시뮬레이션(백테스트)을 수행하고, 오늘자로, 가장 트레이딩 EDGE가 있는 종목을 찾아내는 작업을 합니다. 저 같은 경우는 초단타가 아닌, 추세 추종으로 중장기 트레이딩을 하는 전략을 사용하고 있습니다. 중장기 트레이딩이다 보니, 실제 주식 매매는 손으로 직접 처리하고 있습니다.
넌클러스터드 인덱스와 클러스터드 인데스의 차이점을 다시 한 번 짚어보면 아래 그림과 같습니다.
넌클러스터드 인덱스와 클러스터드 인덱스 간의 가장 큰 차이점은, 리프 노드의 구성입니다.
넌클러스터드 인덱스: 리프 노드에는 인덱스 컬럼의 값과, 해다 테이블의 클러스터드 인덱스의 컬럼 값이 존재
클러스터드 인덱스: 리프 노드에는 실제 데이터가 존재
이 부분을 정확히 알고 있어야 합니다.
자 그러면, 아래 SQL을 살펴봅니다. WHERE 절에서는 ORD_DT에 같다(=) 조건이 사용되었고, SELECT 절에서는 ORD_DT 컬럼만 사용하고 있습니다.
SELECT T1.ORD_DT
FROM MYTUNDB.T_ORD_BIG T1
WHERE T1.ORD_DT = STR_TO_DATE('20170104','%Y%m%d');
EXPLAIN
------------------------------------------------------------------------------------------------------------
-> Index lookup on T1 using T_ORD_BIG_X1 (ORD_DT=str_to_date('20170104','%Y%m%d')) (cost=92.52 rows=900) (actual time=0.087..0.554 rows=900 loops=1)
위 SQL의 실행계획을 보면, T_ORD_BIG_X1 인덱스를 사용해 처리가 된 것을 알 수 있습니다. actual time은 0.087, cost는 92.52라고 측정되어 있습니다. 위 과정을 그림으로 그려보면, 아래와 같습니다. 앞에서도 계속 설명했던 내용입니다.
아래 그림에서 1번 과정은 수직적 탐색, 2번 과정은 수평적 탐색입니다.
1번, 수직적 탐색은 WHERE 절 조건에 해당하는 값이 어디서부터 시작인지, 위치를 찾기 위한 과정입니다.
2번, 수평적 탐색은 WHERE 절 조건을 만족하지 않을때까지 리프 노드를 차례대로 검색하는 과정입니다.
이번에는 아래 SQL을 살펴보도록 합니다. 방금 살펴본 위의 SQL과 거의 같습니다. 다른 점은 SELECT 절에 ORD_DT 외에도 ORD_SEQ 컬럼이 있다는 점입니다. cost는 위의 SQL과 같고, actual time이 아주 약간 늘어난 것을 확인 할 수 있지만, 거의 차이가 없다고 보셔도 됩니다.
아래 SQL의 실행 계획 역시 위의 그림과 동일합니다. T_ORD_BIG_X1 인덱스만 사용해 원하는 결과를 처리합니다. 넌클러스터드 인덱스의 리프 노드에는 클러스터드 인덱스의 컬럼인 ORD_SEQ의 값도 존재하므로, ORD_SEQ 값을 가져오기 위한 추가적인 인덱스 접근이나 액션이 필요하지 않습니다.
SELECT T1.ORD_DT ,T1.ORD_SEQ
FROM MYTUNDB.T_ORD_BIG T1
WHERE T1.ORD_DT = STR_TO_DATE('20170104','%Y%m%d');
EXPLAIN
------------------------------------------------------------------------------------------------------------
-> Index lookup on T1 using T_ORD_BIG_X1 (ORD_DT=str_to_date('20170104','%Y%m%d')) (cost=92.52 rows=900) (actual time=0.133..1.150 rows=900 loops=1)
이번에는 아래 SQL과 같이 SELECT 절에 T1.*를 사용해 모든 컬럼을 조회하도록 합니다. 실행계획을 살펴보면, 이전 SQL들과 마찬가지로 T_ORD_BIG_X1 인덱스를 사용하고 있습니다. 그런데 뒤쪽의 cost를 보면, 987.60으로 10배 가까이 증가한 것을 알 수 있습니다. actual time 역시 0.475로 증가했습니다.
SELECT T1.*
FROM MYTUNDB.T_ORD_BIG T1
WHERE T1.ORD_DT = STR_TO_DATE('20170104','%Y%m%d');
EXPLAIN
-------------------------------------------------------------------------------------------------------------
-> Index lookup on T1 using T_ORD_BIG_X1 (ORD_DT=str_to_date('20170104','%Y%m%d')) (cost=987.60 rows=900) (actual time=0.475..3.152 rows=900 loops=1)
위 SQL의 경우, 실행계획에는 T_ORD_BIG_X1만 사용한 것으로 표시되어 있습니다. 하지만 실제로는, T_ORD_BIG 테이블의 클러스터드 인덱스도 사용하고 있습니다.
위 SQL의 결과를 만들기 위해서는 ORD_DT가 2017년 1월 4일인 로우들의 모든 값이 필요합니다.
이 값들은 클러스터드 인덱스에 저장되어 있습니다. 그러므로 내부적으로 클러스터드 인덱스를 경유해야만 위 SQL의 결과를 처리할 수 있습니다. 안타깝게도, MySQL의 실행계획에서는 해당 작업을 별도로 표시해 주지는 않습니다.
위 SQL을 처리한 과정을 그려보면 아래와 같습니다.
1번, 2번은 넌 클러스터드 인덱스로 WHERE 조건에 해당하는 값의 클러스터드 인덱스의 키 값(ORD_SEQ)을 찾는 과정입니다.
3번은 2번에서 찾은 ORD_SEQ를 사용해, 클러스터드 인덱스를 검색하러 가는 과정입니다.
4번, 5번은 3번의 ORD_SEQ를 사용해 실제 데이터를 찾아오는 과정입니다.
논리적으로 3번, 4번, 5번 작업은 2번에서 찾은 건수만큼 반복 실행됩니다. 내부 매커니즘을 통해 반복 3, 4, 5번의 반복 횟수를 줄일 것이라 예상되지만, 논리적으로는 반복 실행된다고 생각하면 됩니다.
오늘 살펴볼 내용은 여기까지입니다.
자 그러면, 우리는 SELECT 절에 무턱대고 '*'를 사용해서는 안되겠죠. '*'를 처리하기 위해서는 실제 데이터의 접근이 발생할 수 밖에 없으니까요.
또한 테이블의 접근(클러스터드 인덱스 접근)을 피하기 위해 무턱대고 넌클러스터드 인덱스의 컬럼을 늘릴 수는 없겠죠. 이는 CUD 작업의 성능 저하로 이어지니까요. 그래서 인데스 설계가 어렵습니다.
그런데, MySQL은 좀 더 어렵습니다. 이와 같은 넌클러스터드와 클러스터드 구조를 반드시 머리에 넣고 성능을 고려한 인덱스를 설계를 해야 하니까요.
개인적으로 pykrx를 만들어 배포해주신 분들께 매우 감사한 마음입니다. 많은 귀찮은 과정을 손쉽게 해결해주셨으니까요, 좋은일 가득하시길!!
아래 소스는 pykrx를 사용해, 종목코드와 종목명, 마켓명(코스피, 코스닥)을 리스트 형태로 가져와 조회하는 내용입니다.
앞으로의 확장성을 고려해 클래스 형태로 모듈을 만들었습니다.
# pip install pymysql
# pip install matplotlib
# pip install pykrx
from pykrx import stock
class MyCollectKRX:
@staticmethod
def get_stock_master(_market):
stk_master = []
for stk_cd in stock.get_market_ticker_list(market=_market):
stk_nm = stock.get_market_ticker_name(stk_cd)
stk_master.append((stk_cd, stk_nm, _market))
return stk_master
if __name__ == '__main__':
myCollectKRX = MyCollectKRX()
stk_master_all = myCollectKRX.get_stock_master('KOSPI')
stk_master_all.extend(myCollectKRX.get_stock_master('KOSDAQ')) # 리스트 두 개를 합치기 위해서는 extend 사용
for stk in stk_master_all:
print(stk)
위 모듈을 실행하면, 종목코드, 종목명, 마켓명을 리스트 형태로 출력할 수 있습니다. 이후, 이 데이터를 DB화해서 저장까지 해볼 수 있겠죠.
여기서, 눈여겨 볼만한거 하나는 리스트 형태의 데이터를 합치기 위해서는 extend를 사용한다는 점입니다.
append는 리스트 내에 하나의 요소를 더하는 작업이고, 리스트 두 개를 단순히 합치기 위해서는 extend를 사용해야 합니다.
위 내용을 DB로 저장하기 위해서 테이블을 생성할 차례입니다. 여기서는 MySQL을 사용합니다. 아래와 같이 MYTRDDB 데이터베이스를 생성하고, 해당 데이터베이스에 테이블까지 생성합니다.
# SQL 툴을 이용해 MySQL에 접속해서 실행
CREATE DATABASE MYTRDDB; # DB 생성
CREATE TABLE MYTRDDB.KRX_STOCK(
STK_CD VARCHAR(40) NOT NULL
,STK_NM VARCHAR(200) NOT NULL
,MKT_NM VARCHAR(40) NOT NULL
,PRIMARY KEY(STK_CD)
);
위 테이블에 Insert하는 과정까지 추가해서 파이썬 소스를 변경해보면, 아래와 같습니다.
# pip install pymysql
# pip install matplotlib
# pip install pykrx
from pykrx import stock
import pymysql # MYSQL 연결및, SQL 실행을 위해 추가
class MyCollectKRX:
@staticmethod
def get_stock_master(_market):
stk_master = []
for stk_cd in stock.get_market_ticker_list(market=_market):
stk_nm = stock.get_market_ticker_name(stk_cd)
stk_master.append((stk_cd, stk_nm, _market))
return stk_master
if __name__ == '__main__':
myCollectKRX = MyCollectKRX()
stk_master_all = myCollectKRX.get_stock_master('KOSPI')
stk_master_all.extend(myCollectKRX.get_stock_master('KOSDAQ')) # 리스트 두 개를 합치기 위해서는 extend 사용
# MySQL 연결 객체 생성
myConn = pymysql.connect(user='???', password='???', host='localhost', port=3306,
charset='utf8', database='MYTRDDB')
myCursor = myConn.cursor()
for stk in stk_master_all:
sql_ins = "INSERT INTO MYTRDDB.KRX_STOCK(STK_CD ,STK_NM ,MKT_NM) VALUES(%s, %s, %s)" # INSERT SQL 작성
myCursor.execute(sql_ins, (stk[0], stk[1], stk[2])) # 튜플 형태로 파라미터 전달해서, SQL 실행
# COMMIT 처리 및, MySQL 개체 CLOSE
myConn.commit()
myCursor.close()
myConn.close()
print('작업 완료')
위 파이썬 소스를 실행한 후에, MySQL에 접속해, 아래와 같이 SQL을 실행해보면, 주식 마스터 정보가 자신의 DB에 만들어진 것을 확인할 수 있습니다.
아래와 같이 ORD_DT에 범위 조건을 사용해 2017년 1월 4일 하루치의 데이터만 조회하는 SQL을 작성하고 EXPLAIN 처리합니다. 얻은 실행 계획을 보면, type 부분을 보면 range 라고 되어 있는 것을 알 수 있습니다.
type의 range는 범위 조건 처리를 위해 인덱스가 사용되어진 것을 뜻합니다. 어떤 인덱스를 사용했는지는 key 부분에 명시되어 있습니다. 또한 가장 오른쪽 Extra 항목을 보면, Using index라고 추가적으로 명시되어 있습니다.
EXPLAIN
SELECT COUNT(*)
FROM MYTUNDB.T_ORD_BIG T1
WHERE T1.ORD_DT >= STR_TO_DATE('20170104','%Y%m%d')
AND T1.ORD_DT < STR_TO_DATE('20170105','%Y%m%d');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- --------------------------
1 SIMPLE T1 None range T_ORD_BIG_X1 T_ORD_BIG_X1 6 None 900 100.0 Using where; Using index
이번에는 ORD_DT에 같다(=) 조건을 사용해 2017년 1월 4일 하루치 데이터를 조회하도록 합니다. 실행계획을 살펴보면 아래와 같습니다. 이번에는 type이 ref로 표시되어 있습니다. 이처럼 같다(=) 조건에 인덱스가 사용되면 ref라고 표시가 됩니다. 위에서 살펴본 것과 마찬가지로 key에는 어떤 인덱스를 사용했는지가 표시되어 있고, Extra에도 Using index라고 표시 되어 있습니다.
EXPLAIN
SELECT COUNT(*)
FROM MYTUNDB.T_ORD_BIG T1
WHERE T1.ORD_DT = STR_TO_DATE('20170104','%Y%m%d');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
---- ------------- ------- ------------ ------ --------------- -------------- --------- ------- ------ ---------- -------------
1 SIMPLE T1 None ref T_ORD_BIG_X1 T_ORD_BIG_X1 6 const 900 100.0 Using index
마지막으로, ORD_DT 컬럼을 변형해 인덱스를 효율적으로 사용할 수 없도록 쿼리를 작성해 실행해봅니다. 실행계획을 확인해 보면, 아래와 같이 type 부분에 index라고 표시되어 있습니다. 이를 보고, '인덱스를 잘 타고 있구나'라고 착각하면 안됩니다. 인덱스를 사용한 것은 맞지만, 효율적으로 사용한 케이스는 아닙니다.
Key에는 어떤 인덱스를 사용했는지, Extra에 using index 라고 표시되어 있지만, 인덱스 리프 노드를 모두 스캔하는 Index full scan을 하고 있는 동작입니다.
EXPLAIN
SELECT COUNT(*)
FROM MYTUNDB.T_ORD_BIG T1
WHERE DATE_FORMAT(T1.ORD_DT,'%Y%m%d') = '20170104'
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ -------- ---------- --------------------------
1 SIMPLE T1 None index None T_ORD_BIG_X1 6 None 301158 100.0 Using where; Using index
고전적인 실행 계획으로 살펴봤을 때, 인덱스를 잘 사용하고 있는지는 type 항목을 보셔야 합니다. type 항목 값에 따라 아래와 같이 정리할 수 있습니다.
