[SQL] RANK, DENSE_RANK, ROW_NUMBER의 차이

반응형

이번엔 SQL을 이용해 데이터를 추출하는 과정에서,

특정 기준별로 데이터를 정렬해 순번을 매겨주는 세 명령어간의 차이점을 알아보려 한다.

주어진 데이터가 중복 없이 이루어져있다면 위 3가지 중 어느걸 쓰건 무관하나,

중복이 존재하는 경우엔 이 3가지의 결과가 천차만별이 되기에 잘 판단해서 사용해야 한다.

---

먼저 위 명령어들의 사용 형태를 간단하게 적어놓았다.

SELECT RANK() OVER (
    PARTITION BY COLUMN_1, COLUMN_2, ...
    ORDER BY COLUMN_A, COLUMN_B, ...
)
FROM TABLE_NAME
...

RANK 말고도 DENSE_RANK와 ROW_NUMBER 역시 위와 같은 동일한 형태의 구문을 취한다.

PARTITION BY에 들어가는 COLUMN은 어떤 COLUMN의 구간별로 잘라서 나타낼 것인가를 적어주면 된다.

ORDER BY는 WHERE 절 이후 오는 ORDER BY와 동일한 기능을 수행한다. (정렬기능)

따라서 ORDER BY에 DESC를 붙여주는 것도 가능하다.

참고로 저 상태로 컬럼을 쓰면 귀찮으니까 AS 뒤에 순번, 번호, NO 등을 붙여서 주로 사용하기도 한다.

 

이렇게 글로만 적으면 막연하기에, 예제 데이터를 토대로 직관적으로 세 가지 기능의 차이점을 보도록 하자.

---

샘플 데이터.

먼저 위와 같은 샘플데이터가 있다고 하자.

목표는 해당 데이터에서 같은 이름 내에서 나이가 많은 순서대로 순번을 매길 것이다.

간단하게 데이터를 살펴보면, 이름이 이순신인 데이터와 강감찬인 데이터는 크게 문제가 없다.

그러나, 신사임당인 데이터의 경우 AGE가 32인 데이터 2개 있는 것을 알 수 있다.

이 데이터를 처리하는 과정이 위 3가지 기능의 차이라 볼 수 있다.

 

RANK, DENSE_RANK, ROW_NUMBER 순서대로 확인해보도록 하자.

 

RANK를 적용했을 때 결과.

먼저 RANK를 적용했을 때 결과는 위와 같다.

순번이라는 항목을 보면, 신사임당 데이터를 기준으로 AGE가 32인 데이터는 모두 1로 계산된다.

그 이후 AGE=29 데이터는 중복 개수만큼 고려한 다음 순번 3이 계산된다.

만일, AGE 32인 데이터가 10개 있으면, AGE 29 데이터의 순번은 11로 매겨질 것이다.

 

DENSE_RANK를 적용했을 때 결과.

그 다음 DENSE_RANK를 적용한 결과이다.

앞선 RANK와 동일하게, AGE 32인 데이터끼리는 똑같이 순번 1로 매겨진다.

그러나, 그 이후 데이터 AGE 29의 경우, 바로 연번 2가 오는 것을 알 수 있다.

즉, DENSE_RANK는 중복 데이터가 몇 개건 상관없이 그 다음 데이터는 순서대로 순번을 매기게 된다.

 

ROW_NUMBER를 적용했을 때 결과

마지막으로 ROW_NUMBER를 적용한 결과이다.

앞선 두 기능과 다르게, ROW_NUMBER는 중복된 데이터가 있더라고 해도

이에 상관없이 임의로 순번을 매겨주는 것을 알 수 있다.

즉, 동일 PARTITON BY 이내에서 순번이 다른 경우가 없는 것을 확인 가능하다.

---

위 세 명령어들의 차이를 다시 요약하면 다음과 같다.

 

1. RANK: 중복된 데이터끼리는 동일 순위, 그 이후 데이터의 순번은 중복 수 만큼 건너뛰고 매기기
2. DENSE_RANK: 중복된 데이터끼리는 동일 순위, 그 이후 데이터의 순번은 중복 무관 연달아서 매기기
3. ROW_NUMBER: 중복된 데이터끼리도 순위를 임의로 매기기 (중복된 데이터끼리도 다른 순위)

아마 특별한 경우가 아닌 이상 대부분의 SQL에서 위 세 기능을 사용할 수 있는 걸로 안다.

MySQL, Oracle, MS-SQL, 심지어 Sybase까지도 되는걸로 알고 있다.

따라서 SQL 종류에 상관없이 위 기능을 이용해서 기준에 따른 상위 n개 데이터를 추출하는 구문도 자유롭게 만들 수 있다.

 

참고로 위 세 기능들은 SQLD(개발자)나 SQLP(전문가) 자격증 문제에서도 심심하면 출제되는 요소이다.

실무에서도 종종 쓰이니 꼭 알아두도록 하자.

(물론 실제로는 중복까지 고려해서 정렬하기에 어지간해선 ROW_NUMBER만 쓰더라)