2020년 3월 코로나로 인해 전 세계 주식 시장이 매우 큰 폭으로 하락했다. 코로나가 발생하기 전 1주일 평균 시장 가격과 코로나 도중 시장 가격, 현재 시장 가격을 비교해 보면 얼마나 떨어졌다가 얼마나 회복되었을까?
01. 기준 날짜 구간의 데이터 저장하기
기준 날짜 구간의 데이터를 #temp1에 저장한다. symbol 별로 구간별 평균가(AVG)를 계산해 저장하며 GROUP BY에 symbol 을 사용한다.
SELECT
symbol,
AVG([close]) AS [close]
INTO #temp1
FROM stock
WHERE date >= '2021-05-31' AND date <= '2021-06-04'
GROUP BY symbol
SELECT * FROM #temp1
결과
symbol close
^CMC200 999.178482055664
^DJI 34627.279296875
^FTSE 7080.47497558594
^GSPC 4208.22509765625
02. 주가 하락 종목을 비교/검색할 날짜 구간 데이터 저장하기
날짜 구간의 데이터를 검색한 결과를 #temp2에 저장한다.
SELECT
symbol,
AVG([close]) AS [close]
INTO #temp2
FROM stock
WHERE date >= '2021-06-28' AND date < '2021-07-02'
GROUP BY symbol
SELECT * FROM #temp2
결과
symbol close
^CMC200 853.697250366211
^DJI 34427.900390625
^FTSE 7080.82507324219
^GSPC 4299.96240234375
03. 주가 회복 종목을 비교/검색할 날짜 구간 데이터 저장하기
날짜 구간의 데이터를 검색한 결과를 #temp3에 저장한다.
SELECT
symbol,
AVG([close]) AS [close]
INTO #temp3
FROM stock
WHERE date >= '2021-07-05' AND date < '2021-07-09'
GROUP BY symbol
SELECT * FROM #temp3
결과
symbol close
^CMC200 846.916341145833
^DJI 34560.36328125
^FTSE 7111.875
^GSPC 4340.82991536458
04. 3개의 임시 테이블을 결합한 기초 데이터 생성하기
INNER JOIN을 이용해 #temp1, #temp2, #temp3의 데이터셋을 만들어 #result에 저장한다.
SELECT
a.symbol,
a.[close] AS a_close,
b.[close] As b_close,
( b.[close] - a.[close])/ a.[close] * 100 As b_a_ratio,
c.[close] As c_close,
( c.[close] - a.[close])/ a.[close] * 100 As c_a_ratio,
( c.[close] - b.[close])/ a.[close] * 100 As c_b_ratio
INTO #result
FROM #temp1 AS a
INNER JOIN #temp2 AS b ON a.symbol = b.symbol
INNER JOIN #temp3 AS c ON b.symbol = c.symbol
SELECT * FROM #result
결과
symbol a_close b_colse b_a_ratio c_colse c_a_ratio c_b_ratio
^CMC200 999.1785 853.6973 -14.5601 846.9163 -15.2387 -0.67865
^DJI 34627.28 34427.9 -0.57579 34560.36 -0.19325 0.382539
^FTSE 7080.475 7080.825 0.004945 7111.875 0.443473 0.438529
^GSPC 4208.225 4299.962 2.179952 4340.83 3.151087 0.971134
05. 기준 날짜 대비 조건에 맞는 종목 검색하기
#result에서 #temp1 평균가 대비 #temp2 평균가가 -20% 이상 하락한 종목 중에 #temp1의 평균가 대비 #temp3 회복 가격이 -20%~-10%인 종목을 검색한다. 상장 폐지된 symbol은 제외. nasdaq_company와 조인해 company_name, sector, industry 정보를 함께 검색한다. #temp1, #temp2 하락률이 큰 순서의 오름차순과 #temp1,#temp3의 회복률 오름차순으로 정렬한다.
SELECT
b.symbol,
b.comanay_name
b.sector,
b.industry.
round(a.a_close, 2) As a_close,
round(a.b_close, 2) As b_close,
round(a.c_close, 2) As c_close,
round(a.b_a_ratio, 2) As b_a_ratio,
round(a.c_a_ratio, 2) As c_a_ratio,
round(a.c_b_ratio, 2) As c_b_ratio
FROM #reulst AS a
INNER JOIN nasdaq_company AS b ON a.symbol = b.symbol
WHERE a.b_a_ratio <= -20
AND a.c_a_ratio >= -20
AND a.c_a_ratio <= -10
AND b.is_deleted is NULL -- 상장 폐지된 회사는 제외
ORDER BY b_a_ratio, c_a_ratio,
결과
symbol company_name sector industry a_close b_close c_close b_a_ratio c_a_ratio c_b_ratio
ONCS Health Care 3.86 2.95 3.13 -23.41 -18.89 4.52
ARL Consumer Services 12.31 9.62 10.35 -21.9 -15.93 5.98
NG 10.26 8.02 8.34 -21.77 -18.71 3.06
LINK Capital Goods 10.96 8.62 8.97 -21.37 -18.13 3.24
AU Basic Industries 23.4 18.53 19.35 -20.82 -17.32 3.5
RFP Basic Industries 14.88 11.86 12.06 -20.31 -18.94 1.37
06. 모든 임시 테이블 삭제하기
DROP TABLE #temp1
DROP TABLE #temp2
DROP TABLE #temp3
DROP TABLE #result
'IT Story > SQL Story' 카테고리의 다른 글
| 5일, 20일 가격 이동 평균 구하기 (1) | 2022.12.08 |
|---|---|
| 보유 주식 수익 증감액 구하기 (0) | 2022.12.08 |
| 주가가 연속 상승한 종목 분석하기 (0) | 2022.12.07 |
| 전일 대비 종목 분석하기 (0) | 2022.12.06 |
| 하루 동안 상승/하락 종목 분석하기 (0) | 2022.12.06 |
