[SQL] 순위 함수(MySQL, Oracle)

📌요약

윈도우 함수 중 하나인 순위 함수를 사용하면 결과 집합의 각 행에 순위를 할당할 수 있습니다.

	RANK()	DENSE_RANK()	ROW_NUMBER()
동일 값 처리	동일 순위 부여	동일 순위 부여	각 행별 고유 순위 부여
순위 간격	동일 순위있는 경우, 그 다음 순위는 이전 동일 순위를 가진 행 수만큼의 간격 발생	동일 순위가 있어도, 순위를 건너뛰지 않고(간격 없이) 연속된 순위를 부여	동일 순위가 있어도, 각 행에는 순차적으로 유니크한 순위 부여
예시	1,2,2,4,5, ...	1,2,2,3,4, ...	1,2,3,4,5, ...

 

---

1. RANK() 함수

[MySQL Tutorial]
RANK() 함수는 결과 집합의 파티션 내 각 행에 순위를 할당합니다.
행의 순위는 1에 그 앞에 오는 순위 수를 더한 값으로 지정됩니다.

[Oracle Tutorial]
RANK() 함수는 각 집합에서 값의 순위를 계산하는 분석 함수입니다.
RANK() 함수는 동일한 값을 가진 행에 대해 동일한 순위를 반환합니다. 동률 순위에 동률 행 수를 더해 다음 순위를 계산합니다. 따라서 순위는 연속된 숫자가 아닐 수도 있습니다.
RANK() 함수는 상위 N 및 하위 N 쿼리에 유용합니다.

 

RANK() 함수는 윈도우 함수 중 그룹 내 순위 함수로, 특정 윈도우(부분)내에서 순위를 할당하는 데 사용합니다.

동일한 값을 가진 행에 대해 동일한 순위를 부여하고 그 다음 순위는 동일 순위를 가진 행 수만큼의 시퀀스 간격이 생깁니다.

예를 들어 3위가 3명이라면, 1,2,3,3,3,6,7,8, ... 이렇게 순위가 매겨집니다.

 

RANK() 문법

MySQL과 Oracle의 RANK() 함수 문법은 각각 아래와 같습니다.

MySQL 

RANK() OVER (
	PARTITION BY <expression>[{,<expression>...}]
	ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

Oracle

RANK() OVER ([query_partition_clause] order_by_clause)

• PARTITION BY | query_partition_clause : 결과 집합을 파티션으로 나눕니다.
  • 옵션 값이며, 생략할 경우 조회 결과의 모든 행을 하나의 파티션으로 취급합니다.
• ORDER BY | order_by_clause : 하나 이상의 열이나 표현식을 기준으로 파티션 내의 행을 정렬합니다.
  • 필수 값이며, 함수가 적용되는 각 파티션의 행의 논리적인 정렬 순서를 결정합니다.

 

2. DENSE_RANK() 함수

[MySQL Tutorial]
DENSE_RANK() 함수는 순위 값에 차이 없이 파티션 또는 결과 집합 내의 각 행에 순위를 할당하는 윈도우 함수입니다.
행의 순위는 행 앞에 오는 고유한 순위 값에서 1씩 증가합니다.

[Oracle Tutorial]
DENSE_RANK() 함수는 정렬된 행 집합에서 행의 순위를 계산하는 분석 함수입니다.
RANK() 함수와 달리 DENSE_RANK() 함수는 순위 값을 연속된 정수로 할당합니다. 동률인 경우에는 순위를 건너뛰지 않습니다.
순위 기준 값이 동일한 행에는 동일한 순위 값이 적용됩니다. 

 

DENSE_RANK() 함수는 RANK() 함수와 동일하게 동일한 값을 가진 행에 대해 동일한 순위를 부여하지만 그 다음에 나오는 행에는 순위를 건너뛰지 않고 이어서 순위를 부여합니다.

예를 들어 3위가 3명이라면, 1,2,3,3,3,4,5,6, ... 이렇게 순위가 매겨집니다.

DENSE_RANK() 문법

MySQL과 Oracle의 DENSE_RANK() 함수 문법은 각각 아래와 같습니다.

MySQL 

DENSE_RANK() OVER (
    PARTITION BY partition_expression
    ORDER BY sort_expression [ASC|DESC]
)

Oracle

DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)

 

PARTITION BY | query_partition_clause 과 ORDER BY | order_by_clause는 RANK() 함수에서의 설명과 같습니다.

DENSE_RANK()는 파티션에 동일한 순위 값을 가진 두 개 이상의 행이 있는 경우, 각 행에 동일 순위가 부여되고, RANK()함수와는 달리 항상 연속된 순위 값을 할당 합니다. (동일 순위가 있다하더라도)

 

3. ROW_NUMBER() 함수

[MySQL Tutorial]
ROW_NUMBER() 함수는 결과 집합의 각 행에 일련 번호를 할당하는 윈도우 함수 혹은 분석 함수입니다.
첫번째 숫자는 1로 시작합니다.

[Oracle Tutorial]
ROW_NUMBER() 함수는 적용되는 각 행(파티션의 각 행 또는 결과 집합의 각 행)에 순차적으로 고유한 정수를 할당하는 분석 함수입니다.

 

ROW_NUMBER() 함수는 결과 집합 내의 각 행에 고유 번호를 할당합니다.

RANK()나 DENSE_RANK() 함수보다 매우 간단합니다.

ROW_NUMBER() 함수는 각 행에 순위를 부여할 때 동일한 값이 있어도 이를 무시하고 연속적인 순위를 부여합니다.

예를 들어 3위가 3명이라해도, 1,2,3,4,5,6, ... 이렇게 순위가 매겨집니다.

 

ROW_NUMBER() 문법

MySQL과 Oracle의 RANK() 함수 문법은 각각 아래와 같습니다.

MySQL 

ROW_NUMBER() OVER (
    PARTITION BY <expression>,[{,<expression>}...]
    ORDER BY <expression> [ASC|DESC],[{,<expression>}...]
)

Oracle

ROW_NUMBER() OVER (
   [query_partition_clause] 
   order_by_clause
)

PARTITION BY | query_partition_clause 과 ORDER BY | order_by_clause는 RANK() 함수에서의 설명과 같습니다.

 

4. RANK() vs DENSE_RANK() vs ROW_NUMBER()

지금까지 알아본 3가지 윈도우 함수인 RANK(), DENSE_RANK(), ROW_NUMBER()의 실제 결과값의 차이를 확인해보겠습니다.

 

학생 정보가 담긴 테이블을 간단하게 만들고, 학생별 평점으로 각 순위 함수의 차이를 확인해보겠습니다.

CREATE TABLE `students` (
  `student_id` int NOT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `major` varchar(100) DEFAULT NULL,
  `gpa` decimal(3,2) DEFAULT NULL, // 평점
  PRIMARY KEY (`student_id`)
)

적당한 데이터를 여러개 INSERT 한 후 아래 쿼리를 실행시켜 보겠습니다.

SELECT student_id , first_name , gpa 
		, RANK() OVER (ORDER BY gpa DESC) AS 'RANK'
		, DENSE_RANK () OVER (ORDER BY gpa DESC) AS 'DENSE_RANK'
		, ROW_NUMBER() OVER (ORDER BY gpa DESC, first_name ASC) AS 'ROW_NUM'
FROM students;

실행시킨 결과는 아래와 같습니다.

student_id	first_name	gpa	RANK	DENSE_ RANK	ROW_NUM
3	예린	3.95	1	1	1
2	민지	3.90	2	2	2
8	지우	3.90	2	2	3
6	소민	3.85	4	3	4
4	지현	3.85	4	3	5
5	민준	3.80	6	4	6
10	영희	3.80	6	4	7
9	승현	3.75	8	5	8
7	준호	3.75	8	5	9
1	민수	3.7	10	6	10

 

3,4번 행을 보면, 3번 행의 지우와 4번 행의 소민이의 순위 값이 각 함수별로 다른 것을 확인할 수 있습니다.

그리고 각 함수별로 순위도 다르게 매겨지는 것을 알 수 있습니다.

• RANK: 1 ⭢ 2 ⭢ 4 ⭢ 6 ⭢ 8 ⭢ 10
• DENSE_RANK: 1 ⭢ 2 ⭢ 3 ⭢ 4 ⭢ 5 ⭢ 6
• ROW_NUM: 1 ⭢ 2 ⭢ 3 ⭢ 4 ⭢ 5 ⭢ 6 ⭢ 7 ⭢ 8 ⭢ 9 ⭢ 10

 

5. PARTITION BY  문법

추가로 세가지 함수에서 모두 옵션 값으로 사용되는 파티션 구문에 대한 사용법을 알아보겠습니다.

PARTITION BY는 그룹 내 순위 및 그룹별 집계를 구할 때 사용합니다.

SELECT student_id , major , gpa 
	,RANK() OVER (PARTITION BY major ORDER BY gpa DESC) AS 'PARTITION'
FROM STUDENTS;

 

이 쿼리는 학생들의 전공(major)별로 학생들의 평점(gpa)순위를 매깁니다.

• PARTITION BY major ORDER BY gpa DESC: major 컬럼이 같은 행을 그룹으로 묶어서, 그룹 내 gpa 컬럼의 순위를 내림차순(DESC)으로 정렬

위 쿼리를 실행한 결과는 아래와 같습니다.

student_id	major	gpa	PARTITION
1	경영학	3.6	1
8	경영학	3.5	2
7	경영학	3.45	3
4	경영학	3.4	4
9	생물학	3.2	1
6	생물학	3.05	2
3	심리학	3.85	1
5	심리학	3.85	1
2	심리학	3.75	3
10	심리학	3.55	4

 

---

마치며,

윈도우 함수 중 하나인 순위 함수에 대해 알아보았습니다.

코딩테스트를 하면서 RANK() 함수를 활용해야하는 쿼리를 제대로 작성하지 못해 다시 공부하면서 정리하니 전에 공부했던 내용이 다시 생각나기도 하고 새롭게 다시 배우기도 했습니다.

관련한 다른 함수들도 많이 있으니, 다음에는 이외 함수들도 정리하는 시간을 가져보아야겠습니다 :)

 

참고 링크

A Guide to MySQL RANK Funtion By Practical Examples

Oracle RANK() Function By Practical Examples