MS SQL 실습 및 추가 공부 내용

MS SQL

MS SQL 실습 및 추가 공부 내용

s2h15 2024. 4. 12. 18:03

728x90

SSMS 주석생성 단축키 : Ctrl + K + C

주석 제거 : Ctrl + K + U

변수 지정

변수명 앞에 @가 붙음

DECLARE [변수이름] [데이터 형식] [=기본값,옵션]

ex) DECLARE @변수1 INT = 0,

@ 변수 2 INT = 0

SET @변수1 = 30

SET @변수2 = 0

SET 대신 SELECT 로 변수에 값을 저장할 수 있음

트랜잭션

특정한 목적을 위해 여러 명령을 수행 중 문제 발생시 이미 완료된 작업 모두를 취소하는 기능

시스템변수인 @@ERROR와 @@ROWCOUNT 와 같이 주로 쓰인다

DECLARE @INT_실패여부 INT

SET @INT_실패여부 = 0

----- 트랜잭션 시작 -----
BEGIN TRAN

----- UPDATE 후 에러 및 처리 건수를 체크하여 문제가 있으면 실패여부 1 -----
UPDATE A SET
	   A.수량 = 15
	FROM T매출 A
	WHERE A.일자 = '20200101'
	AND A.제품 = 'A1'

IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 SET @INT_실패여부 = 1

----- 실패여부 변수에 따라 COMMIT 또는 ROLLBACK 처리
IF @INT_실패여부 = 0 BEGIN
	COMMIT TRAN     -- 지금까지 작업한 것 작업 완료
END ELSE BEGIN
	ROLLBACK TRAN   -- 지금까지 작업한 것을 모두 취소
END

Case When 조건문

CASE

WHEN 조건식1 THEN 값1

WHEN 조건식 2 THEN 값 2

ELSE 값 3

END

< 추가 함수 정리 >

함수	설명
DECIMAL(a,b)	a에는 전체 숫자의 자리수, b에는 소수점자리수 예시) Decimal(5,3) : 12.345
REPLICATE(문자열 ,횟수)	지정된 문자열을 지정한 횟수만큼 반복 출력
CHARINDEX(찾을문자열, 대상문자 , 위치)	대상문자에서 찾을 문자열을 지정한 위치부터 찾아 인덱스 값을 출력한다

예제1) 52주 동안의 최저가, 최고가, 가격차이, 비율 검색

초안

SELECT symbol , MIN([close]) as min_close ,  MAX([close]) as max_close , (MAX([close]) - MIN([close])) as minmax_diff ,((MAX([close]) - MIN([close])) / MAX([close]) *100)as diff_ratio
FROM stock
WHERE date >= DATEADD(week, -52, '2021-10-04')
AND date <= '2021-10-04'
GROUP BY symbol

서브쿼리 사용

SELECT X.symbol , X.min_close ,  X.max_close , (X.max_close - X.min_close) AS minmax_diff ,((X.max_close - X.min_close)/X.max_close *100)as diff_ratio
FROM (
SELECT symbol, MIN([close]) AS min_close , MAX([close]) AS max_close 
FROM stock
WHERE date >= DATEADD(week, -52, '2021-10-04')
AND date <= '2021-10-04'
GROUP BY symbol) AS X

예제2) 상승/하락 종목 분석

○ 하루 시작가와 하루 종가를 비교해 상승한 금액, 비율과 하루 거래 중 최저 거래가와 최대 거래가의 차이를 구하기

SELECT date, symbol, 
CONVERT(DECIMAL(18,2),[open]) AS [open],
CONVERT(DECIMAL(18,2),[close]) AS [close],
CONVERT(DECIMAL(18,2), [close] - [open]) AS diff_price,
CONVERT(DECIMAL(18,2), ([close] - [open])/[close]*100) AS diff_ratio,
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]) / [high]*100) AS diff_high_ratio
FROM stock
WHERE date = '2021-10-06'

DECIMAL을 통해 데이터의 소수점자리 맞춰줌

○ 가격이 10% 이상 오른 종목 내림차순 검색

SELECT date, symbol, 
CONVERT(DECIMAL(18,2),[open]) AS [open],
CONVERT(DECIMAL(18,2),[close]) AS [close],
CONVERT(DECIMAL(18,2), [close] - [open]) AS diff_price,
CONVERT(DECIMAL(18,2), ([close] - [open])/[close]*100) AS diff_ratio,
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]) / [high]*100) AS diff_high_ratio
FROM stock
WHERE date = '2021-10-06'
AND CONVERT(DECIMAL(18,2), ([close] - [open])/[close]*100) >=10
ORDER BY CONVERT(DECIMAL(18,2), ([close] - [open])/[close]*100) DESC

○ 가격이 오른 종목의 상세 정보 검색하기

++ 기업이름, 산업군, 산업 종류 함께 검색

SELECT date, S.symbol, N.company_name , N.sector, N.industry,
CONVERT(DECIMAL(18,2),[open]) AS [open],
CONVERT(DECIMAL(18,2),[close]) AS [close],
CONVERT(DECIMAL(18,2), [close] - [open]) AS diff_price,
CONVERT(DECIMAL(18,2), ([close] - [open])/[close]*100) AS diff_ratio,
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]) / [high]*100) AS diff_high_ratio
FROM stock AS S INNER JOIN nasdaq_company AS N ON S.symbol = N.symbol 
WHERE date = '2021-10-06'
AND CONVERT(DECIMAL(18,2), ([close] - [open])/[close]*100) >=10
ORDER BY CONVERT(DECIMAL(18,2), ([close] - [open])/[close]*100) DESC

예제3) 전일 대비 종목 분석

○ SELF JOIN으로 전일대비 증감과 증감률 구하기

SELECT A.date AS a_date ,A.[close] AS a_close , ' ' AS '---',  B.date AS b_date , 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'

○ LEAD 함수를 사용하여 전일 대비 증감과 증감률 구하기

SELECT symbol, date, [close] AS a_close , LEAD([close]) OVER (PARTITION BY symbol ORDER BY [date] ASC) AS b_close
FROM stock
WHERE date >= '2021-10-06' AND date <= '2021-10-07'
ORDER BY symbol

Partition By를 통해 같은 symbol과 비교하도록 함

Where 절에 이틀로 date를 제한해둬야함

2일차의 행은 비교할 열이 없으므로 Null이 표시됨

○ 위의 결과에서 NULL 제거하기

SELECT symbol, date, a_close, b_close
FROM (SELECT symbol, date, [close] AS a_close , LEAD([close]) OVER (PARTITION BY symbol ORDER BY [date] ASC) AS b_close
FROM stock
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

NULL값 제거하기 위해 IS NOT NULL 조건을 사용하였다.

식을 간결하게 만들기 위해

FROM의 하위 쿼리에 LEAD 함수를 사용하여 전일 데이터의 결과 b_close 열을 생성하고 서브쿼리 밖의 SELECT 문에서

a_close와 b_close를 재사용해 계산하였다.

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

SELECT Top 3 A.symbol, A.date , B.date , A.[close] as a_close , B.[close] as b_close , (B.[close] - A.[close])/B.[close] 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'
ORDER BY diff_ratio DESC

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

WITH cte_stock AS (
SELECT A.symbol, A.date as a_date , B.date as b_date, A.[close] as a_close , B.[close] as b_close , (B.[close] - A.[close])/B.[close] 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')
SELECT Top 3 * 
FROM cte_stock 
ORDER BY diff_ratio DESC

CTE를 통해 위와 동일한 결과를 얻기 위해서는 전체적인 select 구문을

WITH cte테이블명 AS ( select ~ from~ where~) 과 같이 작성한 후

SELECT * FROM cte테이블명 ORDER BY 조건식 으로 조회하면 된다.

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

WITH cte_stock AS (
SELECT A.symbol, A.date as a_date , B.date as b_date, A.[close] as a_close , B.[close] as b_close , (B.[close] - A.[close])/B.[close] 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')
SElECT * FROM (SELECT Top 3 * 
FROM cte_stock 
ORDER BY diff_ratio DESC) AS X
UNION
SELECT * FROM (SELECT Top 3 * 
FROM cte_stock 
ORDER BY diff_ratio ASC) AS X

UNION 할때 SELECT * FROM (기존 상위 3개 조회했던 쿼리) 와 같이 한번 더 감싸서 FROM 서브쿼리를 사용하여 조회해야한다.

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

같은 데이터를 재사용할 때 임시 테이블에 중간결과를 저장한 다음 해당 데이터를 재사용하면 쿼리를 줄일 수 있다.

임시 테이블에 저장하는 구문은 INTO # 테이블명 이다. 위치는 select 구문 뒤 from 앞

SELECT A.symbol, A.date as a_date , B.date as b_date, A.[close] as a_close , B.[close] as b_close , (B.[close] - A.[close])/B.[close] 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'

이때 temp 임시 테이블은 SQL Server에서 tempdb라는 특별한 공간에 저장된다.

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

SELECT * FROM (SELECT TOP 3 * FROM #temp ORDER BY diff_ratio DESC) AS X
UNION ALL
SELECT * FROM (SELECT TOP 3 * FROM #temp ORDER BY diff_ratio ASC) AS X

이때 주의해야할 점은 임시테이블을 생성한 세션에서만 임시테이블을 사용할 수 있다는 것이다.

즉 현재 쿼리 창에서만 사용이 가능하고 새 쿼리창을 열어 임시테이블을 호출하면 오류가 발생한다.

예제4) 주가가 연속 상승한 종목 분석

2021년 2월 17일부터 2021년 2월 24일 까지의 일별 주식데이터를 비교해서 주가가 10%이상 오른 종목 중 해당 기간동안 한번도 주가가 떨어지지 않은 종목만 검색

○ 증감 주가와 증감 주가율 저장하기

SELECT a.symbol , a.[close] AS a_close, b.[close] AS b_close , 
b.[close] - a.[close] AS close_diff , (b.[close] - a.[close])/a.[close] *100 AS ratio_diff
INTO #temp
FROM stock AS a INNER JOIN stock AS b ON a.symbol = b.symbol AND b.date = '2021-02-24' AND a.date = '2021-02-17'

임시테이블 temp에 저장

○ 주가 증가율이 10% 이상인 일별 데이터 저장하기

ratio_diff >= 10 인 일별 데이터 집합 만들기

ROW_NUMBER 함수를 사용하여 날짜 순서대로 순위를 부여 -> 전일 날짜를 비교할 때 주식시장이 열리지 않는 주말이나 공휴일이 포함될 경우 공백이 생기기 때문

이를 temp2 임시테이블에 저장

SELECT ROW_NUMBER() OVER (PARTITION BY a.symbol ORDER BY date ASC) AS num , a.symbol, a.date , a.[close]
INTO #temp2
FROM stock AS a INNER JOIN #temp AS b ON a.symbol = b.symbol
WHERE b.ratio_diff >= 10
AND a.date BETWEEN '2021-02-17' AND '2021-02-24'

○ SELF JOIN을 사용하여 순위 데이터 비교하기

#temp2 테이블을 SELF JOIN하여 현재 순위보다 순위가 1만큼 높은 데이터를 비교한다

이를 #temp3에 저장한다

SELECT a.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 close_diff , (b.[close] - a.[close]) / a.[close] *100 as ratio_diff
INTO #temp3
FROM #temp2 AS a INNER JOIN #temp2 AS b ON a.symbol = b.symbol AND a.num = b.num-1

○ 주가 하락이 한번도 없는 종목 검색하기

#temp3 에서 전일과 비교해서 한번이라도 주가가 하락한 종목이 있으면 해당 종목을 제외한 데이터를 #temp4 테이블에 저장한다.

WHERE 문에 NOT IN을 사용하여 하락한 symbol이 있는지 검사한 다음 조건에 포함되지 않는 항목만 검색한다.

SELECT symbol , a_date, a_close, b_date, b_close, close_diff , ratio_diff
INTO #temp4
FROM #temp3 
WHERE symbol NOT IN (
SELECT DISTINCT(symbol)
FROM #temp3
WHERE close_diff < 0 )

>> DISTINCT 로 고유값만 추출하였지만 서브쿼리 내부에 GROUP BY symbol로 고유값을 추출할 수도 있다.

○ 모든 임시 테이블과 나스닥 기업 테이블을 조인하여 최종 데이터 생성하기

SELECT a.symbol , d.company_name, d.industry, a.a_close , a.b_close , a.close_diff AS diff_price , ratio_diff 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 ratio_diff DESC

예제5) 맨하탄 거리 구하기

Consider and to be two points on a 2D plane.

happens to equal the minimum value in Northern Latitude (LAT_N in STATION).
happens to equal the minimum value in Western Longitude (LONG_W in STATION).
happens to equal the maximum value in Northern Latitude (LAT_N in STATION).
happens to equal the maximum value in Western Longitude (LONG_W in STATION).

Query the Manhattan Distance between points and and round it to a scale of decimal places.

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

SELECT convert( decimal(18,4), Round(ABS(a.LAT_N - b.LAT_N) + ABS(a.LONG_W - b.LONG_W),4) )
FROM 
    (SELECT TOP 1 LAT_N , LONG_W 
     FROM STATION
     ORDER BY LAT_N DESC) AS a
  ,
    (SELECT TOP 1 LAT_N , LONG_W
     FROM STATION
     ORDER BY LONG_W ASC) AS b

처음에는 INNER JOIN을 사용하려고하니까 해결이 안됐다.

SELECT convert( decimal(18,4), ROUND(ABS(a.LAT_N - b.LAT_N) + ABS(a.LONG_W - b.LONG_W), 4))
FROM 
    (SELECT MIN(LAT_N) AS LAT_N, MIN(LONG_W) AS LONG_W
     FROM STATION) AS a
CROSS JOIN
    (SELECT MAX(LAT_N) AS LAT_N, MAX(LONG_W) AS LONG_W
     FROM STATION) AS b;

해결책은 CROSS JOIN으로

다음과 같은 역할을 한다

두개의 행이있는 두 서브쿼리를 cross join하면 총 4개의 행으로 만들어진다.

728x90