postgres 에서는 mysql과 비슷하게 특정한 값을 기준으로 순위를 매길 수 있는 rank와 dense_rank 함수를 제공합니다. 이 글에서는 rank 함수를 알아봅니다.
- rank [현재글]
- dense_rank
rank 를 구하는 문제
순위를 구한다는 것은 어떤 것을 의미할까요? 어떤 값을 기준으로 1위부터 꼴지까지 줄을 세우는 것을 의미해요. 예를 들어서, 득표수가 높은 순으로 구할 수도 있을 겁니다. 혹은, 얼마나 100m 달리기 기록이 빠른지를 가지고 매길 수도 있어요. 아니면, 누적 경험치가 높은 순으로 랭킹을 매길 수도 있을 거에요. 이러한 것들은, order by절 뒤에 써 주게 됩니다.
그런데, 나라별로, 혹은 직업별로 랭킹을 구하고 싶을 때도 있을 거에요. 이 경우, 랭킹을 구하는 그룹을 나눈 것이지요? partition 되었다고 합니다.1 즉, partition by절 뒤에 써 주시면 됩니다. 정리해 볼까요?
- order by
- 어떤 값을 기준으로 랭킹을 매길 건가요?
- partition by
- 어떤 기준으로 그룹으로 묶어서 랭킹을 구하고 싶나요?
예를 들어, 아래 데이터를 생각해 봅시다.
id와 score, 그리고 반이 있어요. 각 학생에 대한 특정 과목의 점수들을 나타낸 테이블인가 봅니다. 여기서, 하고 싶은 것. score를 많이 받은 순으로, 랭킹을 구하고 싶어요. 그러면 어떤 값을 기준으로 랭킹을 매긴 건가요? score요. 많이 받은 게 먼저 나와야 하기 때문에, desc로 정렬하면 되겠습니다.
노란색으로 칠한 부분이 랭킹을 매기는 기준이 된 셈입니다. 그러면, rank는 어떻게 나올까요? 1번 id가 1, 3번 id가 2, 2번 id가 3, 4번 id가 4가 나옵니다. 랭킹을 매기는 기준이 점수 내림차순이라 그렇습니다. asc를 붙였다면, 역순으로 랭킹이 매겨질 겁니다.
결과는 위와 같이 나옵니다. 그런데요. 반별로 랭킹을 구하고 싶어요. 그러면, 그룹을 나누는 기준이 어떻게 되나요? class가 됩니다. 따라서, partition by class가 들어가겠네요. 이 경우, 어떻게 수행이 되는가? 먼저, 반별로 데이터를 group화 시킵니다.
반별로 나눈 후에, 랭킹을 구하게 됩니다. 예를 들어, id가 4번인 것은 1반의 2등입니다. id가 3번인 것은 2반의 1등입니다. 따라서, rank는 아래와 같이 매겨질 겁니다.
여기까지 별로 어렵지 않지요? rank를 매기는 기준과, 그룹별로 rank를 구한다는 개념을 잘 이해하셔야 합니다. 그래야 밑의 내용이 이해가 될 겁니다.
postgres rank 함수 사용해 보기
그러면, 한 번 사용해 보도록 하겠습니다. 먼저, 데이터는 아래와 같습니다.
score 테이블에는 점수와 이름, 그리고 부서가 담겨져 있습니다. rank 함수를 쓸 때에는 아래와 같이 쓰게 됩니다.
rank() over (partition by A order by B)
B를 기준으로 rank를 매기는데, A를 기준으로 grouping 하고 싶다.
당연하게도, 랭킹을 매길 때 어떤 것을 기준으로 매길지 정하는 것은 필수이므로, 회색으로 칠한 부분은 필수라고 봐도 되겠습니다. 그룹별로 묶어서 ranking 집계를 하고 싶은 경우, 앞에 partition by가 들어갑니다. 이건 선택이라 봐도 되겠지요.
그러면 1번 쿼리를 보겠습니다. 이 쿼리는, 뒤에 order by score desc가 들어갔어요. 무슨 이야기일까요?
- score 기준으로 랭킹을 매기는데
- desc래요. 내림차순으로 매긴다는 이야기입니다.
따라서, score가 높은 것이 랭킹이 더 높을 것이고, 그렇지 않으면 낮겠네요. 결과를 볼까요?
결과는 위와 같습니다. 보면, score가 50인 사람이 1등, 20인 사람이 5등으로 잡히고 있어요. 그런데, 여기서 부서별로 순위를 구하고 싶어졌어요. 예를 들어, a 부서의 1등, b 부서의 2등을 구하고 싶어요. 어떻게 해야 할까요?
각 부서별 점수 순위니까, partition이 되는 기준이 부서가 되는 것이지요?
2번 쿼리를 보겠습니다. order by 앞에 partition by가 붙었습니다. 이러면, depart 별로 나누어서 랭킹을 구하게 됩니다.
결과를 보면, ga와 hui는 a 그룹의 1위, 2위가 됩니다. 다음, cho, cho2는 b 그룹의 1위, cho3은 b번 그룹의 3위가 됩니다. 중요한 것은 그룹별로 순위가 매겨졌기 때문에, hui가 5위가 아닌 2위가 된다는 점입니다.
- 쉽게 말하면, 각 나라별 부자 순위라고 했을 때, 나라가 partition의 기준이 되는 것입니다. ↩︎