특정 열의 값에 대해 순위를(랭킹을) 매기기 위해서 다음과 같은 함수를 사용해볼 수 있습니다.
- RANK
- DENSE_RANK
- ROW_NUMBER
- NTILE
기본적인 함수 형식은 아래과 같습니다.
RANK() OVER ( [PARTITION BY colName1] ORDER BY colName2 [DESC] )
DENSE_RANK() OVER ( [PARTITION BY colName1] ORDER BY colName2 [DESC] )
ROW_NUMBER() OVER ( [PARTITION BY colName1] ORDER BY colName2 [DESC] )
NTILE() OVER ( [PARTITION BY colName1] ORDER BY colName2 [DESC] )
# [대괄호] 안의 값은 선택사항입니다
이렇게 보면 외계어같지만 실제로는 사용이 쉬운 함수들 입니다. (진짜루요)
제가 갖고 있는 데이터베이스에서 Employee의 사번, 이름, 젠더, 샐러리(연봉) 4개를 저장한 임시 테이블 TEMP를 가지고 차례차례 함수를 적용시켜 보겠습니다.
1. RANK()
RANK()함수는 내가 저장한 행(column)에 순위를 매겨서 정렬한 결과값을 보여주는 함수입니다.
SELECT emp_no,
CONCAT(first_name, ' ', last_name) AS full_name,
salary,
RANK() OVER(ORDER BY SALARY DESC) AS `rank`
FROM TEMP;
위의 쿼리에는 SALARY 행 내림차순을 기준으로 랭킹함수를 적용해서 rank라는 새로운 이름의 행(column)을 반환하도록 했습니다.
결과를 살펴봅시다. 잘 보시면 rank 행에 128등이 두 명입니다. 이유는 두 명의 salary 값이 중복되기 때문인데요. 이렇게 RANK() 함수에서는 tie : 중복값이 있는 데이터끼리는 같은 등수를 쉐어하게 됩니다. 그리고 그 등수를 쉐어하는 만큼 그 다음 등수는 밀려서 사라지게 되는데요. 128등이 두 명 오고 난 다음에 129등 없이 130등으로 시작하는 것을 보면 알 수 있습니다. 만약 128등이 3명이었다면 그 다음 등수는 131등으로 시작하겠죠.
2. DENSE_RANK()
DENSE_RANK() 함수는 RANK()와 거의 비슷하지만 약간 다릅니다.
SELECT emp_no,
CONCAT(first_name, ' ', last_name) AS full_name,
salary,
DENSE_RANK() OVER(ORDER BY SALARY DESC) AS `rank`
FROM TEMP;
DENSE_RANK()에서는 같은 값, 중복값의 존재나 갯수와 관계없이 무조건 1씩 차례로 증가합니다. 랭킹이 밀리지 않아요. 127등이 두 명 있다고 그 다음 등수가 129로 시작하지 않고, 128으로 시작하는 것을 보면 알 수 있어요.
예를 들어 올림픽에서 금메달을 2명이 공동 수상한다고 가정을 했을 때, 금메달이 2명이라고 은메달은 아무도 주지 않고 동메달을 주면 안 되잖아요? 금메달을 받은 사람의 수와 관계 없이 은메달도 반드시 준다, 라고 보면 됩니다. (금메달 공동 수상이 실제로 가능한지는.... 저도 모르지만요...)
그런데 보니까 아까 RANK()에서는 128등이 두 명이었는데 이번엔 127등이 두 명이네요, 왜 그럴까요? 데이터 이미지 중간에 생략된 부분에서 같은 값을 가지는 tie가 두 명 있었습니다. 그래서 RANK()는 숫자가 하나 밀려서 128등 두 명이 되었고, DENSE_RANK()는 숫자가 밀리지 않아서 127등 두 명이 되었어요.
3. ROW_NUMBER()
ROW_NUMBER() 함수는 기준에 따라 랭킹 정렬을 하되, 중복값과 관계 없이 무조건 1부터 차례대로 행 번호를 매겨 반환합니다.
SELECT emp_no,
CONCAT(first_name, ' ', last_name) AS full_name,
salary,
ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS `rank`
FROM TEMP;
보시는 것처럼 같은 값이 있던 말던 무조건 1부터 시작해서 하나씩 줄번호를 매겨서 반환합니다.
직관적인 예시로 출석 번호나 키번호를 생각해볼 수 있을 것 같아요. 학급에 동명이인이 있다고 둘이 같은 출석번호를 쉐어하지는 않죠. 키가 같다고 키번호를 똑같이 쓰지도 않을 거구요. 이런 상황에서 ROW_NUMBER() 함수를 사용하면 되겠습니다.
4. NTILE()
NTILE 함수는 랭킹을 매기되, 내가 지정한 블럭 갯수만큼 구간을 나누어 등급 랭킹을 부여합니다.
SELECT emp_no,
CONCAT(first_name, ' ', last_name) AS full_name,
salary,
NTILE(5) OVER(ORDER BY SALARY DESC) AS `rank`
FROM TEMP;
위의 코드에서 NTILE(5)와 같이 NTILE 함수 안에 정수를 넣어 줬는데요. 이 말은 1, 2, 3, 4, 5등 구간으로 나누어 5개의 등급을 매겨 랭킹을 반환하라는 뜻이에요.
데이터 갯수가 많아 이렇게 초반부에는 1등급만 보이지만
샐러리를 내림차순으로 정렬한 다음 5개의 등급으로 나누어 랭킹이 1부터 5까지 차례대로 부여가 되었습니다.
5. PARTITION BY 추가 응용
그럼 PARTITION BY는 어떻게 활용할 수 있을까요?
SELECT emp_no,
CONCAT(first_name, ' ', last_name) AS full_name,
salary,
RANK() OVER(PARTITION BY emp_no ORDER BY SALARY DESC) AS `rank`
FROM TEMP;
제가 가지고 있던 TEMP 테이블에는 같은 사람의 샐러리가 매년 업데이트 되며 누적되어 있어서, 이렇게 이름에 따라서 샐러리 값을 여러 열(row)이 저장 되어 있었어요. OVER() 내부의 시작 부분에 PARTITION BY emp_no 를 추가해 주면서 직원 번호에 따라 파티션을 나누고 그 파티션 내부에서 랭킹을 매긴 값을 반환받았습니다. 이를 통해 직원별로 샐러리가(연봉이) 얼마나 상승했는지를 한 눈에 알아볼 수 있게 되었습니다.
