전일 대비 종목 분석하기

전일 대비 종목을 분석하려면 오늘 날짜의 행과 어제 날짜의 행을 일치시켜 오늘 종목 가격에서 어제 종목 가격을 빼는 계산을 수행해야 한다. 같은 테이블에서 데이터를 비교해야 하므로 SELF JOIN 또는 LAG/LEAD 함수를 사용해야 한다

01. SELF JOIN 함수로 전일 대비 증감과 증감률 구하기

SELECT

a.symbol,

a.date AS a_date,

CONVERT(DECIMAL(18,2), a.[close]) AS a_close,

'' As '---',

b.date AS b_date,

CONVERT(DECIMAL(18,2), b.[close]) AS b_close,

'' As '---',

CONVERT(DECIMAL(18,2), b.[close] - a.[close]) AS diff_price,

CONVERT(DECIMAL(18,2), (b.[close] - a.[close])/b.[close] * 100) AS diff_ratio

FROM stock AS a

INNER JOIN stock AS b ON a.symbol = b.symbol AND a.date = dateadd(day, -1, b.date)

WHERE a.date = '2021-10-06'

AND CONVERT(DECIMAL(18,2), b.[close] - a.[close]) > 0

stock 테이블에 별칭 a, b를 부여해 같은 테이블을 중복 사용하는 SELF JOIN을 적용했다. 조인 조건인 ON에 전일 대비 상승 종목을 구하고자 a 테이블에서 date가 '2021-10-06'에 해당하는 데이터와 b 테이블에서 -1로 계산한 date가 '2021-10-06'에 해당하는 데이터의 값을 비교했다.

symbol a_date a_close --- b_date b_close --- diff_price diff_ratio
^DJI 2021-10-06 34416.99 2021-10-07 34754.94 337.95 0.97
^FTSE 2021-10-06 6995.90 2021-10-07 7078.00 82.10 1.16
^GSPC 2021-10-06 4363.55 2021-10-07 4399.76 36.21 0.82
^IXIC 2021-10-06 14501.91 2021-10-07 14654.02 152.11 1.04

02. LEAD 함수로 전일 대비 증감과 증감률 구하기

다음 쿼리는 LEAD 함수를 사용해 기준 행에서 다음 행을 비교한 것이다. 같은 symbol을 비교하기 위해 PARTITION BY 문에 symbol 조건을 부여했다. 데이터는 전후 비교이므로 최소 이틀 분량의 데이터셋을 사용하려면 WHERE 문에 비교를 위한 날짜 범위를 지정해야 한다는 점도 잊지 말자.

SELECT

symbol,

date,

[close] As a_close,

LEAD( [close] ) OVER(PARTITION BY symbol ORDER BY [date] ASC) AS b_close

FROM stock AS a

WHERE date >= '2021-10-06' AND date <='2021-10-07'

ORDER BY symbol

결과

symbol date a_close b_close
^CMC200 2021-10-06 00:00:00.000 1323.48400878906 1310.09802246094
^CMC200 2021-10-07 00:00:00.000 1310.09802246094 NULL
^DJI 2021-10-06 00:00:00.000 34416.98828125 34754.94140625
^DJI 2021-10-07 00:00:00.000 34754.94140625 NULL

03. LEAD 함수를 사용한 증감 결과에서 NULL 값 제거하기

SELECT
symbol,
date,
CONVERT(DECIMAL(18,2), a_close) AS a_close,
CONVERT(DECIMAL(18,2), b_close) AS b_close,
CONVERT(DECIMAL(18,2), b_close - a_close) AS diff_price,
CONVERT(DECIMAL(18,2), (b_close - a_close)/b_close * 100) AS diff_ratio
FROM (
SELECT
symbol,
date,
[close] As a_close,
LEAD( [close] ) OVER(PARTITION BY symbol ORDER BY [date] ASC) AS b_close
FROM stock AS a
WHERE date >= '2021-10-06' AND date <='2021-10-07'
) AS X
WHERE b_close IS NOT NULL
AND CONVERT(DECIMAL(18,2), b_close - a_close) > 0

결과

symbol date a_close b_close diff_price diff_ratio
^DJI 2021-10-06 00:00:00.000 34416.99 34754.94 337.95 0.97
^FTSE 2021-10-06 00:00:00.000 6995.90 7078.00 82.10 1.16
^GSPC 2021-10-06 00:00:00.000 4363.55 4399.76 36.21 0.82
^IXIC 2021-10-06 00:00:00.000 14501.91 14654.02 152.11 1.04

04. SELF JOIN을 사용한 전일 대비 최대 상승한 종목 검색하기

SELECT

TOP 3 *

FROM (

SELECT
a.symbol,
a.date AS a_date,
CONVERT(DECIMAL(18,2), a.[close]) AS a_close,

b.date AS b_date,
  CONVERT(DECIMAL(18,2), b.[close]) AS b_close,
  CONVERT(DECIMAL(18,2), b.[close] - a.[close]) AS diff_price,
  CONVERT(DECIMAL(18,2), (b.[close] - a.[close])/b.[close] * 100) AS diff_ratio
FROM stock AS a

INNER JOIN stock AS B ON a.symbol = b.symbol AND a.date = dateadd(day, -1, b.date)
WHERE a.date >= '2021-10-06'

) AS A
WHERE diff_price > 0

ORDER BY diff_ratio DESC

결과

상위 N개의 데이터만 출력하고자 TOP 함수를 사용했으며 인자로 3을 입력했다. WHERE 문에 상승한 종목만 나타내야하므로 diff_price가 0보다 큰 조건을 사용했다. TOP 함수를 사용할 때는 상위 결과만 출력하므로 데이터가 반드시 정렬되어 있어야 한다. 따라서, ORDER BY ...DESC 문을 사용했다.

05. CTE 형식을 사용한 전일 대비 최대 상승한 종목 검색하기

WITH cte_stock AS (

SELECT

a.symbol,
a.date AS a_date,
CONVERT(DECIMAL(18,2), a.[close]) AS a_close,

INNER JOIN stock AS B ON a.symbol = b.symbol AND a.date = dateadd(day, -1, b.date)
WHERE a.date >= '2021-10-06'

)

SELECT

TOP 3*

FROM cte_stock

WHERE diff_price > 0

ORDER BY diff_ratio DESC

결과

06. 상/하위 3개 데이터를 합쳐서 검색하기

UNION 문을 사용한 쿼리를 작성

WITH cte_stock AS (

SELECT

a.symbol,
a.date AS a_date,
CONVERT(DECIMAL(18,2), a.[close]) AS a_close,

INNER JOIN stock AS B ON a.symbol = b.symbol AND a.date = dateadd(day, -1, b.date)
WHERE a.date >= '2021-10-06'

)

SELECT TOP 3* FROM cte_stock WHERE diff_price > 0 ORDER BY diff_ratio DESC

UNION ALL

SELECT TOP 3* FROM cte_stock WHERE diff_price < 0 ORDER BY diff_ratio ASC

메시지 156, 수준 15, 상태 1, 줄 1774
키워드 'UNION' 근처의 구문이 잘못되었습니다.

CTE 테이블을 사용해 데이터셋을 정의하고, 그런 다음 CTE 바깥 쪽에서 SELECT 문을 사용해 상위 3행, 하위 3행 데이터를 UNION ALL 문으로 합치려했는데, 에러가 났다. 그 이유는 UNION 문은 데이터셋 결과의 행을 합쳐서 보여주므로 열의 개수와 자료형이 같아야할 뿐만 아니라 쿼리의 결과로 제공되어야 한다.

07. 상/하위 3개 데이터를 합쳐서 검색하기

데이터셋을 합치는 것이므로 기존의 쿼리를 서브 쿼리로 한 번 더 감싸 ( ) 데이터셋으로 만들어 합치도록 한다.

WITH cte_stock AS (

SELECT

a.symbol,
a.date AS a_date,
CONVERT(DECIMAL(18,2), a.[close]) AS a_close,

INNER JOIN stock AS B ON a.symbol = b.symbol AND a.date = dateadd(day, -1, b.date)
WHERE a.date >= '2021-10-06'

)

(SELECT * FROM(SELECT TOP 3* FROM cte_stock WHERE diff_price > 0 ORDER BY diff_ratio DESC) AS X

UNION ALL

SELECT * FROM(SELECT TOP 3* FROM cte_stock WHERE diff_price > 0 ORDER BY diff_ratio ASC) AS X )

결과

symbol a_date a_close b_date b_close diff_price diff_ratio
ELC 2021-11-04 00:00:00.000 0.82 2021-11-05 00:00:00.000 25.36 24.54 96.77
PHUN 2021-10-21 00:00:00.000 1.53 2021-10-22 00:00:00.000 8.74 7.21 82.49
DWACU 2021-10-20 00:00:00.000 10.17 2021-10-21 00:00:00.000 49.08 38.91 79.28
TROW 2021-10-20 00:00:00.000 204.61 2021-10-21 00:00:00.000 204.62 0.01 0.00
^IXIC 2021-10-26 00:00:00.000 15235.71 2021-10-27 00:00:00.000 15235.84 0.13 0.00
ARE 2021-10-27 00:00:00.000 205.91 2021-10-28 00:00:00.000 205.92 0.01 0.00

다음은 FROM 문에서 서브 쿼리를 사용한 쿼리이다.

SELECT

FROM (

SELECT TOP 3 symbol, a_date, a_close, b_date, b_close, diff_price, diff_ratio FROM ( SELECT

a.symbol,
a.date AS a_date,
CONVERT(DECIMAL(18,2), a.[close]) AS a_close,

b.date AS b_date,
CONVERT(DECIMAL(18,2), b.[close]) AS b_close,
CONVERT(DECIMAL(18,2), b.[close] - a.[close]) AS diff_price,
CONVERT(DECIMAL(18,2), (b.[close] - a.[close])/b.[close] * 100) AS diff_ratio
FROM stock AS a

INNER JOIN stock AS B ON a.symbol = b.symbol AND a.date = dateadd(day, -1, b.date)
WHERE a.date >= '2021-10-06'

) AS A

WHERE diff_price > 0 ORDER BY diff_ratio DESC

) AS X

UNION ALL

SELECT

FROM (

SELECT

TOP 3

symbol,

a_date, a_close,

b_date, b_close,

diff_price, diff_ratio

FROM (

SELECT

a.symbol,
a.date AS a_date,
CONVERT(DECIMAL(18,2), a.[close]) AS a_close,

b.date AS b_date,
   CONVERT(DECIMAL(18,2), b.[close]) AS b_close,
   CONVERT(DECIMAL(18,2), b.[close] - a.[close]) AS diff_price,
     CONVERT(DECIMAL(18,2), (b.[close] - a.[close])/b.[close] * 100) AS diff_ratio
  FROM stock AS a

INNER JOIN stock AS B ON a.symbol = b.symbol AND a.date = dateadd(day, -1, b.date)
WHERE a.date >= '2021-10-06'

) AS A

WHERE diff_price > 0 ORDER BY diff_ratio ASC

) AS X

결과

UNION ALL을 기준으로 상위 3개 종목 데이터를 검색하고 아래쪽은 하위 3개 종목 데이터를 검색한다. 쿼리 구성은 같고 정렬 조건만 반대이다. 또한 CTE에 사용했던 방법처럼 FROM 문의 하위 쿼리를 한 번 더 감싸서 사용했다.

08. 임시 테이블에 중간 결과 저장하기

비슷한 형태의 부분 쿼리를 반복해 작성하는 과정이 비효율적으로 보일 수 있다. 그럴 때는 같은 데이터를 재사용한다는 점에 주목해 임시 테이블에 중간 결과를 저장한 다음 해당 데이터를 재사용하면 쿼리를 줄일 수 있다.

SELECT a.symbol,
                a.date AS a_date,
                CONVERT(DECIMAL(18,2), a.[close]) AS a_close,
                b.date AS b_date,
                CONVERT(DECIMAL(18,2), b.[close]) AS b_close,
                CONVERT(DECIMAL(18,2), b.[close] - a.[close]) AS diff_price,
                CONVERT(DECIMAL(18,2), (b.[close] - a.[close])/b.[close] * 100) AS diff_ratio

INTO #temp
FROM stock AS A
INNER JOIN stock AS B ON a.symbol = b.symbol AND a.date = dateadd(day, -1, b.date)
WHERE a.date >= '2021-10-06'

SELECT 문의 결과를 #temp에 저장한다. INTO 문에 사용하는 테이블 이름 앞에 #을 사용해 임시 테이블을 만들어 저장한다. 임시 테이블은 SQL Server에서 tempdb라는 특별한 공간에 저장된다.

09. 임시 테이블에 저장한 데이터로 검색 쿼리 작성하기

SELECT * FROM(SELECT TOP 3* FROM #temp WHERE diff_price > 0 ORDER BY diff_ratio DESC) AS X

UNION ALL

SELECT * FROM(SELECT TOP 3* FROM #temp WHERE diff_price > 0 ORDER BY diff_ratio ASC) AS X

결과

※ 임시 테이블은 임시 테이블을 생성한 세션에서만 사용!

세션이란 현재 연결된 커넥션을 뜻하는 것으로 쉽게 말해 현재 쿼리 창을 말한다.

※ 에러 메시지 참조

https://halizy.tistory.com/11

MSSQL 에러코드 정보 (0~5000) (MSSQL Error Code List)

MSSQL 에러코드별 정보(0~5000) 정리내역입니다. 트러블 슈팅용으로 정리해보았습니다 Ctrl+F 를 눌러 찾으시면 편합니다 '가 필요합니다. 2273 %1!CDATA 섹션이 종료되지 않았습니다. 2274 %1!줄 %2!에서 시

halizy.tistory.com

'IT Story > SQL Story' 카테고리의 다른 글

특정 기간 주가 하락했다가 회복한 종목의 증감률 구하기 (0)	2022.12.08
주가가 연속 상승한 종목 분석하기 (0)	2022.12.07
하루 동안 상승/하락 종목 분석하기 (0)	2022.12.06
주가 분석하기 (0)	2022.12.06
SQL 함수: 분석 함수 (0)	2022.12.06

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

bravesong2

전일 대비 종목 분석하기

'IT Story > SQL Story' 카테고리의 다른 글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

전일 대비 종목 분석하기

'IT Story > SQL Story' 카테고리의 다른 글

'IT Story/SQL Story' Related Articles

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역