SELECT Id
, Name
, Salary
, DepartmentId
, MAX(Salary) OVER (PARTITION BY DepartmentId) AS MaxSalary
FROM Employee
SUM(컬럼) OVER (ORDER BY 컬럼)
누적합을 구해주는 결과
sum을 하되 Line컬럼을 순서로 해서 차곡차곡 더하기
SELECT Id
, Name
, kg
, Line
, SUM(kg) OVER (ORDER BY Line) AS CumSum
FROM Elevator
SUM(컬럼) OVER (ORDER BY 컬럼 PARTITION BY 컬럼)
Id가 A일 때는 Line별로 차근차근 더하고, B일 때는 누적합이 초기화되서 다시 차근차근 더하게 됨
SELECT Id
, Name
, kg
, Line
, SUM(kg) OVER (ORDER BY Line PARTITION BY Id) AS CumSum
FROM Elevator
id
name
kg
line
cumsum
A
aaa
70
1
70
A
bbb
91
2
161
A
ccc
79
3
240
B
ddd
100
4
100
집계 함수 이외에 윈도우 함수에만 지원되는 함수
순위 정하기 (ROW_NUMBER(), RANK(), DENSE_RANK())
val 컬럼을 기준으로 해서 순위를 지정해달라
괄호 안에는 아무 인자도 들어가지 않음
SELECT val
, ROW_NUMBER() OVER (ORDER BY val) AS 'row_number'
, RANK() OVER (ORDER BY val) AS 'rank'
, DENSE_PARK() OVER (ORDER BY val) AS 'dense_rank'
FROM sample
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
ROW_NUMBER()
val가 커짐에 따라 순위지정
중복되는 순위 X
RANK()
val가 똑같을 때 같은 순위를 줌
1위가 2번이면 그 다음 순위는 3위
중복되는 순위 O
DENSE_RANK()
val가 똑같을 때 같은 순위를 줌
1위가 2번이면 그 다음 순위는 2위
중복되는 순위 X
데이터 위치 바꾸기 (LEAD(), LAG())
데이터를 몇 칸씩 밀거나 몇 칸씩 당겨오는 함수
LAG()는 데이터를 밀고 LEAD()는 데이터를 당긴다
SELECT Id
, RecordDate
, Temperature
-- Temperature 컬럼을 recordDate순으로 해서 뒤로 밀어달라
, LAG(Temperature) OVER (ORDER BY RecordDate) AS 'lag'
-- LAG()의 반대 > Temperature 컬럼을 recordDate순으로 해서 데이터를 당겨달라
, LEAD(Temperature) OVER (ORDER BY RecordDate) AS 'lead'
FROM sample
SELECT Id
, RecordDate
, Temperature
-- 기본적으로는 한칸씩 밀고 당기지만 2칸을 밀고 당기고 싶다면 아래와 같이 넣어줄 수 있다
, LAG(Temperature, 2) OVER (ORDER BY RecordDate) AS 'lag'
, LEAD(Temperature, 2) OVER (ORDER BY RecordDate) AS 'lead'
FROM sample
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
맨 끝에 0은 디폴트 값으로 null 대신에 채워지는 값이다
LAG(Temperature, 2, 0)
LEAD(Temperature, 2, 0)
🔥 심화(윈도우 함수 구하지 않고 누적합 구하기)
elavator 테이블
id
name
kg
line
cumsum
A
aaa
70
1
70
A
bbb
91
2
161
A
ccc
79
3
240
B
ddd
100
4
100
JOIN 활용 + Group By
SELECT *
FROM Elavator e1
INNER JOIN Elavator e2 ON e1.Id = e2.Id -- PARTITION
and e1.Line >= e2.Line
e1의 line이 1일 때에는 e2의 line이 1
e2의 line이 2일 때에는 e2의 line은 1, 2 ...
SELECT e1.Id
, e1.Name
, e1.kg
, e1.line
, SUM(e2.kg) AS CumSum
FROM Elavator e1
INNER JOIN Elavator e2 ON e1.Id = e2.Id -- PARTITION
AND e1.Line >= e2.Line
GROUP BY e1.Id, e1.Name, e1.kg, e1.line
group by를 활용하여 id별 합산
SELECT절 서브쿼리 활용
SELECT e1.Id
, e1.Name
, e1.kg
, e1.Line
, (SELECT SUM(e2.kg)
FROM Elevator e2
WHERE e1.Id = e2.Id
AND e1.Line >= e2.Line) AS CumSum
FROM Elevator e1