MySQL 윈도우함수 간단 설명

이번 시간은 MySQL의 윈도우함수를 정리해보려고 합니다. 윈도우함수의 특징은 GROUP BY처럼  데이터를 그룹화하여 집계하거나 연산해주는 기능입니다. 다만 GROUP BY는 GROUP BY에 지정해주는 것을 토대로 해당 결과물만 보여주는 반면 윈도우함수는 기존 데이터에 값을 추가하여 나타내주는 게 특징입니다.

 

윈도우함수의 구조

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

윈도우함수에 들어가는 함수는 다양한 것들이 있는 이부분은 아래에 설명을 해보겠습니다. 괄호 안에 있는 PATITION BY는 GROUP BY 와 비슷한 것이라고 생각하면 됩니다. 즉, PARTITION BY에는 기준이되는 컬럼을 넣는다고 보시면 됩니다. ORDER BY는 당연히 순서를 지정하는 것이겠지요.

 

예시

https://leetcode.com/problems/department-highest-salary/

Department Highest Salary - LeetCode

Employee =
| id | name  | salary | departmentId |
| -- | ----- | ------ | ------------ |
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |

________________________________________________________________________________________
###윈도우함수로 departmentId기준 최고 샐러리 확인해보기

SELECT id, name,salary,departmentId,
       MAX(salary) OVER (PARTITION BY departmentId) AS MAX_SALARY
FROM Employee;

output:
| id | name  | salary | departmentId | MAX_SALARY |
| -- | ----- | ------ | ------------ | ---------- |
| 1  | Joe   | 70000  | 1            | 90000      |
| 2  | Jim   | 90000  | 1            | 90000      |
| 5  | Max   | 90000  | 1            | 90000      |
| 3  | Henry | 80000  | 2            | 80000      |
| 4  | Sam   | 60000  | 2            | 80000      |
__________________________________________________________________________________________

###GROUP BY로 departmentId기준 최고 샐러리 확인해보기
SELECT id, name, salary, departmentId,
       MAX(salary)
FROM Employee
GROUP BY departmentId

output:
| id | name  | salary | departmentId | MAX(salary) |
| -- | ----- | ------ | ------------ | ----------- |
| 1  | Joe   | 70000  | 1            | 90000       |
| 3  | Henry | 80000  | 2            | 80000       |

네, 지난번 join에 서브쿼리를 활용해 퀴즈를 풀었던 예시를 가지고 한번 비교를 해봤는데요. 윈도우함수를 사용하면 원본 테이블에 각 departmentId 기준의 최고 샐러리가 나오는 것을 알 수 있습니다. 윈도우함수는 이밖에도 SUM(), AVG(), COUNT() 등 다양한 집계함수를 지원하고 있습니다. 

 

윈도우함수는 이뿐만 아니라 윈도우함수에서만 지원하는 함수들이 있다. 

 

윈도우함수에서만 지원하는 함수

 

1. 순위 정하기

: ROW_NUMBER(), RANK(), DENSE_RANK()

 

ROW_NUMBER()의 경우 순위를 지정할 때 공동순위가 없다. RANK()는 우리가 흔히 아는 순위로 생각하면 되고 공동순위가 존재한다. DENSE_RANK()는 RANK()처럼 공동순위가 있지만 다른 점은 그 다음 순위가 공동 순위만큼 건너 뛰는 것이 아니라 바로 다음 숫자로 표기된다는 것이다. 즉, 2위가 3명있을 때 RANK()의 경우 그다음 순위가 5위가 되지만 DENSE_RANK()는 3위로 표기된다는 게 특징이다.

 

구조

SELECT val

     , ROW_NUMBER() OVER (ORDER BY val) AS 'ROW_NUMBER'----- 중복이 없는 순위
     , RANK () OVER (ORDER BY val) AS 'RANK' ---- 공동순위 가능
     , DENSE_RANK (ORDER BY val) AS 'DENSE_RANK' ---- RANK 처럼 공동순위 가능, 순차적

FROM table

 

2. 데이터 위치 바꾸기

: LAG(), LEAD()

 

LAG()와 LEAD()는 데이터의 칸을 밀고 당기는 함수다. 즉, 데이터 위치를 바꿔주는 함수다. LAG()는 데이터를 아래로 미는 함수라면 LEAD()는 데이터의 칸을 위로 당기는 함수다.

 

구조

####LAG

LAG(컬럼) OVER (PATITION BY 컬럼 ORDER BY 컬럼)
LAG(컬럼, 칸수) OVER (PATITION BY 컬럼 ORDER BY 컬럼)
LAG(컬럼, 칸수, DEFAULT) OVER (PATITION BY 컬럼 ORDER BY 컬럼)---DEFAULT는 NULL대신 들어가는 수

####LEAD

LEAD(컬럼) OVER (PATITION BY 컬럼 ORDER BY 컬럼)
LEAD(컬럼, 칸수) OVER (PATITION BY 컬럼 ORDER BY 컬럼)
LEAD(컬럼, 칸수, DEFAULT) OVER (PATITION BY 컬럼 ORDER BY 컬럼)---DEFAULT는 NULL대신 들어가는 수