[MySQL] 순위함수

요약

목록 설명
ROW_NUMBER() 동등 순위를 인식하지 않고 매번증가되는 번호를 출력
DENSE_RANK() 순위 값 중 동등 순위 번호는 같게 나오고 그 다음 순위를 다음 번호로 출력
RANK() 순위 값 중 동등 순위 번호는 같게 나오고 그 다음 순위를 다음 번호를 뺀 그 다음 값을 출력
NTILE() PARTITION을 지정된 수 만큼의 등급으로 나누어 각 등급 번호를 출력
LEAD(expr [,offset] [,default]) / LAG() 지정된 칼럼의 이전, 이후의 행 값을 출력
FIRST_VALUE() / LAST_VALUE() 각 그룹별 첫 번째와 마지막값 하나만출력

테이블

Scores table:
+-------+----+-------+
| quart | id | score |
+-------+----+-------+
|  1/4  | 1  | 3.50  |
|  2/4  | 2  | 3.65  |
|  1/4  | 3  | 4.00  |
|  3/4  | 4  | 3.85  |
|  2/4  | 5  | 4.00  |
|  4/4  | 6  | 3.65  |
+-------+----+-------+

ROW_NUMBER(): 동등 순위를 인식하지 않고 매번증가되는 번호를 출력

SELECT
    s.score 
    ,ROW_NUMBER() OVER(ORDER BY s.score DESC) AS 'rank'
FROM Scores s 
;

+-------+------+
| score | rank |
+-------+------+
| 4.00  |   1  |
| 4.00  |   2  |
| 3.85  |   3  |
| 3.65  |   4  |
| 3.65  |   5  |
| 3.50  |   6  |
+-------+------+

DENSE_RANK : 순위 값 중 동등 순위 번호는 같게 나오고 그 다음 순위를 다음 번호로 출력

SELECT
    s.score 
    ,DENSE_RANK() OVER(ORDER BY s.score DESC) AS 'rank'
FROM Scores s 
;

+-------+------+
| score | rank |
+-------+------+
| 4.00  |   1  |
| 4.00  |   1  |
| 3.85  |   2  |
| 3.65  |   3  |
| 3.65  |   3  |
| 3.50  |   4  |
+-------+------+

RANK() : 순위 값 중 동등 순위 번호는 같게 나오고 그 다음 순위를 다음 번호를 뺀 그 다음 값을 출력

SELECT
    s.score 
    ,RANK() OVER(ORDER BY s.score DESC) AS 'rank'
FROM Scores s 
;

+-------+------+
| score | rank |
+-------+------+
| 4.00  |   1  |
| 4.00  |   1  |
| 3.85  |   3  |
| 3.65  |   4  |
| 3.65  |   4  |
| 3.50  |   6  |
+-------+------+

+ PARTITION BY: 동일 그룹으로 묶음

SELECT
    s.quart 
    ,s.score 
    ,RANK() OVER(PARTITION BY s.quart ORDER BY s.score DESC) AS 'rank'
FROM Scores s 
;

+-------+-------+------+
| quart | score | rank |
+-------+-------+------+
|  1/4  | 4.00  |   1  |
|  1/4  | 3.50  |   2  |
|  2/4  | 4.00  |   1  |
|  2/4  | 3.65  |   2  |
|  3/4  | 3.85  |   1  |
|  4/4  | 3.65  |   1  |
+-------+-------+------+

NTILE() : 지정된 수 만큼의 등급으로 나누어 각 순위를 출력

SELECT 
    NTILE(2) OVER(ORDER BY s.score DESC) AS 'group'
    ,s.score 
FROM Scores s 

+-------+-------+
| group | score |
+-------+-------+
|   1   | 4.00  |
|   1   | 4.00  |
|   1   | 3.85  |
|   2   | 3.65  |
|   2   | 3.65  |
|   2   | 3.50  |
+-------+-------+

LEAD/LAG(컬럼 [,값을 가져올 행의 위치] [,기본값]) : 다음 행의 값 찾기

SELECT
    ROW_NUMBER() OVER(ORDER BY s.score DESC) AS 'rank'
    ,s.score 
    ,s.score - (LEAD(s.score, 1, 0) OVER(ORDER BY s.score DESC)) AS 'difference'
FROM Scores s 
;

+------+-------+------------+
| rank | score | difference |
+------+-------+------------+
|   1  | 4.00  |    0.00    |
|   2  | 4.00  |    0.15    |
|   3  | 3.85  |    0.20    |
|   4  | 3.65  |    0.00    |
|   5  | 3.65  |    0.15    |
|   6  | 3.50  |    3.50    |
+------+-------+------------+

FIRST_VALUE/LAST_VALUE : 각 그룹별 첫 번째와 마지막값 하나만 출력

각 분기 1위와의 차이 출력

SELECT
    s.quart 
    ,s.score 
    ,s.score - (FIRST_VALUE(s.score) OVER(PARTITION BY s.quart ORDER BY s.score DESC)) 'difference'
FROM Scores s 
;

+-------+-------+------------+
| quart | score | difference |
+-------+-------+------------+
|  1/4  | 4.00  |    0.00    |
|  1/4  | 3.50  |   -0.50    |
|  2/4  | 4.00  |    0.00    |
|  2/4  | 3.65  |   -0.35    |
|  3/4  | 3.85  |    0.00    |
|  4/4  | 3.65  |    0.00    |
+-------+-------+------------+

links

social