01. 상승/하락 종목 관련 다양한 정보 검색하기
stock 테이블에서 하루 시작가와 하루 종가를 비교해 상승한 금액, 비율과 하루 거래 중 최저 거래가와 최대 거래가의 차이를 구하는 쿼리이다.
SELECT
date,
symbol,
CONVERT(DECIMAL(18,2), [open]) AS [open],
CONVERT(DECIMAL(18,2), [close]) AS [close],
CONVERT(DECIMAL(18,2), [open]-[close]) AS diff_price,
CONVERT(DECIMAL(18,2), ([close]-[open])/[open] * 100) AS diff_ratio,
'' AS ' ---', --공란
CONVERT(DECIMAL(18,2), [low]) AS [low],
CONVERT(DECIMAL(18,2), [high]) AS [high],
CONVERT(DECIMAL(18,2), [high]-[low])) AS diff_high_price,
CONVERT(DECIMAL(18,2), ([high]-[close])/[low] * 100) AS diff_high_ratio
FROM stock
WHERE date = '2021-10-06'
결과
date symbol open close diff_price diff_ratio --- low high diff_high_price diff_high_ratio
2021-10-06 00:00:00.000 HIPO 4.42 4.46 -0.04 0.90 4.31 4.49 0.18 0.70
2021-10-06 00:00:00.000 GLBS 2.77 2.83 -0.06 2.17 2.75 2.85 0.10 0.73
2021-10-06 00:00:00.000 JFU 1.69 1.66 0.03 -1.78 1.61 1.69 0.08 1.86
2021-10-06 00:00:00.000 PINS 49.15 50.36 -1.21 2.46 49.01 50.92 1.91 1.14
02. 가격이 10% 이상 오른 종목을 내림차순으로 정리하기
SELECT
date,
symbol,
CONVERT(DECIMAL(18,2), [open]) AS [open],
CONVERT(DECIMAL(18,2), [close]) AS [close],
CONVERT(DECIMAL(18,2), [open]-[close]) AS diff_price,
CONVERT(DECIMAL(18,2), ([close]-[open])/[open] * 100) AS diff_ratio,
'' AS ' ---', --공란
CONVERT(DECIMAL(18,2), [low]) AS [low],
CONVERT(DECIMAL(18,2), [high]) AS [high],
CONVERT(DECIMAL(18,2), [high]-[low]) AS diff_high_price,
CONVERT(DECIMAL(18,2), ([high]-[low])/[low] * 100) AS diff_high_ratio
FROM stock
WHERE date = '2021-10-06'
AND CONVERT(DECIMAL(18,2), ([close]-[open])/[open]*100) >= 10
ORDER BY CONVERT(DECIMAL(18,2), ([close]-[open])/[open]*100) DESC
결과
date symbol open close diff_price diff_ratio ---low high diff_high_price diff_high_ratio
2021-10-06 00:00:00.000 GROM 3.27 4.98 -1.71 52.29 3.23 5.90 2.67 82.66
2021-10-06 00:00:00.000 FAMI 0.31 0.39 -0.08 25.81 0.30 0.42 0.12 40.00
2021-10-06 00:00:00.000 NXTP 1.36 1.68 -0.32 23.53 1.31 1.77 0.46 35.11
2021-10-06 00:00:00.000 AFRM 109.78 133.70 -23.92 21.79 109.20 138.88 29.68 27.18
2021-10-06 00:00:00.000 SDC 5.70 6.75 -1.05 18.42 5.63 6.76 1.13 20.07
symbol이 GROM인 주식이 82.66%로 가장 높은 상승률을 기록했으며, 상승률 기준으로 내림차순 정렬되어 마지막 결과는 10%에 가장 근접해 상승한 ENSC 종목으로 나타났다.
03. 가격이 오른 종목의 상세 정보 검색하기
nasdaq_company 테이블을 조인해 기업 이름(company_name)과 산업군(sector), 산업 종류(industry)를 함께 검색하는 쿼리다.
SELECT
date,
b.symbol,
b.company_name,
b.sector,
b.industry,
CONVERT(DECIMAL(18,2), a.[open]) AS [open],
CONVERT(DECIMAL(18,2), a.[close]) AS [close],
CONVERT(DECIMAL(18,2), a.[open]-a.[close]) AS diff_price,
CONVERT(DECIMAL(18,2), (a.[close]-a.[open])/a.[open] * 100) AS diff_ratio,
'' AS ' ---', --공란
CONVERT(DECIMAL(18,2), a.[low]) AS [low],
CONVERT(DECIMAL(18,2), a.[high]) AS [high],
CONVERT(DECIMAL(18,2), a.[high]-a.[low]) AS diff_high_price,
CONVERT(DECIMAL(18,2), (a.[high]-a.[low])/a.[low] * 100) AS diff_high_ratio
FROM stock AS a
INNER JOIN nasdaq_company AS b ON a.symbol = b.symbol
WHERE date = '2021-10-06'
AND CONVERT(DECIMAL(18,2), (a.[close]-a.[open])/a.[open]*100) >= 10
ORDER BY CONVERT(DECIMAL(18,2), (a.[close]-a.[open])/a.[open]*100) DESC
결과
date symbol company_name sector industry ... diff_high_ratio
2021-10-06 GROM Grom Social Enterprises Inc. Common Stock 82.66
2021-10-06 FAMI Farmmi Inc. Ordinary Shares Consumer Non-Durables Packaged Foods 40.00
2021-10-06 NXTP NextPlay Technologies Inc. Common Stock Technology EDP Services 35.11
2021-10-06 AFRM Affirm Holdings Inc. Class A Common Stock Technology EDP Services 27.18
2021-10-06 SDC SmileDirectClub Inc. Class A Common Stock Health Care Medical/Dental Instruments 20.07
stock 테이블을 a로, nasdaq_company 테이블을 b로 별칭을 붙였다. symbol 열이 어디에 속하는 지 구분하기 위해서다.
'IT Story > SQL Story' 카테고리의 다른 글
| 주가가 연속 상승한 종목 분석하기 (0) | 2022.12.07 |
|---|---|
| 전일 대비 종목 분석하기 (0) | 2022.12.06 |
| 주가 분석하기 (0) | 2022.12.06 |
| SQL 함수: 분석 함수 (0) | 2022.12.06 |
| SQL 함수: 순위 함수 (0) | 2022.12.06 |