Rank() , dense_rank()

하나의 테이블에 타입별로 순위 지정을 해야 하는 경우.

create table jjh_test (
id            bigint , 
char_type     int    ,
score         int
) engine = innodb;

insert into jjh_test values (1,1,1999);
insert into jjh_test values (1,2,1999);
insert into jjh_test values (1,3,1000);
insert into jjh_test values (1,4,3699);

insert into jjh_test values (2,1,4999);
insert into jjh_test values (2,2,2999);
insert into jjh_test values (2,3,200);
insert into jjh_test values (2,4,2699);

insert into jjh_test values (3,1,5999);
insert into jjh_test values (3,4,5699);

insert into jjh_test values (4,1,999);
insert into jjh_test values (4,2,6999);
insert into jjh_test values (4,3,2000);

insert into jjh_test values (5,2,2999);
insert into jjh_test values (5,3,3000);
insert into jjh_test values (5,4,4699);

insert into jjh_test values (6,2,999);
insert into jjh_test values (6,3,100);
insert into jjh_test values (6,4,4699);

insert into jjh_test values (7,2,2999);
insert into jjh_test values (8,2,1999);

-- dense_rank() Over () 동일 -- 즉 동일 랭킹 있으면 그 다음 랭킹은 바로 다음.. 연결 된다 (1,2,2,3... 이렇게)
-- Limitcbt 는 상위 사용자 자르기 위하여 사용 하였으며 그럴 필요가 없다면 제외 하면 됨.

set @rank :=0, @char_type :=null , @score :=null , @cnt :=0 , @nat_id :=null , @cnt:=0 , @cnt1:=0 , @nat_id1 :=null ,@rank_new:=0 ,@v_rank:=0 , @limitcnt:=0 ,@nat_id_limit :=null ;
   
select   id, char_type ,score ,
   greatest (
   @rank := if (@char_type=char_type and @score=score,@rank,if(@char_type <> char_type,1,@rank+1)), 
   least(0,@score :=score),
   least(0,@char_type :=char_type)) as rank ,
   greatest (
   @limitcnt := if (@nat_id_limit=char_type ,@limitcnt+1,if(@nat_id_limit <> char_type,1,@limitcnt+1)), 
   least(0,@nat_id_limit :=char_type) ) as limitcnt
   from 
      (
              select id , char_type , score
            from jjh_test 
                    order by char_type , score desc 
             
     )V1

 

-- Rank() Over 동일 순위가 연속적이지 않는다. (1,2,2, 4..)  
-- 
set @rank :=0, @char_type :=null , @score :=null , @cnt :=0 , @nat_id :=null , @cnt:=0 , @cnt1:=0 , @nat_id1 :=null ,@rank_new:=0 ,@v_rank:=0 , @limitcnt:=0 ,@nat_id_limit :=null ;
select id , char_type , score  , rank , cnt , cnt1 , 
      greatest ( 
         @rank_new := if (@v_rank <> rank , rank+cnt , @rank_new) , 
          least(0,@v_rank:=rank)
                ) as rank_new     
from  
(
SELECT id , char_type , score , rank , cnt ,
   greatest ( 
           @cnt1 := if (@nat_id1=char_type , @cnt1=@cnt1+cnt , if (@nat_id <> char_type , 0 , cnt)),
     least(0,@nat_id:=char_type))as cnt1  
 from  
    (    
 select id  , char_type , score , rank ,
  greatest (
      @cnt := if(@char_type=char_type and @rank_new=rank , @cnt+1 , if ( @char_type <> char_type , 0 ,@cnt)),
      least(0,@char_type:=char_type),
      least(0,@rank_new:=rank)) as cnt ,limitcnt
      from 
      ( 
       select   id, char_type ,score ,
           greatest (
           @rank := if (@char_type=char_type and @score=score,@rank,if(@char_type <> char_type,1,@rank+1)), 
           least(0,@score :=score),
           least(0,@char_type :=char_type)) as rank ,
           greatest (
           @limitcnt := if (@nat_id_limit=char_type ,@limitcnt+1,if(@nat_id_limit <> char_type,1,@limitcnt+1)), 
           least(0,@nat_id_limit :=char_type) ) as limitcnt
           from 
           (
             select id , char_type , score
             from jjh_test 
             order by char_type , score desc 
                 
          )V1
       )A
      -- where limitcnt < X;  --각 케릭터 별로 상위 X명으로 제한 한다면....
 )B     
)C      

http://cafe.naver.com/mysqlpg/385

[출처] Rank() , dense_rank() 비스무리 하게 만든 쿼리 ..ㅠ.ㅠ (MySQL Power Group) |작성자 ham