MS SQL 실무_4편

Pertition by 절

- 실전에서 가장 자주 사용하는 문법

- 금융 관련 데이터를 다룰 때 자주 활용함

- 순위함수, 이동함수를 같이 많이 사용함

 

1. 순위 함수

select name
, employees
, ROW_NUMBER() over (order by employees DESC) as 순위
from companyinfo
order by 순위

ROW_NUMBER() : employees가 같아도 다른 행으로 취급해라

DESC : 크기가 큰 순서대로 1등 구분, 생략시에 작은 순서대로 구분

RANK()나 DENSE_RANK()를 ROW_NUMBER() 대신 사용이 가능함

 

2. 이동 함수

- SQL Server에서 2012년 이후 버전부터 사용이 가능함

- lag : 이전 행의 값을 가져올 때 사용

- lead : 다음 행의 값을 가져올 때 사용

 

offset : 생략시에 1행의 앞(뒤) 값을 가져옴, 지정한 값만큼의 데이터를 가져옴

select date_
, close_ as 종가
, lag(close_,1) over (order by date_) as 어제종가
, lead(close_,1) over (order by date_) as 내일종가
from StockPrice
where ID=40853
order by Date_

-> ID : 40853인 삼성전자의 종가, 어제종가, 내일 종가를 한꺼번에 보여주기

 

lag, lead 함수

- 변화율을 계산할 때 필수적으로 사용

변화율 = (현재값-이전값)/이전값

= (현재값/이전값)-1

- 매일의 주식수익률 계산 가능

 

lag, lead함수를 이용해 오늘 수익률과 내일 수익률 구하기

 

select date_
, close_/lag(close_,1)over(order by date_)-1 as 오늘수익률
, lead(close_,1) over(order by date_)-1 as 내일수익률
from StockPrice
where ID=40853
order by Date_

3. 테이블 분할(partition by)

- group by와 동일하게 어떤 그룹에 적용하는 함수

- 그룹을 나누어서 해당 레코드의 순위를 살펴보거나 이동함수 등을 적용할 때 사용함

select name
, IncInCtryCode
, rank() over (partition by IncInCtryCode order by employees DESC) as 순위
from companyinfo

 

국가별 고용인 크기별 순위 확인하기

select name
, IncInCtryCode
, rank() over (partition by IncInCtryCode order by employees DESC) as 순위
from companyinfo
where IncInCtryCode is not null #국가코드의 null값을 없애고, 순위순서대로 정렬하기
order by 순위

select date_
, ID
, Close_/lag(Close_,1) over (Partition by ID order by date_)-1.0 as 수익률
from StockPrice

기업을 구분하는 ID 기준으로 수익률 계산