38. MySQL rank 만들기 : count(), rank()

쿼리를 만들다 보면 순위를 매기고 싶을 때가 있습니다. 그럴 때 유용하게 쓸 수 있는 방법에 대해서 알아봅시다.

 

math라는 테이블이 있다고 가정하겠습니다. 각 칼럼은 아래의 값을 의미합니다.

id : 테이블 아이디

math_score : 학생들의 수학 점수

name : 학생들의 이름

 

1. COUNT를 이용해서 만드는 방법

SELECT
    id, math_score, name,
    ( SELECT COUNT(*) + 1 FROM math_socre WHERE math_score > a.math_score ) AS rank
FROM
    math AS a
ORDER BY
    rank ASC

---

2. MySQL의 사용자 정의 변수를 이용하여 만드는 방법

MySQL에도 변수와 같은 개념으로 [@변수명]을 이용하여 값을 저장할 수 있습니다. 다만 [@변수명]은 모든 connect에 유효한 값은 아니고, 해당 connect session에서만 유효합니다.

SELECT
    id, math_score, name,
    ( @rank := @rank + 1 ) AS rank
FROM
    math AS a,
    ( SELECT @rank := 0 ) AS b
ORDER BY
    a.math_score DESC;

---

3. rank() 함수

rank() 함수를 이용하면 over() 안에 ORDER BY "랭크를 매길 수치의 컬럼"을 적고 오름차순(ASC)인지 내림차순(DESC)인지 적어주면 됩니다. 위 방법에 비해 간편하죠?

SELECT 
    id,
    math_score,
    name,
    rank() over(ORDER BY math_score DESC) AS rank 
FROM 
    math;

 

rank()를 역순으로 하고 싶을 때

dense_rank() 함수를 사용하면됩니다.

SELECT 
    id,
    math_score,
    name,
    dense_rank() over(ORDER BY math_score DESC) AS rank 
FROM 
    math;

 

그룹으로 랭크를 매기고 싶을 때

class : 교실이라는 칼럼이 있다고 가정했습니다. 순위를 정하는데 A클래스, B클래스, C클래스 클래스별로 학생들의 수학 성적 순위를 1부터 30까지 매기고 싶을 때 사용하는 것이 [PARTITION BY]라는 옵션입니다. [PARTITION BY] 뒤에는 묶고 싶을 칼럼을 적으시면 됩니다.(SELECT 쿼리로 [GROUP BY]를 사용하고 있다면 [GROUP BY]의 칼럼 값을 적으면 됩니다.)

SELECT 
    id,
    math_score,
    name,
    class,
    dense_rank() over(
        PARTITION BY class,
        ORDER BY math_score DESC
    ) AS rank 
FROM 
    math;