[MySQL] 윈도우함수(Window Functions)

기본 틀

• 함수(컬럼) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)
• PARTITION BY는 기존에 배운 GROUP BY랑 비슷한 원리라 생각하면 편하다.

집계 함수 (AVG,MIN,MAX,COUNT,SUM 등)

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

Employee table

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

• SUM(컬럼) OVER (ORDER BY 컬럼) ORDER BY로 인한 기준 컬럼을 바탕으로 누적합을 구함

Elevator table 1

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	84	5	406

예제 코드 1

SELECT id,
        Name,
        kg,
        Line,
        SUM(kg) OVER (ORDER BY Line) AS Cumsum
FROM Employee

Elevator table 2

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	84	5	406
B	Julia	70	1	70
B	Saorise	2	135

예제 코드 2

SELECT id,
        Name,
        kg,
        Line,
        SUM(kg) OVER (ORDER BY Line PARTITION BY id) AS Cumsum
FROM Employee

순위 정하기

• ROW_NUMBER(), RANK(), DENSE_RANK()
• 데이터의 순위를 정하는 함수
• ()안에 인자를 넣어주지 않음
  • ROW_NUMBER(): 값이 작은수부터 큰 수로 나타냄 (어떻게든 순서를 정하여 중복 순위가 없음)
  • RANK(): value가 같은 값일경우 같은 rank를 부여하고 중복된갯수만큼 그 다음 순위로 진행됨 (중복 순위가 있음)
  • DENSE_RANK(): value가 같은 값일경우 같은 rank를 부여하지만 중복된 갯수를 무시하고 다음 rank로 부여함

tabel

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

예제 코드

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'

데이터 위치 바꾸기

• LEAD(), LAG()
• 데이터를 몇칸씩 밀고 당기는 함수들
  • LEAD() : 당김
  • LAG() : 밈

예제 코드

• LAG(컬럼) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)
• LAG(컬럼,칸 수) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)
• LAG(컬럼,칸 수, Defalut) OVER (PARTITON BY 컬럼 ORDER BY 컬럼)
• LEAD(컬럼) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)
• LEAD(컬럼,칸 수) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)
• LEAD(컬럼,칸 수, Defalut) OVER (PARTITON BY 컬럼 ORDER BY 컬럼)

Default는 값을 입력하여 밀렸을 때 빈 칸이 NULL로 채워지게 되는데 이 때의 값을 정할 수 있음