* 2024년 5월 5일 수정: 이벤트 테이블과 이벤트 응모 테이블, 상품테이블에 출시일자가 추가되었습니다.
StartUP SQL에서 여러분께 가장 먼저 보여드릴 것은 바로 ERD(Entity Relationship Diagram)입니다. ERD는 데이터베이스의 설계도입니다. SQL을 배우는데 왜 데이터베이스 설계도를 알아야 할까요? 그냥 빠르게 SQL 문법만 알려주면 되는 거 아닌가 하는 생각이 드는 분도 있을 것입니다. SQL은 관계형 데이터를 다루는 언어입니다. 다시 말해, SQL을 배우는 목적의 근본은 관계형 데이터를 다루기 위함이며, 더 나아가서 데이터를 분석하고 활용하기 위함입니다. 데이터를 분석하고 활용하는 단계에 이루기 위해서는 SQL로 활용할 데이터 구조를 충분히 이해하고 있어야 합니다.
SQL을 배우는 과정에서 ERD를 계속해서 들여다보는 습관을 갖다보면, 자연스럽게 ERD와 데이터구조가 머릿속에서 빠르게 매핑이 되며, 보다 쉽게 SQL을 작성하고 배울 수 있게 될 것입니다. SQL 입문자라면, ERD 자체가 생소할 것입니다. 걱정할 필요 없습니다. ERD를 이해하기 위해서, 지금은 아래 내용만 이해하면 됩니다.
각각의 네모 하나 하나가 데이터 집합입니다.
네모의 가장 위에는 데이터 집합의 이름이 표시되어 있습니다.
네모 안에는 해당 데이터 집합이 갖고 있는 정보들이 나열되어 있습니다.
네모를 연결하는 선들이 있는데, 네모 간에 관계가 있다는 뜻으로만 이해하면 됩니다.
논리(Logical)는 우리가 쉽게 이해할 수 있는 언어로 작성된 설계도입니다.
물리(Physical)는 실제 데이터베이스에 구현된 실제 명칭을 사용한 설계도입니다.
위 내용을 토대로 논리 ERD를 읽어보면 다음과 같습니다.
매장, 주문, 회원, 주문상세, 상품과 같은 데이터 집합이 있구나.
매장 데이터집합에는 매장ID, 매장명, 매장면적, 매장운영유형과 같은 정보가 있구나.
주문에는 주문번호, 주문일시, 회원ID, 매장ID와 같은 정보가 있구나.
매장과 주문간에 선이 있는 것을 보니 관계가 있구나.
이런식으로 논리ERD를 한번씩 쭉 읽어 보시기 바랍니다. 그리고 논리ERD의 각각 명칭들이 물리ERD에 표시된 이름으로 데이터베이스에 구현된다는 정도만 알고 넘어가시면 됩니다. 앞으로 SQL을 배우는 과정에서 아래의 ERD와 같은 데이터 구조를 계속해서 보여드릴 것입니다.
1만 시간의 법칙이라고 들어보셨나요? 1만 시간을 투자한다면, 전문가가 될 수 있다는 개념입니다.
2024년을 맞이해, 새로운 기술로 1만 시간을 채워볼수 있을까란 생각이 들어 계산을 좀 해봤습니다.
SQL로요! (여기서는 MySQL을 사용했습니다.) 먼저 아래와 같이 날짜 테이블을 생성합니다.
(C_BAS_DT는 StartUP Tuning 강의에서 튜닝을 위해 소개되는 테이블입니다.)
CREATE TABLE C_BAS_DT
(
BAS_DT DATE NOT NULL
,BAS_YMD VARCHAR(8) NOT NULL
,BAS_YM VARCHAR(6) NOT NULL
,BAS_YW VARCHAR(6) NOT NULL
,BAS_DT_SEQ INT NOT NULL
,PRIMARY KEY(BAS_DT)
);
그다음에, MySQL의 RECURSIVE문을 이용해 기본 데이터를 저장합니다. 아래 SQL을 실행하면 2050년 1월 1일까지의 날짜 데이터가 C_BAS_DT에 저장됩니다.
SET @@cte_max_recursion_depth = 30000;
INSERT INTO C_BAS_DT (BAS_DT, BAS_YMD, BAS_YM, BAS_YW, BAS_DT_SEQ)
WITH RECURSIVE WR01 AS (
SELECT STR_TO_DATE('20000101','%Y%m%d') AS BAS_DT
UNION ALL
SELECT DATE_ADD(BAS_DT, INTERVAL 1 day) BAS_DT
FROM WR01
WHERE BAS_DT < STR_TO_DATE('20500101','%Y%m%d')
)
SELECT BAS_DT BAS_DT
,DATE_FORMAT(BAS_DT, '%Y%m%d') AS BAS_YMD
,DATE_FORMAT(BAS_DT, '%Y%m') AS BAS_YM
,CONCAT(YEAR(BAS_DT), LPAD(WEEK(BAS_DT), 2, '0')) AS BAS_YW
,ROW_NUMBER() OVER (ORDER BY BAS_DT) AS BAS_DT_SEQ
FROM WR01
ORDER BY BAS_DT ASC;
1만 시간의 법칙을 계산하기 위해서는 더 많은 날짜 데이터가 필요합니다. 리커시브를 계속 사용하기 보다는 셀프조인을 사용해 추가 날짜 데이터를 생성합니다.
아래의 SQL을 여러번 실행해 2300년 1월 1일까지의 데이터를 생성합니다. 세 번 정도 실행하면 됩니다.(앞에서 생성된 C_BAS_DT를 다시 사용(셀프조인)해 더 많은 연속된 날짜 데이터를 추가로 생성하는 방법입니다.)
-- INSERT 건수가 0건 될때까지 계속 실행(세번 실행하면 됨)
INSERT INTO C_BAS_DT (BAS_DT, BAS_YMD, BAS_YM, BAS_YW, BAS_DT_SEQ)
SELECT T3.BAS_DT BAS_DT
,DATE_FORMAT(T3.BAS_DT, '%Y%m%d') AS BAS_YMD
,DATE_FORMAT(T3.BAS_DT, '%Y%m') AS BAS_YM
,CONCAT(YEAR(T3.BAS_DT), LPAD(WEEK(BAS_DT), 2, '0')) AS BAS_YW
,ROW_NUMBER() OVER (ORDER BY T3.BAS_DT) + T3.MAX_DT_SEQ AS BAS_DT_SEQ
FROM (
SELECT DATE_ADD(T2.MAX_DT, INTERVAL T1.BAS_DT_SEQ day) BAS_DT
,T2.MAX_DT_SEQ
FROM C_BAS_DT T1
CROSS JOIN (SELECT MAX(X.BAS_DT) MAX_DT , MAX(X.BAS_DT_SEQ) MAX_DT_SEQ FROM C_BAS_DT X) T2
WHERE DATE_ADD(T2.MAX_DT, INTERVAL T1.BAS_DT_SEQ day) <= '23000101'
) T3
;
이제 위에서 만들어진 C_BAS_DT 테이블을 사용해, 평일에만 하루에 30분씩 투자해서 1만 시간을 채우려면 얼마나 걸릴지를, SQL로 구해봅니다. 아래와 같이 SQL을 구현해 볼 수 있습니다.
-- 오늘부터 하루에 30분씩 투자해서 평일(월,화,수,목,금)만 했을때 만시간을 채우려면 몇일이 끝일까?
-- SQL로 구해보자.
SELECT MAX(T2.BAS_DT) 만시간완료일자
,SUM(CASE WHEN T2.HOUR_SUM <= 10000 THEN 1 END) 일수
FROM (
SELECT T1.*
,DAYNAME(T1.BAS_DT) DNM
,0.5 HOUR
,SUM(0.5) OVER(ORDER BY T1.BAS_DT ASC) HOUR_SUM
FROM C_BAS_DT T1
WHERE T1.BAS_DT >= DATE_FORMAT(NOW(),'%Y%m%d')
AND DAYNAME(T1.BAS_DT) IN ('Monday','Tuesday','Wednesday','Thursday','Friday')
ORDER BY T1.BAS_DT ASC
) T2
WHERE T2.HOUR_SUM <= 10000;
만시간완료일자 |일수
---------------+-----+
2100-08-27 |20000|
제가 죽기전에는 하루에 30분씩으로는 1만 시간을 채울수가 없네요!
그렇다면, 지금 자신이 하고 있는 직업으로 1만 시간을 채우려면 얼마나걸리까요.
하루에 보통 8시간 일을 하니, 위 SQL에서 0.5 HOUR만 8 로 변경해주면 됩니다.
SELECT MAX(T2.BAS_DT) 만시간완료일자
,SUM(CASE WHEN T2.HOUR_SUM <= 10000 THEN 1 END) 일수
FROM (
SELECT T1.*
,DAYNAME(T1.BAS_DT) DNM
,8 HOUR
,SUM(8) OVER(ORDER BY T1.BAS_DT ASC) HOUR_SUM
FROM C_BAS_DT T1
WHERE T1.BAS_DT >= DATE_FORMAT(NOW(),'%Y%m%d')
AND DAYNAME(T1.BAS_DT) IN ('Monday','Tuesday','Wednesday','Thursday','Friday')
ORDER BY T1.BAS_DT ASC
) T2
WHERE T2.HOUR_SUM <= 10000;
만시간완료일자 |일수 |
----------+----+
2028-10-13|1250|
자신의 직업을 꾸준히 1250일 정도 한다면 만시간을 채울 수 있네요. 우리가 하고있는 일을 오늘부터 열심히 2028년 10월까지, 하루에 8시간씩만 쏟는다면 1만 시간을 채울 수 있습니다.
이처럼, 1만 시간을 채우기란 매우 어려운 일입니다. 실제 1만 시간을 채우지 않고도 1만 시간을 채운 효과를 보기 위해서는 다른 사람들의 도움을 받고, 다른 사람과 유연하게 협동을 해야 하지 않나란 생각이 듭니다.
2024년에서는 다양한 교육 과정과 협동을 통해 1만 시간을 더욱 단축해보실 수 있기 바랍니다.!
SQL(Structured Query Language)은 선언적 언어입니다. 그러나 SQL 튜닝을 잘하고 싶다면, SQL이 DBMS 내부적으로는 절차형 언어로 처리된다는 생각의 전환이 필요합니다. (SQL을 절차형 언어처럼 작성하라는 뜻이 절대 아닙니다.!!)
선언적 언어는 "어떻게(How)" 가 아닌 "무엇을(What)" 할 것인지, 또는 "무엇이" 필요한지를 정의하는 언어입니다. 다시말해, SQL을 사용할때는 필요한 데이터 집합이 무엇인지 정의만 해주면 됩니다. SQL과 같은 선언적 언어는 데이터를 어떠한 순서, 또는 어떠한 절차로 가져올 것인지를 정의할 필요가 없습니다.
자바, 파이썬, C와 같은 프로그래밍 언어는 절차형 언어입니다. 절차형 언어는 "무엇(What)"과 함께 "어떻게(How)"에 모두 집중해야 합니다. 절차형 언어는 필요한 결과(무엇)를 얻기 위한 절차(어떻게)까지 모두 구현해야 하기 때문입니다. 이와 같은 차이로, 절차형 언어만 다루던 분들이 초반에 SQL을 다루기 어려워하기도 합니다. 하지만, 절차형 언어를 제대로 잘하는 개발자라면, 분명히 "SQL 튜너"의 소질이 있을거라 생각합니다.
SQL은 표면적으로는 선언적 언어입니다. 하지만, DBMS 내부적으로 데이터가 처리되는 과정은 절차적입니다. 예를 들어, 뉴욕에 사는 고객의 최근 한달 주문 건수가 필요하다면, 아래와 같이 SQL로 필요한 데이터 집합을 정의해주면 됩니다.
SELECT COUNT(*) 주문건수
FROM 고객 A INNER JOIN 주문 B ON (A.고객ID = B.고객ID)
WHERE A.도시 = 'NY'
AND B.주문 >= ADD_MONTHS(NOW(),-1M);
위와 같은 SQL을 실행하면, DBMS는 내부적인 절차를 만들어 SQL에 맞는 데이터 집합을 만들어 줍니다. 이때, DBMS는 사용자가 요청한 SQL의 결과를 만들기 위해, 다양한 내부적인 처리 절차를 고려할 수 있습니다. 주문 테이블에서 최근 한달치를 읽은 후에, 고객에 접근해 결합(JOIN)할 수도 있으며, 반대로 도시가 NY인 고객을 먼저 찾은 후에 주문 테이블과 결합 할 수도 있습니다. SQL은 선언적인 언어이기 때문에, 내부적인 절차는 DBMS가 마음대로 정하고 처리하게 됩니다. 어떠한 절차로 처리하든 사용자가 요구한 데이터만 정확히 만들어주면 됩니다. 그러나, DBMS가 마음대로 처리 절차를 정한다기 보다는, SQL의 최종 결과를 얻는데 가장 비용이 적은(성능이 좋은) 방법을 고려해 절차를 정합니다. 그리고 이러한 처리 절차를 만드는 모듈이 그 유명한 옵티마이져(Optimizer)입니다.
이 글에서 SQL 성능 관련해 우리가 주목해야 할 것은, 선언적 언어인 SQL이 DBMS 내부에서는 절차적으로 처리된다는 것입니다. 그리고 이 절차에 따라 SQL의 성능 차이가 천차만별이 될 수 있습니다. SQL의 처리 절차는 실행계획(EXPLAIN)을 통해 확인할 수 있습니다. 그러므로, SQL의 성능에 이상이 있다면 가장 먼저 실행계획을 확인해야 합니다. 좀더 덧붙여 말하자면, 실행계획을 통해 SQL의 내부적인 처리 절차를 확인해야 하는 것입니다. 실행계획을 통해, 비효율적인 절차(처리 순서나 처리 방법)를 발견해 제거할 수 있다면, SQL의 성능은 저절로 좋아지게 될 것입니다.
SQL 튜닝은, 이러한 내부적인 절차를 관찰하고 비효율적인 절차를 찾아 제거하거나 변경하는 것입니다. 다시말해, SQL은 표면적으로는 선언적 언어이지만, SQL 튜닝 관점에서는 절차형 사고 방식으로 내부적인 처리 절차를 확인해야 합니다. 이러한 이해를 바탕으로 SQL 튜닝에 접근하면, SQL 튜닝을 좀 더 잘 할 수 있습니다.
끝으로, SQL을 절차적으로 작성하라는 뜻이 절대 아님을 다시 한 번 강조드립니다. 우리가 집합 개념으로 작성한 SQL이, DBMS 내부에서 어떤 절차로 처리되는지를 알 필요가 있다는 뜻입니다.
주식 분석 시스템을 만들기 위해, "종목코드"에 대한 물리명(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 플랫폼을 활용하여, 면세점의 상품과 프로모션을 홍보합니다.
모바일 결제 옵션 확대: 중국 관광객의 모바일 결제 선호도를 고려하여, 다양한 결제 옵션을 제공합니다.
문화 체험 연계 프로모션: 한복 대여, 맛집 투어 등의 체험과 연계된 프로모션을 진행하여, 면세점 방문을 유도합니다.
현지화된 고객 서비스 제공: 중국어 서비스, 문화적으로 맞춤형 상담 및 안내를 통해 고객 만족도를 높입니다.
StartUP Tuning(PostgreSQL, MySQL을 활용한 SQL 튜닝 입문) 강의에서는,
Booster QUIZ를 통해, SQL 튜닝을 각자 직접 실습해볼 수 있도록 하고 있습니다.
이와 같은 실습과 고민의 시간은 SQL 튜닝 능력 향상에 많은 도움이 됩니다.
오늘은 Booster Quiz 중에 NL 조인 관련 연습문제를 하나 풀어보도록 하겠습니다.
Booster Quiz: 아래 SQL을 NL 조인으로 처리한다고 했을때, 어느 방향으로 NL 처리하는 것이 좋은지 파악하기 위해 아래 질문에 차례대로 답해보세요.
SELECT t1.itm_id
,COUNT(*) CNT
FROM m_itm t1
INNER JOIN t_itm_evl_big t2 ON (t2.itm_id = t1.itm_id)
WHERE t1.itm_tp = 'CLOTHES'
AND t1.unt_prc <= 1000
AND t2.evl_pt = 5
GROUP BY t1.itm_id;
SQL을 보면 m_itm(아이템/품목)과 t_itm_evl_big(아이템평가), 두 개의 테이블이 사용되고 있습니다.
위 SQL에 대한 실행계획과 인덱스를 확인하지 않고 아래 질문에 차례대로 답해봅니다.
1. t1 > t2 방향으로 NL 조인한다면, t2에 방문(loop) 횟수는?
아래와 같이 원래 SQL에서 t1 부분만 추출해 카운트하면 알 수 있습니다.(답: 10번)
SELECT COUNT(*)
FROM m_itm t1
WHERE t1.itm_tp = 'CLOTHES'
AND t1.unt_prc <= 1000;
count|
-----+
10|
2. t2 > t1 방향으로 NL 조인한다면, t1에 방문(loop) 회수는?
아래와 같이 원래 SQL에서 t2 부분만 추출해 카운트하면 알 수 있습니다.(답:25,000번)
SELECT COUNT(*)
FROM t_itm_evl_big t2
WHERE t2.evl_pt = 5;
count|
-----+
25000|
1번과 2번 질문을 통해, 2번보다는 1번 방법이 더 유리할 수 있겠구나 판단할 수 있습니다.
t1>t2 방향으로 NL 조인하면 t2에는 10번만 접근하면 되고, t2>t1 방향으로 NL 조인하면 t1에는 무려 25,000번 반복 접근해야 합니다. 반복적인 접근 횟수를 줄이는 것은 SQL 성능에 도움이 됩니다. (물론 적절한 인덱스 구성이 선행되어야 합니다.)
이어서 다음 질문에도 답해봅니다.
3. 힌트를 사용해 t1 > t2 방향으로 NL 조인되도록 처리하고, 실제 실행계획을 확인
아래와 같이 힌트를 적용해 실행계획을 확인해보면, t2의 loops가 10인 것을 알 수 있습니다.
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
/*+ Leading((t1 t2)) NestLoop(t1 t2) */
SELECT t1.itm_id
,COUNT(*) CNT
FROM m_itm t1
INNER JOIN t_itm_evl_big t2 ON (t2.itm_id = t1.itm_id)
WHERE t1.itm_tp = 'CLOTHES'
AND t1.unt_prc <= 1000
AND t2.evl_pt = 5
GROUP BY t1.itm_id;
GroupAggregate (actual time=9.148..10.760 rows=2 loops=1)
Group Key: t1.itm_id
Buffers: shared hit=296
-> Nested Loop (actual time=0.035..10.411 rows=3500 loops=1)
Buffers: shared hit=296
-> Index Scan using m_itm_pk on m_itm t1 (actual time=0.012..0.035 rows=10 loops=1)
Filter: ((unt_prc <= '1000'::numeric) AND ((itm_tp)::text = 'CLOTHES'::text))
Rows Removed by Filter: 90
Buffers: shared hit=2
-> Index Scan using t_itm_evl_big_pk on t_itm_evl_big t2 (actual time=0.807..1.004 rows=350 loops=10)
Index Cond: ((itm_id)::text = (t1.itm_id)::text)
Filter: (evl_pt = '5'::numeric)
Rows Removed by Filter: 1550
Buffers: shared hit=294
Planning Time: 0.179 ms
Execution Time: 10.792 ms
4. 힌트를 사용해 t2 > t1 방향으로 NL 조인되도록 처리하고, 실제 실행계획을 확인
아래와 같이 힌트를 적용해 실행계획을 확인해보면, t1의 실제 loops는 16번만 발생했습니다. 하지만, NL 조인의 후행 집합쪽을 보면, t1의 데이터를 Memoize 처리했고, Memoiz에 25,000 번의 반복 접근이 발생했습니다.
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
/*+ Leading((t2 t1)) NestLoop(t1 t2) */
SELECT t1.itm_id
,COUNT(*) CNT
FROM m_itm t1
INNER JOIN t_itm_evl_big t2 ON (t2.itm_id = t1.itm_id)
WHERE t1.itm_tp = 'CLOTHES'
AND t1.unt_prc <= 1000
AND t2.evl_pt = 5
GROUP BY t1.itm_id;
HashAggregate (actual time=30.695..30.697 rows=2 loops=1)
Group Key: t1.itm_id
Batches: 1 Memory Usage: 24kB
Buffers: shared hit=1694
-> Nested Loop (actual time=0.028..30.145 rows=3500 loops=1)
Buffers: shared hit=1694
-> Seq Scan on t_itm_evl_big t2 (actual time=0.010..22.777 rows=25000 loops=1)
Filter: (evl_pt = '5'::numeric)
Rows Removed by Filter: 147500
Buffers: shared hit=1662
-> Memoize (actual time=0.000..0.000 rows=0 loops=25000)
Cache Key: t2.itm_id
Cache Mode: logical
Hits: 24984 Misses: 16 Evictions: 0 Overflows: 0 Memory Usage: 2kB
Buffers: shared hit=32
-> Index Scan using m_itm_pk on m_itm t1 (actual time=0.004..0.004 rows=0 loops=16)
Index Cond: ((itm_id)::text = (t2.itm_id)::text)
Filter: ((unt_prc <= '1000'::numeric) AND ((itm_tp)::text = 'CLOTHES'::text))
Rows Removed by Filter: 1
Buffers: shared hit=32
Planning Time: 0.186 ms
Execution Time: 30.736 ms
지금까지의 내용을 종합해, NL 조인 처리 방향에 따른 성능 차이를 아래와 같이 정리할 수 있습니다. 정리를 통해 현재 SQL에서는 어떤 방향으로 NL 조인이 적합한가를 판단할 수 있습니다.
t1 > t2
후행(t2)에 접근횟수: 10번
t1 > t2 IO(Buffers) : 296
t1 > t2 Execution time : 10.792 ms
t2 > t1
후행(t1)에 접근횟수: 실제 테이블에는 16번 접근, 하지만 memoize(cache) 처리된 데이터를 25,000번 접근.
IO(Buffers): 1694
Execution time : 30.736 ms
물론, 지금까지 살펴본 내용에 인덱스 구성을 추가하거나 변경한다면 완전히 다른 결과가 나올 수 있습니다.
여기서 중요한건, 이와 같은 순차적인 질문과 답으로 NL 조인의 방향에 따라 어떤 부분에서 성능 변화가 발생할 수 있는지 확인해보는 과정입니다.
