순위(랭크) 구하기

 

https://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table



    - 주의!  rank라는 이름 사용시 (칼럼이름, 별칭 등) ,  따옴표를 꼭 써야 한다.



    //----------------------------------

    - count 사용 - 2000행에 4.5초

            - 2만행 테이블에서 2000행 조회에 4초

SELECT  id, score,  1+(SELECT count(*) from test1 a WHERE a.score > b.score) as rank1

FROM test1 b

where id=1000

-- order by rank1



    //----------------------------

    - rank() 함수 사용 - 2000행에 15.7초 - (count 방식에 비해 4배 이상 느림)

        - 2만행 테이블에서 2000행 조회에 38초

SELECT  t2.id, t2.score ,t2.rank12 

FROM    ( SELECT id, score, RANK() OVER ( ORDER BY score DESC ) AS rank12 FROM test1 ) t2 

WHERE   t2.id = 1000




        //-----------------------------------

        - 성능 테스트 프로시저

CREATE DEFINER=`root`@`localhost` PROCEDURE `rank_test`()

BEGIN

 

declare v_max int unsigned default 2000;

declare v_counter int unsigned default 0;

 

while v_counter < v_max do

        

        /*SELECT    (t2.rank12)  into @var 

FROM    ( SELECT id , RANK() OVER ( ORDER BY score DESC ) AS rank12 FROM test1 ) t2 

WHERE   t2.id = v_counter ; */

 

SELECT   1+(SELECT count(*) from test1 a WHERE a.score > b.score) as rank1  into @var 

FROM test1 b

where id= v_counter;

        

    set v_counter = v_counter+1;

end while;

commit;

END



//----------------------------------------------------------------------------

//----------------------------------------------------------------------------

    - 테이블의 모든 행의 순위를 업데이트

 

    //--------------------------------------------------

    - rank() 함수 이용 - 1만행에 0.2초

 

UPDATE test1 

SET rank1 = (

    SELECT

        rank12 

    FROM

        ( SELECT id, RANK() OVER ( ORDER BY score DESC ) AS rank12 FROM test1 ) t2 

    WHERE

    t2.id = test1.id 

    )

주의! 칼럼이름이나 별칭을 rank로 하면 안됨, rank 이름을 사용하려면 따옴표로 묶어야 됨




        //--------------------------------------------------

        - count 이용 방법 1  - 2만행에 0.2초

            - 변수 사용

https://stackoverflow.com/questions/3196971/mysql-update-statement-to-store-ranking-positions

 

SET @r=0;

UPDATE test1 SET rank1= @r:= (@r+1) ORDER BY score DESC;



        //-------------------------------

        - 참고, 내부에서 변수 초기화

select @rownum:=@rownum+1 ‘rank’, p.* 

from player p, (SELECT @rownum:=0) r 

order by score desc;



        //--------------------------------------------------

    - count 이용 방법 2 

        - 1만행에 47초 걸림  (rank() 방식에 비해 100배 이상 느림)

        - 행이 많아질수록 느려짐

 

UPDATE test1 

SET rank1 = (

    SELECT

        rank12 

    FROM

        (

        SELECT

            id,

            score,

            1+ ( SELECT count(*) FROM test1 a WHERE a.score > b.score ) AS rank12 

        FROM

            test1 

        ) t2 

    WHERE

    t2.id = test1.id 

    )




//-----------------------------------------------------------------------------

SELECT 결과 출력 하지 않기(막기)

    - 성능 속도 측정시 사용

 

SELECT name1  into @var 




 

Posted by 코덴스

댓글을 달아 주세요