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 를 해줄 필요가 없다.
끝
퍼가실땐 댓글~~~~~
