[SQL 문법] RANK/DENSE_RANK, LAG, 분할 함수 / 테이블 분할

1. 순위함수

SELECT 컬럼이름
              , RANK() OVER (PARTITION BY 그룹 이름 ORDER BY 컬럼이름)
FROM 테이블이름

1-1. RANK

중복 순위 포함해서 출력 

ex) 1,1,3,4,5...

 

1-2. DENSE_RANK

중복 순위 무시하고 출력 (중간 순위를 비우지 않음)

ex) 1,1,2,2,3,4...

 

1-3. ROW_NUMBER

값에 무관하게 고유한 순위 부여 (ORDER BY 필요)

ex) 1,2,3,4,5,6...

 

 

✅RANK & DENSE RANK 예제

# RANK
SELECT *,
	   RANK() OVER(ORDER BY MKT_CAPITALIZATION_USD) AS 순위 -- MKT기준 오름차순으로 정렬후 순위를 매기는 컬럼 추가
FROM STOCK_INFO;

[OUT]
9744  1  -- RANK로하면 중복데이터는 공동1위(13개)후에 2위가 아니라 13위로 출력됨
9743 13
8716 47

 

# DENSE_RANK
SELECT INDCODE,
	   DENSE_RANK() OVER(ORDER BY INDCODE DESC) AS 순위 -- DENSE로하면 공동1위후에 공동2위 공동3위.. 순서대로 간다
FROM STOCK_INFO;

[OUT]
9744 1
9743 2
8716 3

 

 

 

2022.12.05 - [Data Analytics/CodingTest] - [leetcode] 185. Department Top Three Salaries / 그룹별 TOP3 순위 구하기

[leetcode] 185. Department Top Three Salaries / 그룹별 TOP3 순위 구하기

 

---

 

2. 이동함수

이전행, 다음행 가져오기 (offset : 몇개나 이동할지)

SELECT  컬럼1, 이동함수(컬럼1, offset) OVER (ORDER BY 컬럼1)
FROM 테이블이름

LAG 함수 : 이전 행의 값을 리턴

LEAD 함수 : 다음 행의 값을 리턴

ORDER BY 필수 

✅ LAG 기본 예제

# CLOSE_컬럼의 1일전 데이터를 가져오는데 기준OVER은 ID로 분할하고 MARKET으로 오름차순
SELECT id, marketdate, close_,
		LAG(close_, 1) OVER(PARTITION BY id ORDER BY MarketDate) as 어제값
FROM stocketf_price
ORDER BY id, marketdate;

=>
id  marketdate  close_  어제값
425 2018-06-04  40.08   null
425 2018-06-04  44.09   40.08001..

# CLOSE_컬럼의 5일전 데이터를 가져와줘
SELECT id, marketdate, close_,
		LAG(close_, 5) OVER(PARTITION BY id ORDER BY MarketDate) as 어제값
FROM stocketf_price
ORDER BY id, marketdate;

 

✅ LEAD 기본 예제

# CLOSE_컬럼의 내일값 가져와줘
SELECT id, marketdate, close_,
		LEAD(close_, 1) OVER(PARTITION BY id ORDER BY MarketDate) as 내일값
FROM stocketf_price
ORDER BY id, marketdate;

=>
id  marketdate  close_  내일값
425 2018-06-04  40.08   44.09..
425 2018-06-05  44.09   45.52..
425 2018-06-06  45.52   47.97..

 

---

 

3. 분할 함수(PARTITION BY)

SELECT 컬럼1, 함수( ) OVER (PARTITION BY 컬럼1 ORDER BY 컬럼1)
FROM 테이블이름

 

✅PARTITION BY 예제

SELECT *,
	   RANK() OVER(PARTITION BY REGION ORDER BY INDCODE DESC) AS 순위
FROM STOCK_INFO; -- 국가별로 순위를 매겨서 출력(CH안에서 1~순위매기고, JP에서 다시 1~순위매김)

 

 

---

 

4. FRAME 절

SELECT 컬럼1, 함수( ) OVER (ORDER BY 컬럼1 BETWEEN FRAME절 AND FRAME절)
FROM 테이블이름

CURRENT ROW	현재 로우
N PRECEDING	N번째 뒤의 로우
N FOLLOWING	N번째 앞의 로우
UNBOUNDED PRECEDING	가장 첫번째 로우
UNBOUNDED FOLLOWING	가장 마지막 로우

 

✅PRECEDING AND CURRENT ROW 예제

# CURRENT ROW(오늘 날짜)부터 4번째 뒤(4일전까지) 총 5일 데이터의 평균을 구하는 '이동평균'컬럼 보여줘
SELECT id, marketdate, close_,
	   AVG(CLOSE_) OVER(PARTITION BY ID ORDER BY MARKETDATE
						ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) 이동평균
FROM stocketf_price
ORDER BY id, marketdate;

 

 

✅UNBOUNDED PRECEDING AND CURRENT ROW 예제

# CLOSE_컬럼을 SUM하는데 조건(OVER)은 첫번째 로우부터 현재 로우까지 ROWS를 만들어서 '누적총계'컬럼으로 보여줘
SELECT id, marketdate, close_,
	   SUM(CLOSE_) OVER(PARTITION BY ID ORDER BY MARKETDATE
						ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 누적총계
FROM stocketf_price
ORDER BY id, marketdate;

 

 

---

 

5. 문제

❓1) Financial_Info 테이블에서 각 연도별 총자산(Total Assets) 순위를 구해보세요(내림차순)

SELECT *
	,RANK() OVER(PARTITION BY FISCALPRD_ ORDER BY VALUE_ DESC) 총자산순위
FROM FINANCIAL_INFO
WHERE NAME_ = 'TOTAL ASSETS';

 

 

❓2) StockETF_Price 테이블에서 삼성전자의 5일 /20일 이동평균 값을 구해보세요

SELECT ID, MarketDate, Close_,
	AVG(CLOSE_) OVER(ORDER BY MarketDate 
					 ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) MA5,
 	AVG(CLOSE_) OVER(ORDER BY MarketDate 
					 ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) MA20
FROM stocketf_price
WHERE ID = 40853
ORDER BY MarketDate;

# 삼성 아이디 검색(40853)
SELECT *
FROM stock_info
WHERE NAME LIKE '%SAMSUNG ELEC%';

 

 

❓3) Financial_Info 테이블에서 각 기업별로 전년도 대비Pretax Income 증감률을 확인해보세요 🡪 증감률 = (이번 금액 – 이전 금액) / abs(이전 금액)

SELECT *,
	   LAG(Value_, 1) OVER(PARTITION BY CODE ORDER BY FiscalPrd_) 전년도,
	   (VALUE_ - LAG(Value_, 1) OVER(PARTITION BY CODE ORDER BY FiscalPrd_)) / ABS(LAG(Value_, 1) OVER(PARTITION BY CODE ORDER BY FiscalPrd_)) 증감률
FROM financial_info
WHERE NAME_ = 'PRETAX INCOME'
ORDER BY CODE, FISCALPRD_;

=>
# Code	Name_	FiscalPrd_	CalPrdEndDate	Value_	전년도	증감률
1156	Pretax Income	2018	2018-12-31	1318670		null    null
1156	Pretax Income	2019	2019-12-31	1462671	 1318670	0.1092
1156	Pretax Income	2020	2020-12-31	1612134	 1462671	0.1022
1226	Pretax Income	2018	2018-03-31	435501	 null     null
1226	Pretax Income	2019	2019-03-31	395730	 435501	 -0.0913
1226	Pretax Income	2020	2020-03-31	412561	 395730	  0.0425
1226	Pretax Income	2021	2021-03-31	287561	 412561	 -0.3030