요즘에 깃허브 코파일럿을 파이썬에 연동해서 사용하는중인데요... 이게... 뭐랄까.. 이전에 자동 완성을 완전히 뛰어 넘네요. 예를 들어, 긴 예약어나 변수를 자동완성하는게 아니고. 그다음 코드 한줄을 완전히 자동으로 만들어 버리네요. 코파일럿이 만든 코드가 맞으면 tab만 입력하면 되네요...
기존에 GPT로 코드 도움을 많이 받으면서도 놀랐는데.. 이건뭐... 대화좀 하고, 상황 설명하고, 탭만 누르면 된다니. 어허... 어허.... 어허.. 뭐라 할말이 없네요..
데이터 분석을 위해서는 시각화가 중요합니다. 시각화를 통해 캐치할 부분을 비교적 쉽게 찾아낼 수 있습니다. 일한 성과를 시각화하는 것도 매우 중요한 일입니다. 또한 쉽지 않은 일이기도 하고요. 그래서 데이터 시각화, 인사이트에 대한 책들을 종종 읽어보고 있습니다.
오늘은 제가 경험했던, 선만 추가했을 뿐인데 OK가 된 리포트의 사례에 대해 정리해봤습니다.
이전 프로젝트에서 SQL 튜닝 프로젝트 결과를 리포팅한적이 있습니다.
열심히 몇일 동안 튜닝 후 결과를 시각화해보니, 아래와 같이 결과가 나왔습니다. 아래는 SQL별 일별 평균 실행 시간을 시각화한 차트입니다.
차트를 보면 일별로 SQL 평균 실행시간이 들쑥날쑥합니다.
해당 시스템은, 대량의 데이터를 처리하는 복잡한 배치가 특정 요일(월과 목)에 집중 실행되어, SQL 평균 실행시간이 요일별로 들쑥날쑥한 상황입니다. 업무를 살펴보니, 월과 목요일에 주로 제품 출하가 있으며 이때마다 시스템이 버벅되는 상황이었습니다.
빨간 세로 점선을 기준으로 왼쪽은 튜닝 반영전, 오른쪽은 튜닝 반영후입니다. 전과 후를 비교해보면, 좋아지긴 좋아졌습니다.
하지만, 여전히 배치 자체가 처리해야할 물리적인 데이터 양 때문에 개선에 한계가 있으며,
개선후에도 요일별로 SQL 평균 실행 시간이 들쑥날쑥합니다.
Tip.!
이러한 대량 데이터 배치 SQL 성능의 한계를 극복하기 위해서는 개발자와 업무 담당자의 적극적인 지원이 필요하며, 상황에 따라 배치 프로세스 전체를 변경하거나 테이블 구조 변경도 고려해야 합니다. 또한 세밀한 테스트가 필요해 튜닝 작업이 쉽지 않습니다. 대부분 사이트들이 배치 작업에 대해 튜닝을 하고 싶어하지만 현실적으로 변경에 대한 작업양과 리스크가 너무 커서 실제 바꾸지 못하는 경우가 많습니다.
위와 같은 튜닝 결과 차트를 실무 담당 고객에게 보여주니, 결과서를 팀장에게 보고하기에는 좀 난감해보인다고 하더군요. 자세히 보고 생각해보면 좋아진건 알겠는데, 첫 눈에 성능이 개선된걸 알 수 있어야 할거 같다고 하더군요.
제가 생각해도... 위의 차트를 이용해 고객에게 프로젝트 성과를 와닿게 하기는 쉽지 않겠다는 생각이 들더군요.
그래서.. 좀 고민을 하다가... 아래와 같이 가로선을 추가해서 고객에게 보여줬습니다.
그랬더니, 고객도.. 음.. 좋아졌네요. 고개를 끄덕거리시네요.
SQL 평균실행시간- 가로선 추가
오래전 일이지만, 소개를 한번 해보면 좋겠다 싶었던 내용입니다. 데이터 분석을 위해서는 시각화가 중요합니다.
시각화를 통해 캐치할 부분을 비교적 쉽게 찾아낼 수 있습니다.
일한 성과를 시각화하는 것도 매우 중요한 일입니다. 또한 쉽지 않은 일이기도 하고요.
그래서 데이터 시각화, 인사이트에 대한 책들을 종종 읽어보고 있습니다.
지금 다시 보고서를 만든다면, 아래와 같이 이동평균도 이용해볼 수 있을거 같네요. 아래는 7일간의 이동평균(오렌지색 점선)을 추가한 결과입니다.
우선, DBeaver에 연동해서 API 형태로 GPT를 사용하는 것은 실제 OpenAI에 접속해 GPT와 채팅하면서 답변을 얻는 것과는 차이가 있어 보입니다. 아무래도 GPT와 채팅하면서 얻는 답변이 좀 더 품질이 좋아보입니다.
테스트한 결과를 살펴보겠습니다.
먼저, 매장 테이블과 주문 테이블을 조인하는 간단한 SQL을 요청했습니다.
SQL에 성능 문제가 발생되도록 작성되었습니다. OrdDtm 컬럼을 DATE_FORMAT으로 가공해 인덱스가 있어도 사용을 못하도록 SQL이 만들어졌습니다. 그리고 아래 SQL의 프롬프트는 제 실수가 있었습니다. 사실 매장등급이란 컬럼은 없습니다. 없으면 없다고 메시지를 주면 좋은데, 그러지는 않네요. 자신이 생각할때 적절하다고 생각한 컬럼을 사용해 결과를 만들어 냅니다.
-- 프롬프트: 매장 테이블과 주문 테이블을 조인해서 보여줘,
-- 주문일시가 2022년 12월이고, 매장등급이 플래티넘(PLAT)인 데이터만 조회해줘
SELECT *
FROM shop s
JOIN ord o ON s.ShopId = o.ShopId
WHERE DATE_FORMAT(o.OrdDtm, '%Y%m') = '202212' AND s.ShopSt = 'PLAT';
이번에는 성능을 고려하라고 프롬프트를 추가하고, 매장이 아닌 회원과 조인을 요청합니다. 이번 SQL은 100점입니다. 인덱스 사용을 고려해달라고 요청하니 OrdDtm에 적절하게 조건 처리를 했습니다. 물론, OrdDtm에 인덱스는 아직 없지만요.
-- 프롬프트
-- 회원 테이블과 주문 테이블을 조인해서 보여줘,
-- 주문일시가 2022년 12월이고, 회원등급이 플래티넘(PLAT)인 데이터만 조회해줘.
-- 성능을 고려해서 SQL을 작성해줘. 인덱스를 사용할 수 있게 SQL을 작성해야 한다는 거지.
-- Sure, here is the SQL query that joins the `member` and `ord` tables, and filters for orders made in December 2022 by members with a 'PLAT' grade. This query is optimized for performance by using the appropriate indexes.
/*
This query joins the member and ord tables on the MemberId field.
It then filters for orders made in December 2022 and by members with a 'PLAT' grade.
The WHERE clause is written in a way that allows the use of indexes for performance optimization.
*/
SELECT m.*, o.*
FROM `member` AS m
INNER JOIN ord AS o ON m.MemberId = o.MemberId
WHERE m.MemberGd = 'PLAT'
AND o.OrdDtm >= '2022-12-01 00:00:00'
AND o.OrdDtm < '2023-01-01 00:00:00';
-- Please note that the date format and the 'PLAT' value are just examples and should be replaced with the actual format and value used in your database. Also, make sure that there are indexes on the `MemberId`, `MemberGd`, and `OrdDtm` fields for optimal performance.
휴~ 다행이네요. 아래 SQL은 에러가 발생합니다. SELECT 절에서 ItemId 컬럼을 사용할때 테이블이 지정되지 않았기 때문이죠.
-- [BOOSTER QUIZ 20-2] 2022년 11월 2022년 12월 주문에 대해, 주문년월별 상품별 주문수량을 구해주세요, 주문년월별로 주문수량에 따른 순위를 구해주세요.
-- - 대상 테이블: 주문(Ord), 주문상세(OrdDet), 상품(Item)
-- - 조회 조건: OrdDtm이 2022년 11월부터 2022년 12월 말까지인 데이터
-- - 조회 컬럼: OrdYm, ItemId, ItemNm, SumOrdQty, RankOverByYm
-- - 추가 조건:
-- : OrdYm, ItemId별 GROUP BY 처리하시오.
-- : OrdYm은 OrdDtm을 DATE_FORMAT으로 년월 형태로 변환한 값입니다.
-- : SumOrdQty는 OrdYm, ItemId별 SUM한 OrdQty 값입니다.
-- : RankOverByYm은 ItemId의 판매수량(SumOrdQty)에 대한 순위입니다. 이 순위는 년월별로 구해야 하며 판매 수량이 높으면 1이 됩니다.
SELECT
DATE_FORMAT(OrdDtm, '%Y%m') AS OrdYm,
ItemId,
ItemNm,
SUM(OrdQty) AS SumOrdQty,
RANK() OVER (PARTITION BY DATE_FORMAT(OrdDtm, '%Y%m') ORDER BY SUM(OrdQty) DESC) AS RankOverByYm
FROM
ord
JOIN
orddet ON ord.OrdNo = orddet.OrdNo
JOIN
item ON orddet.ItemId = item.ItemId
WHERE
OrdDtm BETWEEN '2022-11-01' AND '2022-12-31'
GROUP BY
DATE_FORMAT(OrdDtm, '%Y%m'),
ItemId,
ItemNm
ORDER BY
OrdYm,
RankOverByYm;-- This query will return the total quantity of each item ordered per month for November and December 2022, along with a rank for each item based on the quantity ordered. The rank is calculated separately for each month.
DBeaver에 GPT API를 연동해 다양한 SQL을 시도해봤습니다. 아직은 API 연동으로 복잡한 SQL을 작성하는 데는 한계가 있어 보입니다.
(GPT와 직접 채팅으로 시도해보면 좀 더 결과가 잘나오지 않을까 싶네요.) 그러나 책으로만 SQL을 배운 신입보다는 낫다고도 할 수 있습니다.
지금은 AI를 활용한 SQL 작성에 한계는 있어보이지만, 개인적으로는 언젠가 이 한계를 극복할 것이라고 생각합니다.
다만, GPT가 잘할거라고 해서 우리가 공부를 게을리 해도 된다는 것은 아닙니다. 오히려 이전보다 더 깊이있게 공부할 필요가 있다고 생각합니다. 우리가 잘 알아야 잘 시킬 수 있으니까요.. 언젠가는 시킬 일 조차 없게 될지도 모르지만, 그때는 그때고요. 오늘은 노력해야 하지 않을까요?
주식 분석 시스템을 만들기 위해, "종목코드"에 대한 물리명(Column Name)을 고민중입니다.
지금까지는 단순하게 Stock Code 개념으로 STK_CD를 사용해왔는데요,
글로벌하게는 Ticker 또는 Symbol이란 용어가 더 많이 사용되니까요.
글로벌한 시스템을 고려한다면 물리명을 다시 한번 고려해야겠단 생각이 들었습니다.
그래서!, GPT에게 물어봤죠.
단순한 랜덤 결정이 아닌, GPT가 데이터 아키텍쳐나 데이터 모델러 관점으로 결정할 수 있게,
실시간 웹 검색을 유도했고, 논리적으로 결정을 내려주도록 질문을 했습니다.
아래와 같이 Ticker나 Symbol 사용하는 것이 좋을거 같다는 논리를 들어 설명해줍니다.
이처럼 논리를 가지고 어떠한 결정을 하는 것은 데이터 모델러와 데이터 아키텍쳐에게 매우 중요한 부분입니다.
주식 분석 시스템의 데이터베이스에서 사용할 용어로 "Stock Code", "Ticker", "Symbol" 중에서 고르는 것에 대해 고려할 점들을 한글로 정리해드리겠습니다:
주식 기호/티커(Ticker): 주식 기호 또는 티커는 거래 목적으로 증권에 할당된 고유한 문자 시리즈입니다. 트레이더와 투자자가 특정 주식 시장의 특정 주식을 식별하기 위해 사용하는 약어 또는 코드입니다. 예를 들어, 마이크로소프트의 주식 기호는 MSFT이고 애플의 주식 기호는 AAPL입니다. 이 기호는 금융 시장에서 회사의 정체성을 확인하는 디지털 여권 역할을 하며, 거래를 간소화하여 주식 시장을 보다 효율적이고 접근하기 쉽게 만듭니다. 또한 전 세계 금융 시장 참여자들 간의 공통 언어 역할을 하여 국경을 넘는 거래를 원활하게 합니다. "티커"라는 용어는 주식 교환소에서 사용되던 티커 테이프 기계에서 유래되었으며, 이 기계는 실시간 주식 가격 및 거래 정보를 제공했습니다.
주식 코드(Stock Code): 티커 심볼 또는 주식 기호를 나타내기 위해 사용되기도 하지만, "주식 코드"는 이 용어들보다는 구체적이지 않고 국제 사용자나 주식 거래 용어에 익숙한 사람들에게 즉시 알아보기 어려울 수 있습니다. 주식 코드 또는 티커 심볼은 전보를 사용하여 주식 가격을 약어 코드로 전달하는 시스템에서 발전했습니다. 이 시스템은 1960년대까지 사용되었으며, 오늘날 이 코드들은 비즈니스 TV 보고서, 금융 웹사이트 및 기타 매체에 나타나 실시간 주식 시장 정보를 제공하는 중요한 역할을 합니다.
국제 및 지역 변형: 서로 다른 주식 거래소와 지역은 주식 심볼에 대한 자체 명명 규칙과 특정 형식을 가지고 있을 수 있습니다. 예를 들어, 아시아 시장에서는 숫자 기반 주식 심볼이 더 일반적이지만, 미국과 유럽 거래소에서는 문자 기반 심볼이 널리 사용됩니다. 또한, 일부 주식 심볼은 특히 상호 기금과 워런트에 대해 문자와 숫자를 결합할 수도 있습니다.
이 정보를 바탕으로, 주식 분석 시스템에서 "Ticker" 또는 "Symbol"을 사용하는 것이 더 적절할 수 있습니다. 특히 시스템이 글로벌 청중을 대상으로 하거나 주식 거래에 익숙한 사람들을 위한 것이라면, 이 용어들은 금융 세계에서 널리 인식되고 이해됩니다.
GPT가 Ticker와 Symbol 중에 하나를 선택을 못해서, 아무거나 하나 정해서 테이블을 설계해달라고 요청했습니다. 그러자 글로벌 인지도를 고려해 Symbol을 선택한 것을 알 수 있습니다.
기존의 논의를 바탕으로, 주식 분석 시스템을 위한 '종목마스터' 정보를 관리하는 데이터베이스 테이블을 설계할 때, "Ticker" 또는 "Symbol" 중 어느 것을 사용해도 괜찮을 것 같습니다. 두 용어 모두 주식이나 증권을 식별하는 데 널리 사용되고 있으며, 사용자와 시장의 요구에 적합한 용어를 선택하는 것이 중요합니다.
가정해 보자면, "Symbol"이라는 용어를 사용하는 것이 좋을 것 같습니다. "Symbol"은 글로벌한 인식도가 높고, 주식 시장에 익숙한 사용자들에게도 쉽게 이해될 수 있습니다. 이를 바탕으로 종목마스터 테이블을 다음과 같이 설계할 수 있습니다:
종목 심볼 (Symbol): 주식의 고유 식별자. 예: 'AAPL' (애플), 'MSFT' (마이크로소프트).
회사명 (CompanyName): 주식을 발행하는 회사의 이름.
시장 (Market): 주식이 거래되는 시장. 예: 'NASDAQ', 'NYSE'.
산업 분류 (Industry): 회사가 속한 산업 분류.
시가총액 (MarketCap): 회사의 시가총액.
주식 가격 (StockPrice): 현재 주식 가격.
배당률 (DividendYield): 주식의 배당률.
연간 수익 (AnnualRevenue): 회사의 연간 수익.
순이익 (NetIncome): 회사의 순이익.
주식 수 (SharesOutstanding): 시장에 유통되는 주식 수.
이 테이블은 주식 분석을 위한 핵심적인 데이터를 제공하며, 사용자들이 주식 시장에 대한 효율적인 분석 및 의사결정을 할 수 있도록 도와줍니다. 데이터베이스 설계는 시스템의 목적과 사용자의 요구에 따라 변경될 수 있으며, 이는 단지 기본적인 구조를 제시한 것입니다.
그런데, 설계해준 내용을 보면, 종목마스터라고 하기에는 마스터 성이 아닌 정보(주기적으로 변할 수 있는 정보)가 섞여 있습니다.(물론 최종 개념으로 마스터에 넣어줘도 무방한 정보이긴 합니다.)
어쨋든, 주기적으로 변하는 정보는 분리해서 MySQL용으로 DDL을 부탁했더니 아래와 같이 만들어주네요.
CREATE TABLE StockMaster (
Symbol VARCHAR(10) PRIMARY KEY,
CompanyName VARCHAR(100),
Market VARCHAR(50),
Industry VARCHAR(100)
);
주어진 내용에 대한 댓글을 생성하기 위해 OpenAI의 GPT 모델을 호출하는 함수입니다.
이 함수는 모델을 지정하고, 사용자의 메시지를 바탕으로 챗봇 응답을 생성합니다.
웹 페이지 크롤링
requests와 BeautifulSoup 라이브러리를 사용하여 특정 URL의 HTML 내용을 가져오고 파싱합니다.
이 코드에서는 단순하게 티스토리의 특정 페이지만 사용했습니다.
사용하는 블로그에 따라 쉽게 크롤링이 안되거나, 파싱이 안되므로 그에 맞게 코드를 변경해야 합니다.
코드는 다음과 같습니다.
# GPT를 사용한 댓글 봇 만들기.
from openai import OpenAI
import requests
import traceback
from bs4 import BeautifulSoup
client = OpenAI(api_key='각자API키를사용') # API 키 설정
def getReplyFromGPT(content):
try:
# 사용하려는 모델 지정
model = "gpt-4"
# 댓글을 생성할 메시지 설정
prompt = f"이 내용에 공감하는 댓글을 짧은 한 문장의 한글로, 진짜 사람같은 느낌으로 만들어줘: {content}"
# GPT 모델을 사용하여 챗봇 응답 생성
response = client.chat.completions.create(
model=model, # 사용할 모델 지정
messages=[
{"role": "user", "content": prompt}
]
)
res = response.choices[0].message.content
return res
except Exception as e:
print(traceback.format_exc())
# URL 설정
url = 'https://sweetquant.tistory.com/481'
# GET 요청으로 URL에서 데이터 가져오기
response = requests.get(url)
# 응답 상태 확인
if response.status_code == 200:
# HTML 내용 파싱
soup = BeautifulSoup(response.content, 'html.parser')
# 원하는 부분의 내용 추출
# 이 예제에서는 'div' 태그와 'tt_article_useless_p_margin' 클래스를 기준으로 내용을 추출합니다.
# 실제 웹사이트 구조에 따라 이 부분은 변경될 수 있습니다.
main_content = soup.find('div', class_='tt_article_useless_p_margin')
content = main_content.get_text(strip=True)
reply = getReplyFromGPT(content)
print(reply)
else:
print(f"페이지를 가져오는 데 실패했습니다. 상태 코드: {response.status_code}")
그랬더니, 갑자기 영어로.. 어쩌고 합니다. 한글로 설명을 부탁하니, 아래와 같이 설명해줍니다.
제공하신 엑셀 파일에는 2015년부터 2023년까지 각 월별로 한국을 방문한 각국 국민들의 통계 데이터가 포함되어 있습니다. 각 행은 한 달을 나타내며, 열에는 'TOTAL'이라는 총 방문자 수를 비롯하여 중국, 일본, 미국, 대만, 홍콩, 필리핀, 태국 등 다양한 국가별 방문자 수가 포함되어 있습니다. 또한 자이르, 케이맨제도, 미국인근섬과 같이 덜 일반적인 출신지에 대한 기록도 250개의 열에 걸쳐 포함되어 있습니다.
이 데이터를 통해 원하시는 특정 분석이나 요약을 진행해 드릴까요? 예를 들어, 시간에 따른 추세, 국가별 비교, 연간 요약 등이 있을 수 있습니다.
이제, 저는 면세점 직원인데 이 데이터를 이용해 마케팅 분석 보고서를 만들어야 한다고 GPT에게 말했습니다.
아래와 같이 제법 유용한 말들을 해줍니다.
월별 방문자 패턴 분석: 각 월별로 평균 방문자 수를 분석하여 특정 시즌에 방문자 수가 증가하는 경향을 파악합니다. 예를 들어, 여름 휴가철이나 명절 시즌에 방문자 수가 증가하는지 여부를 분석할 수 있습니다.
주요 국가별 방문자 비율 분석: 중국, 일본, 미국 등 주요 국가의 방문자 수를 전체 방문자 수와 비교하여 그 비율을 분석합니다. 이를 통해 주요 고객층이 어느 국가에 집중되어 있는지 파악할 수 있습니다.
국가별 월별 방문자 비교: 주요 국가들을 대상으로 각 월별 방문자 수를 비교합니다. 이를 통해 어떤 국가의 방문자들이 특정 시즌에 많이 방문하는지 파악할 수 있습니다.
이러한 분석은 면세점의 재고 관리 및 마케팅 전략 수립에 유용한 정보를 제공할 수 있습니다.
뭔가 가능한 이야기들인거 같습니다. 그래서 당장 12월 연말에 마케팅을 해야하는 관점으로 분석을 요청했습니다. 아래와 같이 답을 줍니다.
12월 연말 시즌에 대한 분석을 진행한 결과, 다음과 같은 정보를 얻었습니다:
중국: 12월 평균 방문자 수가 가장 많은 국가로, 약 215,139명입니다.
일본: 두 번째로 많은 방문자를 보내는 국가로, 평균 약 185,687명입니다.
미국: 세 번째로 방문자 수가 많으며, 평균 약 50,007명입니다.
대만, 홍콩, 태국: 이 국가들도 각각 평균 40,589명, 33,565명, 32,521명으로 상당한 방문자 수를 기록했습니다.
필리핀, 싱가포르, 말레이시아: 각각 평균 26,942명, 26,936명, 25,960명의 방문자 수를 보입니다.
교포: 평균 19,103명이 방문합니다.
이 데이터를 바탕으로, 연말 마케팅 전략을 수립할 때 중국, 일본, 미국 방문객을 주요 타겟으로 설정하는 것이 좋습니다. 또한 대만, 홍콩, 태국 등의 국가들에 대해서도 특별한 마케팅 활동을 고려할 수 있습니다. 이러한 국가의 방문객들을 위한 맞춤형 프로모션, 특별 할인, 문화적 행사 등을 기획하여 방문객들의 관심을 끌고 매출을 증대시킬 수 있을 것입니다
관련 내용을 시각화를 부탁하니 알아서 시각화도 잘 해줍니다. 그런데, GPT-4에서 데이터 분석을 통한 시각화에서는 한글이 깨지는 문제가 있습니다. 그래서 한글 부분은 영어로 번역해서 표현하도록 처리했습니다. (시각화 했던 내용은 저장되어 있지 않아 보여드릴 수가 없네요.)
계속해서 분석을 하다가, 중국 관광객이 많으니 중국인들 대상으로 마케팅을 해야 할거 같다는 생각이 들었습니다.
그래서, 중국 관광객에 대한 최신 인터넷 기사들을 검색해서, 중국 관광객과 관련 높은 키워드를 뽑아달라고 부탁했습니다.
GPT-4에는 현재 빙을 사용한 인터넷 검색 기능이 있어, 이와 같은 검색이 가능합니다. 아래와 같은 결과를 얻었습니다.
최신 중국 관광객에 대한 인터넷 기사를 검토한 결과, 다음과 같은 주요 키워드가 도출되었습니다:
MZ세대 중심의 개별 여행 선호: 중국 MZ세대(1980년대 초~2000년대 초 출생)는 단체 여행보다 개별 여행을 선호하는 경향이 있습니다.
제주도 방문 증가: 제주도는 중국인 관광객들에게 인기 있는 목적지로, 특히 MZ세대 관광객이 많이 찾고 있습니다. 제주목 관아 등의 문화 명소가 인기를 끌고 있습니다.
SNS 영향력: 중국 관광객들은 여행지를 선택할 때 SNS의 영향을 크게 받습니다. 특히 샤오홍슈(小红书)와 같은 중국 SNS 플랫폼이 중요한 역할을 합니다.
체험 중심 여행 선호: 중국 관광객들은 쇼핑 위주의 단체 관광보다 로컬 체험, 카페투어, 맛집투어 등 체험 중심의 자유 관광을 선호합니다.
모바일 결제 증가: 중국 관광객들 사이에서 모바일 페이를 통한 간편 결제의 사용이 증가하고 있습니다. 이는 소비 패턴의 변화를 반영합니다.
한복 대여 및 문화체험 인기: 중국 관광객들은 한국 전통 문화를 체험하고자 한복 대여 및 문화체험에 관심이 많습니다.
개별 여행지 및 맛집 탐방: 중국 젊은 층은 일정이 짜여진 단체관광보다 개별적으로 특정 여행지나 맛집을 집중적으로 찾는 경향이 있습니다.
이러한 키워드들은 중국 관광객을 대상으로 한 마케팅 전략을 수립하거나 워드클라우드를 만드는 데 유용하게 활용될 수 있습니다.
지금까지 만든 차트와 검색 결과를 통해 스토리 있게 발표문을 만들어 달라고 부탁했습니다. 아래와 같이 잘 만들어 주네요.
슬라이드 1: 인사 및 주제 소개
"안녕하세요, 오늘 저는 최근 중국 관광객의 트렌드 변화와 이에 따른 우리의 마케팅 전략에 대해 발표하겠습니다."
슬라이드 2: 현재 중국 관광객의 통계 및 변화
"먼저, 최근의 통계에 따르면, 중국인 관광객의 수는 올해 약 200만 명에 달할 것으로 예상되며, 이는 큰 취업 유발 효과를 가져올 것입니다. 하지만 코로나19 이전과 비교하면, 여전히 회복이 더디게 진행되고 있습니다."
슬라이드 3: 중국 MZ세대의 여행 선호도 변화
"중요한 변화 중 하나는, 중국 MZ세대가 단체 여행보다 개별 여행을 선호한다는 점입니다. 이들은 제주도 같은 특정 목적지를 찾으며, 샤오홍슈와 같은 SNS를 통해 여행지를 결정합니다."
슬라이드 4: 중국인 관광객의 소비 특성
"중국인 관광객은 체험 중심의 여행을 선호하며, 모바일 결제를 증가시키고 있습니다. 이들은 쇼핑보다는 로컬 체험, 카페투어, 맛집투어 등을 선호합니다."
슬라이드 5: 마케팅 전략 제안
"이러한 트렌드를 바탕으로, 우리는 중국 MZ세대의 개별적인 여행 스타일과 문화 체험에 초점을 맞춘 마케팅 전략을 수립해야 합니다. 예를 들어, 한복 대여 및 문화 체험 서비스를 강화하고, 개별적인 맛집이나 특별한 여행지를 홍보하는 것이 좋습니다."
슬라이드 6: 결론 및 감사 인사
"오늘 제시한 마케팅 전략이 중국 관광객 유치에 크게 기여할 것으로 기대합니다. 감사합니다."
그런데 보고서 내용이, 현재 조사한 내용만 정리되어 있는 느낌입니다.
그래서, 면세점에서 중국 관광객 대상으로 12월 마케팅을 어떻게 해야 할지 전략도 보고서에 추가해달라고 했습니다.
그랬더니, 아래와 같이 마케팅 전략 제안을 해줍니다.
개별 여행객 중심의 상품 구성: 중국 MZ세대가 선호하는 개별적인 여행 경험을 반영하여, 다양한 문화적 체험과 연계된 상품을 제공합니다.
SNS 마케팅 강화: 중국인 관광객이 많이 사용하는 SNS 플랫폼을 활용하여, 면세점의 상품과 프로모션을 홍보합니다.
모바일 결제 옵션 확대: 중국 관광객의 모바일 결제 선호도를 고려하여, 다양한 결제 옵션을 제공합니다.
문화 체험 연계 프로모션: 한복 대여, 맛집 투어 등의 체험과 연계된 프로모션을 진행하여, 면세점 방문을 유도합니다.
현지화된 고객 서비스 제공: 중국어 서비스, 문화적으로 맞춤형 상담 및 안내를 통해 고객 만족도를 높입니다.
빅데이터와 No-SQL 기술의 등장은 우리에게 텍스트, 이미지, 비디오, 음성과 같은 다양한 형태의 데이터를 새로운 방식으로 다루게 했습니다. 이전에는 '데이터'라고 하면 대부분의 사람들이 컬럼과 로우로 구성된 테이블 형태의 '관계형 데이터'를 떠올렸습니다. 하지만 지금은 그 경계가 흐려졌습니다.
No-SQL 기술이 주목받을 때, 많은 이들이 관계형 데이터베이스의 중요성이 줄어들 것이라 예상했지만, 그러지 않았습니다. No-SQL은 그 자체로 중요한 기술이지만, 관계형 데이터베이스를 완전히 대체할 수는 없었습니다. 이는 기업의 업무에 필요한 데이터를 다루기에 관계형 구조의 데이터가 효율이 가장 좋기 때문입니다.
그렇다면, 생성형 AI의 선두 주자인 GPT와 같은 기술의 등장은 어떨까요? GPT는 관계형 데이터베이스의 위치에 어떤 영향을 줄까요? 실제로 GPT는 테이블 형태의 데이터를 저장하거나 관리하는 기능에는 한계가 있습니다. 그러나, GPT는 관계형 데이터의 분석 및 해석에 있어 매우 유용한 도구로 활용할 수 있습니다.
기업들이 오랜 시간 동안 쌓아온 관계형 데이터는 이제는 GPT와 같은 기술을 통해 새로운 가치와 인사이트를 제공할 수 있습니다. 따라서 GPT의 등장은 기업들에게 오랜 시간 동안 축적해온 관계형 데이터를 다시 한번 깊게 다뤄볼 기회를 제공하게 될 것입니다.
간단한 예를 살펴보도록 하죠. 아래는 제가 사용하는 샘플 DB에서 월별 제품카테고리별 판매 현황을 추출한 SQL입니다.
(기업의 관계형 데이터를 추출해냈다고 생각하면 되는 것이죠.)
SELECT DATE_FORMAT(T1.ORD_DT,'%Y%m') Order_YearMonth
,T3.ITM_TP ItemCategory
,COUNT(*) OrderQty
,SUM(T1.ORD_AMT) OrderAmount_KRW
FROM MYTUNDB.T_ORD T1
INNER JOIN MYTUNDB.T_ORD_DET T2 ON (T2.ORD_SEQ = T1.ORD_SEQ)
INNER JOIN MYTUNDB.M_ITM T3 ON (T3.ITM_ID = T2.ITM_ID)
GROUP BY DATE_FORMAT(T1.ORD_DT,'%Y%m')
,T3.ITM_TP
ORDER BY Order_YearMonth, ItemCategory;
위 SQL로 추출한 결과를 아래와 같이 첨부하니, GPT-4를 사용하시는 분들은 똑같이 분석을 해보시기 바랍니다.
위 데이터를 GPT-4를 사용해 분석을 요청해 아래와 같은 결론을 얻었습니다. GPT를 통해 더욱 다양한 분석 기법을 요청할 수도 있습니다. 또한 집요하게 물어볼 수록 우리가 쉽게 얻기 어려운 정보도 얻어낼 수 있습니다.
- 제품 유형별로는 '의류' 카테고리에 더 주력하는 것이 좋을 것 같습니다. - 월별 판매 추이를 보면 1월부터 4월까지, 그리고 8월부터 10월까지 판매가 상승하는 추세를 보이므로 이 기간에 판매 활동을 강화하는 것이 좋습니다. - 계절별로는 여름에 판매가 가장 활발하므로 여름 시즌에 특히 주의를 기울이면 좋을 것 같습니다.
예산 상의 한계로 고가의 분석 도구나 전문 분석가를 고용하지 못했던 기업들에게는 GPT와 같은 AI 도구가 저렴하면서도 효과적인 분석 도구로서 큰 기회가 될 수 있습니다.
더불어, 인간이 놓치기 쉬운 데이터의 패턴이나 정보를 GPT를 통해 발견할 수도 있을 것입니다.
그렇지만, 아쉽게도 한계는 존재합니다. 대량의 데이터 처리는 여전히 제한적이며, 특히 실시간 분석에도 한계가 있습니다. 분석 과정에서 예상치 못한 방향으로 진행될 수도 있어, AI의 분석 결과는 반드시 인간이 검증하고 해석해야 합니다.
마지막으로 강조하고 싶은 것은, 관계형 데이터의 중요성이 여전하다는 것입니다. GPT와 같은 혁신적인 기술의 등장에도 불구하고, 관계형 데이터를 다루는 기초적인 학문인 SQL과 관계형 데이터베이스 기술의 중요성은 여전하므로 지속적인 학습과 연구가 필요하다는 것을 잊지 말아야 합니다.
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');
'트레이드 전략 - 위꼬리가 긴 하루'의 마지막 글입니다. 제목에 비해 별 내용이 없는데라고 생각하실 분들도 있을듯 싶네요. 다양한 방법의 하나로 봐주시면 감사하겠습니다.
앞에서 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('작업 완료')
