[MYSQL] PERCENTAGE_RANK 메서드(RANK 함수들), ORDER BY(다중정렬)

코딩 테스트를 하면서 집계함수 중 Percentage_rank라는 메서드를 발견을 하게 되었다.

 

필자는 SQL로 맛보는 데이터 전처리 분석(노수영 지음) 책을 가지고 SQL을 공부를 했었는데 여기서는 등수를 나타내는 함수를 3개로 표현을 했다.

• RANK()
• ROW_NUMBER()
• DENSE_RANK()

이 3가지 였다. 그렇기에 코딩 테스트 문제를 풀다가 %에 대한 rank를 나타내려고 했을 때 일단 rank로 순위를 준 후 그 값들을 제일 큰값에서 나누는 굉장히 힘들고 깨끗하지 못한 코드로 풀고 있엇다.ㅠㅠㅠ

 

그러다가 친구가 알려주면서 나의 편견이 깨지고 문제를 쉽고 간단하게 풀게 되었다. 

---

RANK() 함수 종류

그렇기에 오늘은 각종 RANK함수에 대해서 알아보자.

• RANK() : RANK() 함수는 우리가 원하는 column을 선택하면 크기에 따라 순위를 지정한다. 만약 같은 값을 갖는다면 동일한 순위를 주고 다음 순위에 동일한 순의만큼의 시퀀스를 부여한다. 

select
	*
    , RANK() over (order by amount DESC) AS RNK
from sales;

• ROW_NUMBER() : ROW_NUMBER() 함수는 우리가 원하는 column을 선택하면 크기에 따라 순위를 지정하는데 만약 같은 값을 갖는 행이 있어도 고유한 다른 순위를 지정해서 준다. 즉, 각 행마다 고유한 랭크가 부여한다.

select
	*
    , ROW_NUMBER() over (order by amount DESC) AS RNK
from sales;

•  DENSE_RANK() : RANK()함수와 비슷하지만 만약 같은 값을 같은 행이 존재한다면 같은 순위를 주지만 그 다음 순위에 간격만큼 시퀀스를 띄우지 않고 바로 다음 숫자의 순위를 부여한다.

select
	*
    , DENSE_RANK() over (order by amount DESC) AS RNK
from sales;

• PERCENTAGE_RANK() : PERCENTAGE_RANK() 함수는 결과 잡합 내 행의 백분위수 순위를 계산하는 상대 순위 함수이다. 지정된 순서 열에 있는 모든 값 중에서 현재 행 값의 상대적 위치를 나타내는 0과 1 사이의 값을 반환한다. 계산은 순위를 기준으로 하지만 0과 1 사이에서 정규화 된다. 
  • 간단하게 설명하면 순위를 매긴 후에 그 순위에 따라 0과 1 사이에서 모든 값들 중에 상위 몇 프로에 해당하는지에 대해 알려주는 메서드이다.

 

위 코드에 대한 결과값

• RANK함수들 안에 들어가는 PARTITION BY의 사용
  • PARTITION BY는 만약 우리가 여러과목들에 시험점수 데이터가 있을 때 과목별로 나누어서 순위를 정해주고 싶을 때 PARTITION BY을 사용함으로써 과목별로 순위를 따로 지정을 해줄 수 있다. 이것은 예시를 보자.

과목	점수
수학	100
수학	95
수학	90
영어	97
영어	98
영어	90

 

이렇게 데이터가 존재를 할 때 여기서 RANK() 함수를 돌린다면 과목에 상관없이 점수별로 RANK가 나눠질 것이다. 하지만 PARTITION BY를 사용한다면 과목별로 나누어 지는 것을 볼 수 있다. 코드를 보고 결과를 보자.

SELECT
    subject
    , score
    , RANK() over (PARTITION BY SUBJECT ORDER BY score DESC) 순위
from students;

 

결과값

subject	score	순위
수학	100	1
수학	95	2
수학	90	3
영어	98	1
영어	97	2
영어	90	3

 

위의 결과값 표와 같이 PARTITION BY를 사용하면 우리가 원하는 과목 별 점수 순위를 뽑을 수 있게된다.

---

ORDER BY 다중정렬

아 여담으로 ORDER BY의 다중 정렬에 대해서 정리를 해봐야지.

 

ORDER BY는 정렬을 어떻게 할 것인지에 대해서 정해주는 것이다.

이때 ORDER BY는 왼쪽에 있는 것부터 순차적으로 정렬을 해주기에 순서에 신경을 써주어야 한다.

 

ORDER BY 컬럼_1 DESC, 컬럼_2 DESC 이라면,

컬럼_1를 먼저 내림차순으로 정렬을 한 후에 만약 컬럼_2 값에 똑같은 컬럼_1 순위가 존재한다면 그 순위를 다시 컬럼_2값으로 내림차순으로 정렬하겠다는 의미이다.

 

SELECT
	*
FROM TABELS
ORDER BY status DESC, created_at DESC

 

 

위에 같은 코드가 있다면 status를 먼저 내림차순으로 정렬을 한 후에 만약 created_at 중에 똑같은 status 순위가 존재한다면 그 순위를 created_at값으로 내림차순으로 정렬하겠다는 의미이다. ++

 

여기서 주의해야 할점은 status가 제일 왼쪽에 있기에 status가 먼저 내림차순으로 정렬이 되고, 그 다음에 created_at이 내림차순으로 정렬이 되기에 created_at이 정렬될 때에 status는 순서가 바뀌지 않는다!!!

 

아!! 여기서 다중정렬을 하면서 필자가 실수 했는데 'and'를 사용하면 안되고 꼭 ' , '로 구분해주어야 한다. 이것때문에 계속 안됐었음ㅋㅋㅋㅋ

 

이것으로 코테를 풀다가 알게된 메서드를 정리해 보았다!!! 또 모르는게 있으면 정리해야지~~

 

 

@@++