12. 윈도우 함수

자세한 내용은 아래 링크 참고

👉 dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

A window function performs an aggregate-like operation on a set of query rows.

GROUP BY 함수와 비슷하게 SUM, COUNT, MAX, MIN, AVG 등을 그룹별로 구해볼 수 있는 함수.

GROUP BY의 대부분의 aggregation 함수를 다 사용할 수 있다고 보면 된다.

하지만 GROUP BY와는 다르다.

어떤 점이?

By contrast, window operations do not collapse groups of query rows to a single output row. 

Instead, they produce a result for each row. 

GROUP BY처럼 한 줄로 요약해서 보여주는 대신, 각각의 행에 결과물들을 찍어낸다.

집계함수는 아니지만 윈도우함수에서만 지원하는 특별한 함수들도 존재한다.

 

[집계함수]

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

함수 : AVG, SUM, COUNT, MAX, MIN 등

PARTITION BY : 묶어줄 기준 

ORDER BY : 순서를 정해줄 기준

*PARTITION BY와 ORDER BY는 둘 다 존재해도, 둘 다 존재하지 않아도, 하나만 존재해도 OK

 

예시1 ) SELECT id, name, salary, depart_id

예1시 ) ,MAX(salary) OVER (PARTITION BY depart_id) AS MaxSalary

예1시 ) FROM Employee

id	name	salary	depart_id	MaxSalary
1	caroline	70,000	1	90,000
2	alexa	90,000	1	90,000
3	cathy	80,000	2	80,000
4	anne	60,000	2	80,000
5	jess	90,000	1	90,000

> 각 부서별로 가장 많이 버는 사람의 연봉을 각 행에 보여준다.

 

예시2 ) SELECT id, name, kg, line

예시 2) ,SUM(kg) OVER (ORDER BY line) AS CumSum

예시 2) FROM Elevator

id	name	kg	line	CumSum
A	caroline	70	1	70
A	alexa	91	2	161
A	cathy	59	3	220
A	anne	100	4	320
A	jess	86	5	406

> line을 기준으로 정렬하면서 kg값을 각 행에 누적해서 합계로 보여준다.

 

예시3 ) SELECT id, name, kg, line

예시3 ) ,SUM(kg) OVER (ORDER BY line PARTITION BY id) AS CumSum

예시 3) FROM Elevator

id	name	kg	line	CumSum
A	caroline	70	1	70
A	alexa	91	2	161
A	cathy	59	3	220
A	anne	100	4	320
A	jess	86	5	406
B	julia	70	1	70
B	abigail	65	2	135

* id를 기준으로 그룹해서 누적합을 구해달라고 했으므로 id가 B로 넘어가면 누적합도 처음부터 다시!

 

[윈도우 함수에만 존재하는 연산]

1. 순위 정하기

ROW_NUMBER(), RANK(), DENSE_RANK()

자세한 내용은 아래 링크 참고

👉 dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

예시1 ) SELECT val, ROW_NUMBER() OVER (ORDER BY val) AS 'row_num'

예시1 ) ,RANK() OVER (ORDER BY val) AS 'rank'

예시1 ) ,DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank' *함수 자체에는 아무 인자도 안들어감

val	row_num	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

row_number : val을 기준으로 순위를 차례대로 매겨준다. *중복되는 순위는 없다. 

rank : val이 똑같을 때에는 같은 순위를 준다. 비는 순위가 있음.

dense_rank : val이 똑같을 때 같은 순위를 주나 비는 순위가 없음.

 

2. 데이터 위치 바꾸기

예시 ) SELECT id, RecordDate, temperature

--temperature 데이터를 RecordDate를 기준으로 한 칸씩 밀어줘라

예시 ) ,LAG(temperature) OVER (ORDER BY RecordDate) AS 'lag'

--temperature 데이터를 RecordDate를 기준으로 한 칸씩 당겨줘라

예시 ) ,LEAD(temperature) OVER (ORDER BY RecordDate) AS 'lead'

예시 ) FROM sample

id	RecordDate	temprature	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

만약 한 칸이 아니라 여러 칸을 당기고 싶을때는? *NULL 데이터도 두 개

👉 LAG(temperature, 2 ) OVER (ORDER BY RecordDate) AS 'lag'

NULL값을 대체해줄 디폴트값을 지정하고 싶을때는?

👉 LAG(temperature, 2, 0 ) OVER (ORDER BY RecordDate) AS 'lag'

예시출처 : https://leetcode.com/problems/rising-temperature/

 

 

[윈도우 함수 없이 누적합 구하기(JOIN을 활용)]

*위에 caroline, alexa,.. 표를 예로 들면

> SELECT e1.Id, e1.Name, e1.kg, e1.Line, SUM(e2.kg) AS CumSum
> FROM Elevator e1

> INNER JOIN Elevator e2 ON e1.Id = e2.Id -- PARTITION

> AND e1.Line >= e2.Line

> GROUP BY e1.Id, e1.Name, e1.kg, e1.Line

 

[윈도우 함수 없이 누적합 구하기(SELECT 서브쿼리를 활용)]

> SELECT e1.Id, e1.Name, e1.kg, e1.Line

> SELECT ,(SELECT SUM(e2.kg)

> SELECT,(FROM Elevator e2

> SELECT,(WHERE e1.Id = e2.Id

> SELEC T,(AND e1.Line >= e2.Line) AS CumSum

> FROM Elevator e1

 

자세한 내용은 여기 참고

👉 https://medium.com/better-programming/4-ways-to-calculate-a-running-total-with-sql-986d0019185c

>