[SQL] 윈도우함수 rank, dense rank, row_num

윈도우 함수 한 눈에 보기

RANK(), DENSE_RANK(), ROW_NUMBER 이 세 함수는 MySQL 8.0 이상 버전부터 윈도우 함수(Windows Function)로 전부 사용 가능하다. 각 함수는 비슷해 보이지만 미묘한 차이가 있어서 정리 해 보려고 한다.

이 세 함수는 무조건 OVER(ORDER BY ...) 절과 함께 써야 한다. ORDER BY 없이 쓰면 MySQL은 에러를 낸다. 왜냐하면 이 함수들의 역할은 "정렬된 기준에 따라 순서를 부여"하는 것이기 때문이다. 기준 정렬이 없으면 순위를 매길 수가 없다.

예를 들면,

-- ❌ 잘못된 사용 예 (에러 남)
SELECT 
  ENAME,
  ROW_NUMBER() OVER () AS RN
FROM EMPLOYEES;

→ 오류 발생: ROW_NUMBER() requires an ORDER BY clause.

올바른 사용예

SELECT 
  ENAME,
  ROW_NUMBER() OVER (ORDER BY SAL DESC) AS RN
FROM EMPLOYEES;

→ 이건 잘 실행된다. WHY? SAL을 기준으로 정렬하고 그 순서대로 번호를 매기는 것이기 때문이다.

💡 참고로 PARTITION BY는 선택사항이다.

• ORDER BY는 반드시 필요하다.
• PARTITION BY는 그룹별로 나누고 싶을 때만 사용하면 된다.

1. RANK()

• RANK 함수는 ORDER BY를 포함한 쿼리문에서 특정 항목에 대한 순위를 구하는 함수이다.
• 꼭 OVER (ORDER BY...) 구문이 필요하다.
• 이 때 특정범위 (PARTITION BY) 도 같이 쓰면 그룹별도 순위를 매길 수도 있다.
• 동점자에게는 같은 순위를 부여하지만, 그 다음 순위는 건너뛴다.
• 예) 1, 2, 2, 4

SELECT 
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank_num
FROM students;

2. DENSE_RANK()

• DESNE_RANK 함수는 동일한 순위를 하나의 건수로 취급한다.
• 동점자에게 같은 순위를 부여하지만, 그 다음 순위는 건너뛰지 않는다.
• 이건 함수 이름 그대로 촘촘한 랭킹이라고 외우면 쉽다.
• 예) 1, 2, 2, 3

3. ROW_NUMBER()

• 동일한 값이 있어도 고유한 순위를 부여한다.
• 무조건 순번을 1, 2, 3, ... 처럼 하나씩 증가한다.
• 중복 업시 고유한 순번을 부여하고 싶을 때 좋다. 
• 동일한 값의 순위를 정해야 할 때 오라클의 경우 ROWID가 적은 행이 먼저 나온다. 
• 만약 동일 값에 대한 순서까지 관리하고 싶으면 ROW_NUMBER() OVER (ORDER BY SAL DESC, ENAME)을 같이 ORDER BY 절을 이용해 추가적인 정렬기준은 정의해야한다. 

SELECT 
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

4. partition

좋아! RANK()랑 DENSE_RANK()를 PARTITION BY랑 같이 사용할 때 어떻게 작동하는지 다음 예시를 살펴 보자

예시 테이블 : EMPLOYEES

SELECT 
  ENAME,
  JOB,
  SAL,
  RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) AS RANKING,
  DENSE_RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) AS DENSE_RANKING
FROM EMPLOYEES;

📊 결과

이미지 출처

• https://medium.com/@omniview/row-number-vs-rank-vs-dense-rank-bb9af91b75c4