일정 날짜 동안 주가가 N% 이상 오른 종목 중 특히 연속으로 오른 종목을 검색할 것이다.
stock 테이블에서 2021년2월17일부터 2021년2월24일까지의 일별 주식 데이터를 비교해 주가가 10% 이상 오른 종목 중 해당 기간 동안 한번도 주가가 떨어지지 않은 종목만 검색한다.
01. 증감 주가와 증감 주기율 저장하기
첫 날짜와 마지막 날짜의 종가를 검색해 증감 주기와 증감 주기율의 데이터셋을 만든다.
같은 symbol 데이터를 비교하기 위해 FROM 문의 서브 쿼리를 사용해 2021년2월17일의 데이터셋을 만들고, 2021년2월24일의 데이터셋을 만든 다음 조인 조건으로 symbol 열을 비교한다. 검색 결과는 임시 테이블 #temp에 저장한다.
SELECT -- 최종적으로 보고 싶은 컬럼을 선택
a.symbol,
a.[close] AS a_close,
b.[close] AS b_close,
b.[close] - a.[close] AS diff_price,
(b.[close] - a.[close])/ b.[close] * 100 AS diff_ratio
INTO #temp
FROM (
SELECT
symbol,
[close]
FROM stock
WHERE date = '2021-02-17'
) AS a
INNER JOIN (
SELECT
symbol,
[close]
FROM stock
WHERE date = '2021-02-24'
) AS b ON a.symbol = b.symbol
--#temp 데이터 검색
SELECT * FROM #temp
결과
symbol a_close b_close diff_price diff_ratio
AGM 84 85.120002746582 1.12000274658203 1.31579265794491
ADTX 4.07000017166138 3.3199999332428 -0.750000238418579 -22.5903690813035
ALDX 13.1999998092651 12.2200002670288 -0.979999542236328 -8.01963601326996
AMER 27.9200000762939 28.7999992370605 0.879999160766602 3.0555527224952
02. 주가 증가율이 10% 이상인 일별 데이터 저장하기
diff_ratio >= 10인 일별 데이터셋을 만들기 위해 #temp와 stock을 조인한다.
이때 ROW_NUMBER 함수를 사용해 symbol 단위로 파티션을 생성하고 날짜 순서대로 순위를 부여한다.
순위를 부여하는 이유는 전일 날짜를 비교할 때 주식 시장이 열리지 않는 주말이나 공휴일이 기간에 포함될 경우 공백이 생기므로 -1일 방식으로 비교할 수 없기 때문이다. 생성한 데이터는 #temp2에 저장한다.
SELECT
ROW_NUMBER() OVER (PARTITION BY a.symbol ORDER BY date ASC) As num,
a.symbol,
b.date,
b.[close]
INTO #temp2
FROM #temp AS a
INNER JOIN stock as b ON a.symbol = b.symbol
WHERE a.diff_ratio >= 10
AND b.date >='2021-02-17'
AND b.date <='2021-02-24'
SELECT * FROM #temp2
결과
num symbol date close
1 AA 2021-02-17 00:00:00.000 21.9300003051758
2 AA 2021-02-18 00:00:00.000 21.1200008392334
3 AA 2021-02-19 00:00:00.000 23.1299991607666
4 AA 2021-02-22 00:00:00.000 24.7999992370605
5 AA 2021-02-23 00:00:00.000 24.8999996185303
6 AA 2021-02-24 00:00:00.000 27.2000007629395
03. SELF JOIN을 사용해 순위 데이터 비교하기
#temp2를 SELF JOIN해서 현재 순위보다 1만큼 높은 데이터를 비교한다. 생성한 데이터는 #temp3에 저장한다.
SELECT
b.symbol,
a.[date] AS a_date,
a.[close] AS a_close,
b.[date] AS b_date,
b.[close] AS b_close,
b.[close] - a.[close] AS diff_price,
(b.[close] - a.[close])/ a.[close] * 100 AS diff_ratio
INTO #temp3
FROM #temp2 AS a
INNER JOIN #temp2 AS b ON a.symbol = b.symbol AND a.num = b.num-1
ORDER BY b.symbol, b.date
SELECT * FROM #temp3
결과
symbol a_date a_close b_date b_close
AA 2021-02-17 00:00:00.000 21.9300003051758 2021-02-18 00:00:00.000 21.1200008392334
AA 2021-02-18 00:00:00.000 21.1200008392334 2021-02-19 00:00:00.000 23.1299991607666
AA 2021-02-19 00:00:00.000 23.1299991607666 2021-02-22 00:00:00.000 24.7999992370605
AA 2021-02-22 00:00:00.000 24.7999992370605 2021-02-23 00:00:00.000 24.8999996185303
AA 2021-02-23 00:00:00.000 24.8999996185303 2021-02-24 00:00:00.000 27.2000007629395
diff_price diff_ratio
-0.809999465942383 -3.6935679647538
2.0099983215332 9.51703712908641
1.67000007629395 7.22006111926982
0.100000381469727 0.403227357040755
2.30000114440918 9.23695252869621
04. 주가 하락이 한 번도 없는 종목 검색하기
#temp3에서 전일과 비교해 한 번이라도 주가가 하락한 종목이 있으면 제외한 테이블을 #temp4에 저장한다.
WHERE 문에 NOT IN을 사용해 하락한 symbol이 있는지 검사한 다음 조건에 포함되지 않는 항목한 검색한다.
SELECT
symbol,
a_date,
round(a_close, 2) AS a_close,
b_date,
round(b_close, 2) AS b_close,
round(diff_close, 2) As diff_price,
round(diff_ratio, 2) AS diff_ratio
INTO #temp4
FROM #temp3
WHERE symbol NOT IN (SELECT symbol FROM #temp3 WHERE diff_ratio <0 GROUP BY symbol)
SELECT * FROM #temp4
결과
symbol a_date a_close b_date b_close diff_price diff_ratio
AAT 2021-02-17 00:00:00.000 29.2 2021-02-18 00:00:00.000 29.26 0.06 0.21
AAT 2021-02-18 00:00:00.000 29.26 2021-02-19 00:00:00.000 29.3 0.04 0.14
AAT 2021-02-19 00:00:00.000 29.3 2021-02-22 00:00:00.000 30.45 1.15 3.92
AAT 2021-02-22 00:00:00.000 30.45 2021-02-23 00:00:00.000 31.62 1.17 3.84
05. 모든 임시 테이블과 나스닥 기업 테이블을 조인해 최종 데이터 생성하기
SELECT
a.symbol,
d.company_name,
d.industry,
ROUND(a.a_close, 2) AS a_close,
ROUND(a.b_close, 2) AS b_close,
ROUND(a.diff_close, 2) AS diff_price,
ROUND(a.diff_ratio, 2) AS diff_ratio
FROM #temp AS a
INNER JOIN (SELECT symbol FROM #temp2 GROUP BY symbol) As b ON a.symbol = b.symbol
INNER JOIN (SELECT symbol FROM #temp4 GROUP BY symbol) As c ON a.symbol = c.symbol
INNER JOIN nasdaq_company AS d ON a.symbol = d.symbol
ORDER BY diff_ratio DESC
결과
symbol company_name industry a_close b_close diff_price diff_ratio
AMPG Amplitech Group Inc. Common Stock Industrial Machinery/Components 6.61 9.25 2.64 28.54
BLBD Blue Bird Corporation Common Stock Construction/Ag Equipment/Trucks 19.48 26.55 7.07 26.63
CAR Avis Budget Group Inc. Common Stock Rental/Leasing Companies 42.82 55.9 13.08 23.4
TRIP TripAdvisor Inc. Common Stock 38.05 49.56 11.51 23.22
최종적으로 얻은 데이터는 주가가 10% 이상 오르고(#temp2) 하루도 빠지지 않고 상승한 종목들(#temp4)의 symbol, 기업 정보, 시작 날짜의 주가, 마지막 날짜의 주가, 증감액, 증가율을 보여준다.
06. 모든 임시 테이블 삭제하기
DROP TABLE #temp
DROP TABLE #temp2
DROP TABLE #temp3
DROP TABLE #temp4
'IT Story > SQL Story' 카테고리의 다른 글
| 보유 주식 수익 증감액 구하기 (0) | 2022.12.08 |
|---|---|
| 특정 기간 주가 하락했다가 회복한 종목의 증감률 구하기 (0) | 2022.12.08 |
| 전일 대비 종목 분석하기 (0) | 2022.12.06 |
| 하루 동안 상승/하락 종목 분석하기 (0) | 2022.12.06 |
| 주가 분석하기 (0) | 2022.12.06 |
