[mysql] rank 값 출력 하기

oracle 과 달리 mysql 에서는 랭크 값을 구할수 있는 함수가 따로 존재 하지 않는다.

따라서 SELECT 된 컬럼값을 스칼라 변수에 할당하고 초기화하길 반복 하면서 랭크값을

구해야한다.



EVENT라는 테이블이 있다고 가정하고

JOIN_COUNT: 참여 회수 , RIGHT_COUNT: 적중 회수 , MEMBER_ID: 회원번호(유니크)

라는 컬럼이 있다고 가정하자

일단 적중회수가 같을 경우 참여회수가 많은 것을 우선 출력한다 가정할경우에 쿼리는

SELECT
         T.*
         ,@ROWNUM:= @ROWNUM + 1  AS ROWNUM
FROM (
       SELECT
                 JOIN_COUNT
                 ,RIGHT_COUNT
                 ,MEMBER_ID
       FROM EVENT
       WHERE 1=1
       ORDER BY RIGHT_COUNT DESC, JOIN_COUNT DESC
) T ,(SELECT @ROWNUM:=0) R

이렇게 할경우 적중수 우선 정렬후 참여수가 많은 순으로 정렬되면서 행번호가 매겨져서
랭킹을 구할수 있다 하지만 문제점이 동일 적중에 참여수도 동일일 경우에는 랭킹이 동등 하게 나오지가 않는다.

따라서 위 쿼리는 사용불가

SELECT
         T.*
       
FROM (
       SELECT
                 JOIN_COUNT
                 ,RIGHT_COUNT
                 ,MEMBER_ID
       FROM EVENT
       WHERE 1=1
       ORDER BY RIGHT_COUNT DESC, JOIN_COUNT DESC
) T ,(SELECT
           @RANK:=1
           ,@PREV_RIGHT_COUNT:=0
           ,@PREV_JOIN_COUNT:=0
           ,@SAME_COUNT:=1) R

일단 랭크값을 1위 부터 증가 하므로 RANK 에 기본값 1을 할당한다.
그리고 이전 적중수,참여수를 저장하기 위한 변수 PREV_RIGHT_COUNT,PREV_JOIN_COUNT
에 기본 0 값을 할당한다. SAME_COUNT RANK 의 증가량을 저장하기위한변수로 기본 1씩 증가 하기때문에 1로 할당한다

위 쿼리를 변형.

SELECT
         T.*
                        -- 이전 로우보다 적중카운트가 작을경우 순위 증가
         ,@RANK:= CASE WHEN T.RIGHT_COUNT< @PREV_RIGHT_COUNT
                                THEN @RANK + @SAME_COUNT
                        -- 이전 로우랑 적중 카운트는 같으나 참여 회수가 적을경우 순위 증가
                                WHEN (T.RIGHT_COUNT  =  @PREV_RIGHT_COUNT AND
                                          T.JOIN_COUNT < @PREV_JOIN_COUNT)
                                THEN @RANK + @SAME_COUNT
                        -- 이전 로우랑 적중 ,참여회수가 모두 동일할경우 같은 순위로 처리
                                WHEN  WHEN (T.RIGHT_COUNT   = @PREV_RIGHT_COUNT
                                           AND T.JOIN_COUNT = @PREV_JOIN_COUNT)
                                THEN @RANK
                                ELSE @RANK
                         END AS RANK
           -- RANK 라는 이름을 붙혀주고 경우에따라 저장될 값을 초기화한다.
         
           -- 랭크중복 로우가 아니면  SAME_COUNT 1로 초기화거나
           -- 랭크가 중복되는 만큼 랭크증가량은 은 늘어난다.
           ,@SAME_COUNT := IF(
                                         (T.RIGHT_COUNT  = @PREV_RIGHT_COUNT  
                                         AND T.JOIN_COUNT = @PREV_JOIN_COUNT ),                                                        @SAME_COUNT+1, 1)
           -- 현재 적중수와 참여수를 다음 로우에서 참고하기위해 값을 넣어준다.
           ,@PREV_RIGHT_COUNT:=T.RIGHT_COUNT
           ,@PREV_JOIN_COUNT:=T.JOIN_COUNT


FROM (
       SELECT
                 JOIN_COUNT
                 ,RIGHT_COUNT
                 ,MEMBER_ID
       FROM EVENT
       WHERE 1=1
       ORDER BY RIGHT_COUNT DESC, JOIN_COUNT DESC
) T ,(SELECT
           @RANK:=1
           ,@PREV_RIGHT_COUNT:=0
           ,@PREV_JOIN_COUNT:=0
           ,@SAME_COUNT:=1) R


거의 완성 됐다 최종본은 위 쿼리를 FROM 절에서 한번더 묵어서 필요한 컬럼만 SELECT 하면 됨.
-- 필요한것만 SELECT
SELECT
    T2.RANK
    ,T2.RIGHT_COUNT
    ,T2.JOIN_COUNT
    ,T2.MEMBER_ID
FROM (
SELECT
         T.*
                        -- 이전 로우보다 적중카운트가 작을경우 순위 증가
         ,@RANK:= CASE WHEN T.RIGHT_COUNT< @PREV_RIGHT_COUNT
                                THEN @RANK + @SAME_COUNT
                        -- 이전 로우랑 적중 카운트는 같으나 참여 회수가 적을경우 순위 증가
                                WHEN (T.RIGHT_COUNT  =  @PREV_RIGHT_COUNT AND
                                          T.JOIN_COUNT < @PREV_JOIN_COUNT)
                                THEN @RANK + @SAME_COUNT
                        -- 이전 로우랑 적중 ,참여회수가 모두 동일할경우 같은 순위로 처리
                                WHEN  WHEN (T.RIGHT_COUNT   = @PREV_RIGHT_COUNT
                                           AND T.JOIN_COUNT = @PREV_JOIN_COUNT)
                                THEN @RANK
                                ELSE @RANK
                         END AS RANK
           -- RANK 라는 이름을 붙혀주고 경우에따라 저장될 값을 초기화한다.
         
           -- 랭크중복 로우가 아니면  SAME_COUNT 1로 초기화거나
           -- 랭크가 중복되는 만큼 랭크증가량은 은 늘어난다.
           ,@SAME_COUNT := IF(
                                         (T.RIGHT_COUNT  = @PREV_RIGHT_COUNT  
                                         AND T.JOIN_COUNT = @PREV_JOIN_COUNT ),                                                        @SAME_COUNT+1, 1)
           -- 현재 적중수와 참여수를 다음 로우에서 참고하기위해 값을 넣어준다.
           ,@PREV_RIGHT_COUNT:=T.RIGHT_COUNT
           ,@PREV_JOIN_COUNT:=T.JOIN_COUNT


FROM (
       SELECT
                 JOIN_COUNT
                 ,RIGHT_COUNT
                 ,MEMBER_ID
       FROM EVENT
       WHERE 1=1
       ORDER BY RIGHT_COUNT DESC, JOIN_COUNT DESC
) T ,(SELECT
           @RANK:=1
           ,@PREV_RIGHT_COUNT:=0
           ,@PREV_JOIN_COUNT:=0
           ,@SAME_COUNT:=1) R

) T2
WHERE
1=1

-- 이미 정렬후 RANK 만 붙혔기때문에 따로 ORDER BY 를 해줄 필요가 없다.

끝

퍼가실땐 댓글~~~~~