윈도우 함수

함수(컬럼) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)

SUM(profit) OVER (PARTITION BY COUNTRY)

• PARTITION BY는 GROUP BY와 비슷하다고 생각하면 좋다.
• 어떤 것을 그룹으로 만들 것이지, 그 기준이 되는 컬럼을 적어주면 된다.
• PARTITION BY 컬럼, ORDER BY 컬럼은 둘 중 하나만 있을 수도, 둘 다 없을 수도 있다.

집계함수

OVER PARTITION BY : ~별 집계

MAX(컬럼) OVER (PARTITION BY 컬럼)

e.g.
각 부서별로 가장 많이 버는 사람을 찾기

ID	Name	Salary	DepartmentID	MaxSalary
1	Joe	70000	1	90000
2	Jim	90000	1	90000
3	Henry	80000	2	80000
4	Sam	60000	2	80000
5	Max	90000	1	90000

SELECT ID
	 , Name
     , Salary
     , DepartmentID
     , MAX(Salary) OVER (PARTITION BY DepartmentID) AS MaxSalary
FROM Employee

---

OVER ORDER BY : 누적 계산

• 누적합 구하기

SUM(컬럼) OVER (ORDER BY 컬럼)

e.g.

ID	Name	Kg	Line	CumSum
A	Joe	70	1	70
A	Jim	91	2	161
A	Henry	59	3	220
A	Sam	100	4	320
A	Max	86	5	406

SUM(Kg) OVER (ORDER BY Line) AS CumSum

• Kg 컬럼을 SUM하되, Line 컬럼을 기준 순서로, 누적합을 구해준다.

---

OVER BY + PARTITION BY : ~별 누적 계산

• ~별 누적합 구하기

SUM(컬럼) OVER (ORDER BY 컬럼 PARTITION BY 컬럼)

e.g.

Id	Name	Kg	Line	CumSum
A	Joe	70	1	70
A	Jim	91	2	161
A	Henry	59	3	220
A	Sam	100	4	320
A	Max	86	5	406
B	Julia	70	1	70
B	Zoey	65	2	135

SUM(Kg) OVER (ORDER BY Line PARTITION BY Id) AS CumSum

• PARTITION BY ID : 각 Id별로 누적합을 구해준다.
• Id가 A인 것끼리 누적합, B인 것끼리 누적합

윈도우 함수에만 지원되는 기능들

1) 순위 정하기

val	row_number	rank	dense_rank
1	1	1	1
1	2	1	1
2	3	3	2
3	4	4	3
3	5	4	3
3	6	4	3
4	7	7	4
4	8	7	4
5	9	9	5

• 아래는 val 컬럼을 기준으로 순위를 정하고 싶을 때 사용하는 함수들
• 순위를 정하는 함수들은 괄호 안에 인자가 들어가지 않는다

SELECT val
	 , ROW_NUMBER() OVER (ORDER BY val) AS 'row_number'
     , RANK() OVER (ORDER BY val) AS 'rank'
     , DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'
FROM sample

---

ROW_NUMBER()

ROW_NUMBER() OVER (ORDER BY val) AS 'row_number'

• 어떻게든 순서를 정해서 중복되는 순위가 없다.

RANK()

RANK() OVER (ORDER BY val) AS 'rank'

• 중복 순위가 있다.
• val이 같을 때는 같은 순위를 준다.
• 1등이 2명 있으면, 다음 순위는 3이 된다.

DENSE_RANK()

DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'

• 1등이 2명 있어도, 다음 순위는 2가 된다.
• 비는 숫자 없이 rank가 매겨진다.
  
  

---

2) 데이터 위치 바꾸기

LAG() : 데이터를 미는 함수

LEAD() : 데이터를 당기는 함수

Id	RecordDate	Temperature	lag	lead
1	2015-01-01	10	NULL	25
2	2015-01-02	25	10	20
3	2015-01-03	20	25	30
4	2015-01-04	30	20	28
5	2015-01-05	28	30	NULL

• LAG() : 괄호 안의 칼럼이 n칸씩 뒤로 밀림
• LEAD() : 괄호 안의 칼럼이 n칸씩 앞으로 당겨짐

SELECT Id
	 , RecordDate
     , Temperature
     , LAG(Temperature) OVER (ORDER BY RecordDate) AS 'lag'
     , LEAD(Temperature) OVER (ORDER BY RecordDate) AS 'lead'
FROM sample  

---

• RecordDate컬럼 순서로 Temperature 칼럼을 1칸씩 뒤로 밀기

LAG(Temperature) OVER (ORDER BY RecordDate) AS 'lag'

• RecordDate컬럼 순서로 Temperature 칼럼을을 2칸씩 뒤로 밀기

LAG(Temperature, 2) OVER (ORDER BY RecordDate) AS 'lag2'

• 0은 디폴트 값. NULL 대신 채워지는 값이라고 보면 된다.

LAG(Temperature, 2,0) OVER (ORDER BY RecordDate)

---

관련 문제

• Leet Code
  1) 180. Consecutive Numbers
  2) 184. Department Highest Salary
  3) 185. Department Top Three Salaries