개요
MySQL 8.0부터는 Window 함수를 지원하면서 보다 편리하게 순위를 매길 수 있게 되었다. 순위 함수는 Window 함수 중 비집계 함수이며 OVER 절과 함께 사용해야 한다. MySQL에서 지원하는 순위함수는 RANK, DENSE_RANK, PERCENT_RANK, ROW_NUMBER 정도가 있다.
예시 데이터를 이용해 순위 함수의 사용 방법을 정리한다.
예시 데이터
아래의 사원 번호(empno), 사원 이름(ename), 직업(job), 급여(sal) 데이터로 구성된 테이블을 예시로 사용한다.
DDL
CREATE TABLE emp (
empno int,
ename varchar(30),
job varchar(30),
sal int
)
INSERT INTO emp
VALUES (7902, 'FORD', 'ANALYST', 3000),
(7788, 'SCOTT', 'ANALYST', 3000),
(7369, 'SMITH', 'CLERK', 800),
(7900, 'JAMES', 'CLERK', 950),
(7876, 'ADAMS', 'CLERK', 1100),
(7934, 'MILLER', 'CLERK', 1300),
(7782, 'CLARK', 'MANAGER', 2450),
(7698, 'BLAKE', 'MANAGER', 2850),
(7566, 'JONES', 'MANAGER', 2975),
(7839, 'KING', 'PRESIDENT', 5000),
(7654, 'MARTIN', 'SALESMAN', 1250),
(7521, 'WARD', 'SALESMAN', 1250),
(7844, 'TURNER', 'SALESMAN', 1500),
(7499, 'ALLEN', 'SALESMAN', 1600);| empno | ename | job | sal |
| ----- | ------ | --------- | ---- |
| 7902 | FORD | ANALYST | 3000 |
| 7788 | SCOTT | ANALYST | 3000 |
| 7369 | SMITH | CLERK | 800 |
| 7900 | JAMES | CLERK | 950 |
| 7876 | ADAMS | CLERK | 1100 |
| 7934 | MILLER | CLERK | 1300 |
| 7782 | CLARK | MANAGER | 2450 |
| 7698 | BLAKE | MANAGER | 2850 |
| 7566 | JONES | MANAGER | 2975 |
| 7839 | KING | PRESIDENT | 5000 |
| 7654 | MARTIN | SALESMAN | 1250 |
| 7521 | WARD | SALESMAN | 1250 |
| 7844 | TURNER | SALESMAN | 1500 |
| 7499 | ALLEN | SALESMAN | 1600 |
RANK
파티션 내 현재 행의 순위를 반환한다. 동점인 경우 동일한 순위를 반환한다. 공동 순위가 존재하는 경우, 다음 순위는 그 수만큼 건너뛰어 매겨진다. (예시: 1, 2, 2, 4, ……)
RANK() OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)
예시 데이터로 급여를 기준으로 사원 정보를 오름차순으로 순위를 매겨 나열하면 다음과 같다.
SELECT
empno,
ename,
job,
sal,
RANK() OVER (ORDER BY sal) AS sal_rank
FROM emp
;
실행 결과
| empno | ename | job | sal | sal_rank |
| ----- | ------ | --------- | ---- | -------- |
| 7369 | SMITH | CLERK | 800 | 1 |
| 7900 | JAMES | CLERK | 950 | 2 |
| 7876 | ADAMS | CLERK | 1100 | 3 |
| 7654 | MARTIN | SALESMAN | 1250 | 4 |
| 7521 | WARD | SALESMAN | 1250 | 4 |
| 7934 | MILLER | CLERK | 1300 | 6 |
| 7844 | TURNER | SALESMAN | 1500 | 7 |
| 7499 | ALLEN | SALESMAN | 1600 | 8 |
| 7782 | CLARK | MANAGER | 2450 | 9 |
| 7698 | BLAKE | MANAGER | 2850 | 10 |
| 7566 | JONES | MANAGER | 2975 | 11 |
| 7902 | FORD | ANALYST | 3000 | 12 |
| 7788 | SCOTT | ANALYST | 3000 | 12 |
| 7839 | KING | PRESIDENT | 5000 | 14 |salesman인 Martin과 Ward, analyst인 Form와 Scott이 같은 급여를 받아 공동 순위가 발생했고, 그다음 순위는 4에서 6, 12에서 14로 이어지는 모습을 확인할 수 있다.
DENSE_RANK
파티션 내 현재 행의 순위를 반환한다. 동점인 경우 동일한 순위를 반환한다. 공동 순위가 존재 해도 다음 순위는 연속된 값으로 이어진다. (예시: 1, 2, 2, 3, ……)
DENSE_RANK() OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)
예시 데이터로 급여를 기준으로 사원 정보를 내림차순으로 순위를 매겨 나열하면 다음과 같다.
SELECT
empno,
ename,
job,
sal,
DENSE_RANK() OVER (ORDER BY sal DESC) AS sal_rank
FROM emp
;
실행 결과
| empno | ename | job | sal | sal_rank |
| ----- | ------ | --------- | ---- | -------- |
| 7839 | KING | PRESIDENT | 5000 | 1 |
| 7902 | FORD | ANALYST | 3000 | 2 |
| 7788 | SCOTT | ANALYST | 3000 | 2 |
| 7566 | JONES | MANAGER | 2975 | 3 |
| 7698 | BLAKE | MANAGER | 2850 | 4 |
| 7782 | CLARK | MANAGER | 2450 | 5 |
| 7499 | ALLEN | SALESMAN | 1600 | 6 |
| 7844 | TURNER | SALESMAN | 1500 | 7 |
| 7934 | MILLER | CLERK | 1300 | 8 |
| 7654 | MARTIN | SALESMAN | 1250 | 9 |
| 7521 | WARD | SALESMAN | 1250 | 9 |
| 7876 | ADAMS | CLERK | 1100 | 10 |
| 7900 | JAMES | CLERK | 950 | 11 |
| 7369 | SMITH | CLERK | 800 | 12 |salesman인 Martin과 Ward, analyst인 Form와 Scott이 같은 급여를 받아 공동 순위가 발생했고, 그다음 순위는 2에서 3, 9에서 10으로 이어지는 모습을 확인할 수 있다.
PERCENT_RANK
PERCENT_RANK()
OVER (
PARTITION BY expr,...
ORDER BY expr [ASC|DESC],...
)PERCENT_RANK는 백분율 순위를 구할 수 있다. 반환 값의 범위는 0에서 1까지이며 아래 수식의 결과로 계산된 행의 상태 순위를 반환한다.
(rank - 1) / (rows - 1)수식에서 rank는 행의 순위이고, rows는 파티션 행의 수이다. partition 내의 첫 행은 항상 0을 반환한다. rank가 같으면 같은 값을 반환한다.
예시 데이터로 job 별로 사원 정보를 급여 기준으로 오름차순으로 백분율 순위를 매겨 나열하면 다음과 같다. 백분율 순위 값의 소숫점 자릿수는 2가 되도록 반올림한다.
SELECT
empno,
ename,
job,
sal,
ROUND(PERCENT_RANK() OVER (PARTITION BY job ORDER BY sal), 2) AS sal_rank
FROM emp
;
실행 결과
| empno | ename | job | sal | sal_rank |
| ----- | ------ | --------- | ---- | -------- |
| 7902 | FORD | ANALYST | 3000 | 0 |
| 7788 | SCOTT | ANALYST | 3000 | 0 |
| 7369 | SMITH | CLERK | 800 | 0 |
| 7900 | JAMES | CLERK | 950 | 0.33 |
| 7876 | ADAMS | CLERK | 1100 | 0.67 |
| 7934 | MILLER | CLERK | 1300 | 1 |
| 7782 | CLARK | MANAGER | 2450 | 0 |
| 7698 | BLAKE | MANAGER | 2850 | 0.5 |
| 7566 | JONES | MANAGER | 2975 | 1 |
| 7839 | KING | PRESIDENT | 5000 | 0 |
| 7654 | MARTIN | SALESMAN | 1250 | 0 |
| 7521 | WARD | SALESMAN | 1250 | 0 |
| 7844 | TURNER | SALESMAN | 1500 | 0.67 |
| 7499 | ALLEN | SALESMAN | 1600 | 1 |
ROW_NUMBER
파티션 내 현재 행의 수를 반환한다. 행 마다 다른 값을 할당하여, 중복된 값을 반환하지 않는다. 공동 순위를 허용하지 않고, 정렬 기준이 2개 이상일 때 사용하기 좋다.
ROW_NUMBER() OVER (<partition_definition> <order_definition>)
예시 데이터로 급여 기준으로 사원 정보를 내림차순으로 정렬하되, 급여가 같은 경우 사원 번호를 기준으로 오름차순으로 정렬하면 다음과 같다.
SELECT
empno,
ename,
job,
sal,
ROW_NUMBER() OVER (ORDER BY sal DESC, empno) AS sal_rank
FROM emp
;
실행 결과
| empno | ename | job | sal | sal_rank |
| ----- | ------ | --------- | ---- | -------- |
| 7839 | KING | PRESIDENT | 5000 | 1 |
| 7788 | SCOTT | ANALYST | 3000 | 2 |
| 7902 | FORD | ANALYST | 3000 | 3 |
| 7566 | JONES | MANAGER | 2975 | 4 |
| 7698 | BLAKE | MANAGER | 2850 | 5 |
| 7782 | CLARK | MANAGER | 2450 | 6 |
| 7499 | ALLEN | SALESMAN | 1600 | 7 |
| 7844 | TURNER | SALESMAN | 1500 | 8 |
| 7934 | MILLER | CLERK | 1300 | 9 |
| 7521 | WARD | SALESMAN | 1250 | 10 |
| 7654 | MARTIN | SALESMAN | 1250 | 11 |
| 7876 | ADAMS | CLERK | 1100 | 12 |
| 7900 | JAMES | CLERK | 950 | 13 |
| 7369 | SMITH | CLERK | 800 | 14 |급여가 같은 Scott과 Ford, Ward와 Martin은 empno를 기준으로 정렬된 것을 확인할 수 있다.
참고 문서
https://jhryu1208.github.io/devlang/2020/10/25/mysql-percent_rank/
12.20.1 Window Function Descriptions