MySQL 순위 함수

May 31, 2024

Contents

1. ROW_NUMBER 2. RANK 3. DENSE_RANK 셋의 차이점 4. NTILE 5. PARTITION BY 5.1. 그룹 내에서 순위 계산하기 5.2. 그룹 별 평균 계산하기 5.3. 이전 값과의 차이 계산하기

1. ROW_NUMBER

각 행에 고유한 순위를 부여하며, 동일한 값이 있어도 각 행에 연속적인 순위를 부여한다.

👉 순위의 중복 허용 ❌


SELECT 
    ID, 
    SIZE, 
    ROW_NUMBER() OVER (ORDER BY SIZE) AS ROW_NUMBER
FROM TABLE;


ID | SIZE | ROW_NUMBER
------------------------------
1  | 10   | 1
2  | 20   | 2
3  | 20   | 3
4  | 30   | 4
5  | 40   | 5

2. RANK

동일한 값이 있는 경우 같은 순위를 부여하며, 다음 순위는 건너뛴다.

👉 순위의 중복 허용 ⭕


SELECT 
    ID, 
    SIZE, 
    RANK() OVER (ORDER BY SIZE) AS RANK
FROM TABLE;


ID | SIZE | RANK
------------------------------
1  | 10   | 1
2  | 20   | 2 
3  | 20   | 2 
4  | 30   | 4 ⭐
5  | 40   | 5

3. DENSE_RANK

동일한 값이 있는 경우 같은 순위를 부여하지만, 다음 순위를 건너뛰지 않는다.

👉 순위의 중복 허용 ⭕


SELECT 
    ID, 
    SIZE, 
    DENSE_RANK() OVER (ORDER BY SIZE) AS DENSE_RANK
FROM TABLE;


ID | SIZE | DENSE_RANK
------------------------------
1  | 10   | 1
2  | 20   | 2 
3  | 20   | 2 
4  | 30   | 3 ⭐
5  | 40   | 4

셋의 차이점


ID | SIZE | ROW_NUMBER | RANK | DENSE_RANK
-------------------------------------------
1  | 10   | 1          | 1    | 1
2  | 20   | 2          | 2    | 2
3  | 20   | 3          | 2    | 2
4  | 30   | 4          | 4    | 3
5  | 40   | 5          | 5    | 4

ROW_NUMBER	RANK	DENSE_RANK
고유의 순위	같은 값은 중복 순위, 다음 값은 건너뛴다.	같은 값은 중복 순위, 다음 값을 건너뛰지 않는다.

4. NTILE

데이터 집합을 n개의 동일한 크기 그룹으로 나누고, 각 행에 그룹 번호를 부여 한다. 주로 순위를 그룹화하여 구할 때, 즉 상위 % 구할 때 사용할 수 있다.

👉 순위의 중복 허용 ⭕


SELECT 
    ID, 
    SIZE, 
    NTILE(4) OVER (ORDER BY SIZE) AS NTILE
FROM TABLE;


ID | SIZE | NTILE
------------------------------
1  | 10   | 1
2  | 20   | 2
3  | 30   | 3
4  | 40   | 4

❔ 만약 데이터는 5개의 행이지만, 그룹은 4개로 나눈다면?

NTILE은 데이터 집합을 지정된 수의 동일한 크기 그룹으로 나누는 윈도우 함수다. 하지만 동일한 크기로 나눌 수 없다면, 가능한 한 동일한 크기로 나눈다.


NTILE(4) OVER (ORDER BY SIZE) AS NTILE

ID | SIZE | NTILE
------------------------------
1  | 10   | 1
2  | 20   | 2
3  | 20   | 2
4  | 30   | 3
5  | 40   | 4

➡️ ID 2, 3은 같은 SIZE이지만, NTILE이 다르다.

총 5개의 행을 4개의 그룹으로 나누려면, 1개의 그룹 = 2개의 행 / 나머지 그룹 = 1개의 행을 가진다.

첫 번째 그룹: ID 1 (한 행)

두 번째 그룹: ID 2, 3 (두 행)

세 번째 그룹: ID 4 (한 행)

네 번째 그룹: ID 5 (한 행)

이렇게 나누는 이유는 가능한 한 균등한 크기의 그룹으로 생성하려고 하기 때문이다. SIZE의 값은 10, 20, 30, 40이 있고 4개의 그룹으로 나누면 10/20/30/40으로 나눌 수 있다. 이에 따라 각 그룹 번호가 할당 된다.

5. PARTITION BY

순위를 매길 때 모든 값이 아닌 그룹 별 로 순위를 매기고 싶을 때 사용한다.

5.1. 그룹 내에서 순위 계산하기

TABLE에서 TYPE으로 그룹화하고, 각 그룹 내에서 VALUE를 기준으로 내림차순으로 순위를 매긴다.


SELECT
    ID,
    TYPE,
    VALUE,
    ROW_NUMBER() OVER (⭐ PARTITION BY TYPE ORDER BY VALUE DESC) AS RANK_WITH_GROUP
FROM
    TABLE;

5.2. 그룹 별 평균 계산하기

TABLE에서 YEAR, MONTH으로 그룹화하고, 각 그룹 내에서 VALUE의 평균을 계산한다.


SELECT
    YEAR,
    MONTH,
    AVG(VALUE) OVER (PARTITION BY YEAR, MONTH) AS AVG_VALUE_PER_MONTH
FROM
    TABLE;

5.3. 이전 값과의 차이 계산하기

TABLE에서 TYPE으로 그룹화하고, 각 그룹 내에서 ID를 기준으로 정렬하여 각 값과 이전 값과의 차이를 계산한다.


SELECT
    ID,
    VALUE,
    VALUE - LAG(VALUE) OVER (PARTITION BY TYPE ORDER BY ID) AS DIFF_WITH_PREVIOUS_VALUE
FROM
    TABLE;

❔ LAG( )

현재 행의 바로 위의 행에 대한 값을 가져온다. 바로 위의 행의 값을 가져와서 현재 행과 비교하거나 바로 위의 행의 값과의 차이를 계산하는 데 사용한다.

보통 OVER 절과 함께 사용한다.


SELECT
    ID,
    VALUE,
    LAG(VALUE) OVER (ORDER BY ID) AS PREVIOUS_VALUE
FROM
    TABLE;


ID | VALUE | PREVIOUS_VALUE
---------------------------
1  | 10    | NULL
2  | 20    | 10 (= 이전 ID 1)
3  | 15    | 20 (= 이전 ID 2)
4  | 25    | 15 (= 이전 ID 3)

See more posts

MySQL 순위 함수

1. ROW_NUMBER

2. RANK

3. DENSE_RANK

셋의 차이점

4. NTILE

❔ 만약 데이터는 5개의 행이지만, 그룹은 4개로 나눈다면?

5. PARTITION BY

5.1. 그룹 내에서 순위 계산하기

5.2. 그룹 별 평균 계산하기

5.3. 이전 값과의 차이 계산하기

More articles

[백준/Java] 2884번 알람시계 (조건문)

MySQL 010-0000-0000 처럼 전화번호에 하이픈(-) 넣기

MySQL NULL 처리 (IFNULL, CASE, COALESCE)

[프로그래머스] Lv.3 대장균들의 자식의 수 구하기 (OUTER JOIN)