01. 최고가, 최저가, 가격 차이, 비율 검색하기
SELECT
symbol,
CONVERT(DECIMAL(18,2), MIN([close])) AS w52_min,
CONVERT(DECIMAL(18,2), MAX([close])) AS w52_max,
CONVERT(DECIMAL(18,2), MAX([close])) - MIN([close]) AS w52_diff_price,
CONVERT(DECIMAL(18,2), (MAX([close])) - MIN([close]))/ MIN([close]) * 100 AS w52_diff_ratio
FROM stock
WHERE date > = DATEADD(week, -52, '2021-10-04')
AND date < = '2021-10-04'
GROUP BY symbol
결과
symbol w52_min w52_max w52_diff_price w52_diff_ratio
ACTCW 1.15 8.82 7.67000002384186 666.956535566507
FIIIU 10.00 15.43 5.43 54.3
ISR 0.36 2.47 2.10999998569489 586.111087821149
LINC 4.53 8.08 3.54999979019165 78.3664422865488
CCNEP 25.10 28.86 3.75999961853027 14.9800794536077
PSO 6.55 12.33 5.77999980926514 88.2442722395024
SII 28.29 46.56 18.2699990844727 64.5811219821215
2021년10월4일부터 과거 52주 데이터를 검색하려고 WHERE문에 DATEADD 함수를 사용했다.
날짜 계산은 주 단위로 하므로 DATEADD 함수의 첫번째 인자는 week 를 전달했다.
SELECT 문의 symbol에 따른 최솟값과 최댓값을 구하려고 MIN, MAX 함수를 사용했다.
또한 검색 결과에서 주가의 자릿수는 18자리까지 표시하되, 소수점은 2자리까지만 표시하게 했다(DECIMAL(18,2))
GROUP BY 문에 symbo 열을 지정했다.
02. 쿼리 간결하게 만들기
FROM 문에 서브 쿼리를 사용해 52주 데이터의 symbol 별 최저가와 최고가를 먼저 구하고, 그 값을 서브 쿼리 밖의 SELECT 문에서 재사용하는 방식으로 간결하게 만든다.
SELECT
X.symbol,
w52_min,
w52_max,
w52_max - w52_min As w52_diff_price,
(w52_max - w52_min)/ w52_min * 100 AS w52_diff_ratio
FROM (
SELECT
symbol,
CONVERT(DECIMAL(18,2), MIN([close])) AS w52_min,
CONVERT(DECIMAL(18,2), MAX([close])) AS w52_max
FROM stock
WHERE date > = DATEADD(week, -52, '2021-10-04')
AND date < = '2021-10-04'
GROUP BY symbol
) AS X
결과
메시지 8134, 수준 16, 상태 1, 줄 1392
0으로 나누기 오류가 발생했습니다.
분모로 사용한 w52_min이 0인 경우 발생한 에러다.
03. 에러 수정
CASE 함수를 사용해 분모 w52_min가 0보다 클 경우에만 나누기 계산을 하고, 0인 경우에는 수행하지 않고 결과를 그냥 0으로 출력하도록 수정한다.
SELECT
X.symbol,
w52_min,
w52_max,
w52_max - w52_min As w52_diff_price,
CONVERT(DECIMAL(18,2), CASE WHEN w52_min > 0 THEN (w52_max - w52_min)/ w52_min * 100 ELSE 0 END) AS w52_diff_ratio
FROM (
SELECT
symbol,
CONVERT(DECIMAL(18,2), MIN([close])) AS w52_min,
CONVERT(DECIMAL(18,2), MAX([close])) AS w52_max
FROM stock
WHERE date > = DATEADD(week, -52, '2021-10-04')
AND date < = '2021-10-04'
GROUP BY symbol
) AS X
결과
symbol w52_min w52_max w52_diff_price w52_diff_ratio
ACTCW 1.15 8.82 7.67 666.96
FIIIU 10.00 15.43 5.43 54.30
ISR 0.36 2.47 2.11 586.11
LINC 4.53 8.08 3.55 78.37
CCNEP 25.10 28.86 3.76 14.98
PSO 6.55 12.33 5.78 88.24
SII 28.29 46.56 18.27 64.58
처음에 작성한 쿼리는 왜 에러가 나지 않았나?
04. w52_min이 0인 데이터 검색하기
MIN 함수가 반환한 값이 0인 symbol이 무엇인지 확인해보자.
WHERE 문에서 w52_min = 0을 추가한다.
SELECT
X.symbol,
w52_min,
w52_max,
w52_max - w52_min As w52_diff_price,
CONVERT(DECIMAL(18,2), CASE WHEN w52_min > 0 THEN (w52_max - w52_min)/ w52_min * 100 ELSE 0 END) AS w52_diff_ratio
FROM (
SELECT
symbol,
CONVERT(DECIMAL(18,2), MIN([close])) AS w52_min,
CONVERT(DECIMAL(18,2), MAX([close])) AS w52_max
FROM stock
WHERE date > = DATEADD(week, -52, '2021-10-04')
AND date < = '2021-10-04'
GROUP BY symbol
) AS X
WHERE w52_min = 0
결과
symbol w52_min w52_max w52_diff_price w52_diff_ratio
SPIR 0.00 0.02 0.02 0.00
05. 가공하지 않은 w52_min 살펴보기
symbol SPIR 인 w52_min는 0이다. 가공하지 않은 w52_min을 살펴보자.
SELECT
symbol,
CONVERT(DECIMAL(18,2), MIN([close])) AS w52_min,
MIN([close]) As w52_min_origin,
CONVERT(DECIMAL(18,2), MAX([close])) AS w52_max,
CONVERT(DECIMAL(18,2), MAX([close])) - MIN([close]) AS w52_diff_price,
CONVERT(DECIMAL(18,2), (MAX([close])) - MIN([close]))/ MIN([close]) * 100 AS w52_diff_ratio
FROM stock
WHERE date > = DATEADD(week, -52, '2021-10-04')
AND date < = '2021-10-04'
AND symbol = 'SPIR'
GROUP BY symbol
결과
symbol w52_min w52_min_origin w52_max w52_diff_price w52_diff_ratio
SPIR 0.00 0.0019000000320375 0.02 0.0180999999679625 1052.63156119806
FROM 문에서 서브 쿼리를 사용해 데이터셋을 만들 때 DECIMAL(18,2)가 소수점 2자리까지만 허용하므로 w52_min 이 0.00이 된 것이다.
'IT Story > SQL Story' 카테고리의 다른 글
| 전일 대비 종목 분석하기 (0) | 2022.12.06 |
|---|---|
| 하루 동안 상승/하락 종목 분석하기 (0) | 2022.12.06 |
| SQL 함수: 분석 함수 (0) | 2022.12.06 |
| SQL 함수: 순위 함수 (0) | 2022.12.06 |
| SQL 함수: 수학 함수 (0) | 2022.12.05 |