컴퓨토피아

올바른 순위 정렬 결과를 얻으려면 (1) : 올바른 순위 정렬 결과를 얻으려면


ORDER BY를 실행하여 정렬하면 순위를 간단히 출력할 수 있지만 아쉽게도 같은 값이 존재한다면 같은 순위를 부여해야 한다.
예를 들어 아래와 같은 순위 결과를 출력하고 싶다면 어떤 쿼리를 활용해야 할까?

성적 테이블

순위 이름 점수
1 김진수 99
2 김영은 87
2 김한영 87
4 정진은 65

위의 표에서 나타나듯 김영은씨와 김한영씨는 같은 점수이다. 결국 공동2위로 순위표가 정상으로 출력되었다.
위와 같은 결과를 출력시키기 위해서는 그 사람의 점수보다 성적이 좋은 사람이 몇 명 있는지를 계산해야 한다.
서브쿼리를 활용하여 기술하면 위와 같은 올바른 순위 정렬 결과를 얻을 수 있다.

SELECT ( SELECT COUNT (*) + 1 FROM 성적 WHERE 성적.점수 > S.점수),S.이름, S.점수 FROM 성적 S



먼저 서브 쿼리를 통해 순위값을 구한다. 자신보다 성적이 좋은 사람이 없으면 COUNT(*)의 값은 0이되고
결국 +1을 통해 순위에 1이 리턴된다.

제공 : 코리아인터넷닷컴, a 2001년 11월 19일 

순위 함수의 부재로 인해 MSSQL2000에서는 실행될 수 없었지만 MSSQL2005에서 가능해진 기능을 소개하고자 합니다.

Transact-SQL은 다음과 같은 순위 함수를 제공합니다.

 

RANK	NTILE
DENSE_RANK	ROW_NUMBER

순위함수의 내용은 http://msdn2.microsoft.com/ko-kr/library/ms189798.aspx 링크의 온라인북을 참고하셔도 됩니다.

MSSQL2000에서는 오라클에서 쓰는 ROW_NUMBER() 같은 함수를 쓸 수 없어 유저들의 불만이 많았습니다. 다음과 같이 동적으로 카운트를 하는 서브쿼리를 통하여 비슷하게 구현할 수 있었습니다. 다음의 예제를 통해서 MSSQL2000에서 여러 단계를 거쳐야 했던 방법을 MSSQL2005에서 한 문장으로 해결되는 예제를 소개합니다.

 

create table TEST1 (user_id char(3),num int) insert TEST1 values('aaa',3) insert TEST1 values('aaa',2) insert TEST1 values('bbb',15) insert TEST1 values('bbb',2) insert TEST1 values('aaa',6) insert TEST1 values('aaa',4) insert TEST1 values('aaa',3)

리스트 1 기초 데이터 입력

구분되는 일련번호가 없는 데이터를 다음과 같은 결과물을 얻으려면 어떻게 해야 되는지에 대한 문제입니다. 데이터는 user_id로 구분이 되어 있고 구분이 없는 num 값으로 구성되어 있습니다.

 

user_id   num1        num2        num3        num4        num5        ------- ----------- ----------- ----------- ----------- ---------- aaa         3             2              6              4               3 bbb        15            2             NULL          NULL           NULL

리스트 2 원하는 결과

MSSQL 2000을 이용할 경우

리스트 2 의 결과 처럼 user_id 별로 num1~num5 컬럼으로 나누어 데이터를 가져오는 것입니다. 각 로우를 구분하기 위해 indentity()함수를 이용해서 임시테이블에 id 라는 일련번호 컬럼을 만듭니다.

 

-------------------------------------------------------- -- 일련번호생성하여 #temp_1 만들기 select identity(int,1,1) id,* into #temp_1 from TEST1

리스트 3 일련번호 생성하기 위해 identity()함수를 이용한 임시테이블 만들기

각 로우가 구분되는 id라는 컬럼이 만들어 졌으므로, user_id 별로 일련번호를 Group by및 case 문을 사용하여 원하는 결과를 쿼리합니다.

 

select user_id,   max(case id when 1 then num else null end), max(case id when 2 then num else null end), max(case id when 3 then num else null end), max(case id when 4 then num else null end), max(case id when 5 then num else null end) from (select (select count(*)   from #temp_1 where user_id = a. user_id and id <= a.id) id,a.user_id,num from #temp_1 a) t1 group by user_id

리스트 4 MSSQL2000의 임시테이블을 이용한 쿼리

 

/* user_id     num1    num2    num3    num4     num5 ------- ----------- ----------- ----------- ----------- ---------- aaa             3           2          6           4           3 bbb           15          2        NULL       NULL       NULL (2개행적용됨) */

리스트 5 MSSQL2000의 임시테이블을 이용한 쿼리 결과

MSSQL 2005 ROW_NUMBER( ) OVER 절 이용

ROW_NUMBER ( )  구문

 

ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

리스트 6 ROW_NUMBER ( )  구문

인수

<partition_by_clause>
 
<order_by_clause>
 
MSSQL2000의 쿼리에서는 원하는 결과를 위해서 각 로우를 구분하는 id열을 가진 임시테이블을 만들고 또한, 구분되어진 id 열을 user_id에 따라 서브쿼리를 하고, count 하여야 원하는 결과를 구할수 있었습니다. MSSQL 2005에서 등장한 순위 함수를 이용하면, 다음과 같은 한 문장으로 원하는 결과를 구할수 있습니다.

 

select user_id,   max(case id when 1 then num else null end), max(case id when 2 then num else null end), max(case id when 3 then num else null end), max(case id when 4 then num else null end), max(case id when 5 then num else null end) from (select row_number()   over (partition by user_id order by user_id) id,*  from TEST1) t1 group by user_id

리스트 7 MSSQL2005의 row_number() 함수를 이용한 쿼리

 

/* user_id                                                 ------- ----------- ----------- ----------- ----------- -------- aaa     3           2           6           4           3 bbb    15         2         NULL      NULL      NULL 경고: 집계또는다른SET 연산에의해NULL 값이제거되었습니다. (2개행적용됨) */

리스트 8 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과


리스트 9 MSSQL2000과 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과 실행계획

임시테이블을 생성하는 로직을 포함하지 않더라도, MSSQL2005의 순위 함수를 사용하는 것이 성능상 유리합니다.

 

SET STATISTICS IO ON SET STATISTICS TIME ON GO select identity(int,1,1) id,* into #temp_1 from TEST1 SQL Server 구문분석및컴파일시간:   CPU 시간= 13ms, 경과시간= 13ms.   테이블'TEST1'. 검색수1, 논리적읽기수1, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0. SQL Server 실행시간:   CPU 시간= 0ms, 경과시간= 13ms.   (7개행적용됨) select user_id,   max(case id when 1 then num else null end), max(case id when 2 then num else null end), max(case id when 3 then num else null end), max(case id when 4 then num else null end), max(case id when 5 then num else null end) from (select (select count (*) from #temp_1 where user_id = a.user_id and id <= a.id) id,a. user_id ,num from #temp_1 a) t1 group by user_id user_id                                                 ------- ----------- ----------- ----------- ----------- ---------- aaa     3           2           6           4           3 bbb    15         2         NULL       NULL       NULL 경고: 집계또는다른SET 연산에의해null 값이제거되었습니다. (2개행적용됨) 테이블'#temp_1'. 검색수8, 논리적읽기수16, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기 수0. SQL Server 실행시간:   CPU 시간= 0ms, 경과시간= 1ms. select user_id,   max(case id when 1 then num else null end), max(case id when 2 then num else null end), max(case id when 3 then num else null end), max(case id when 4 then num else null end), max(case id when 5 then num else null end) from (select row_number( ) over (partition by user_id order by user_id) id,*  from TEST1) t1 group by user_id user_id                                                 ------- ----------- ----------- ----------- ----------- ---------- aaa     3           2           6           4           3 bbb     15          2           null        null        null 경고: 집계또는다른SET 연산에의해null 값이제거되었습니다. (2개행적용됨) 테이블'TEST1'. 검색수1, 논리적읽기수1, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0. SQL Server 실행시간:   CPU 시간= 0ms, 경과시간= 1ms.

리스트 10 실행통계

이상과 같이 실행통계의 성능을 통하여 MSSQL 2005를 이용하면 성능상 더 좋고 간단한 방법을 알아보았습니다. 다른 랭크 함수 (RANK,NTITLE,DENSE_RANK) 들의 이용과 응용은 여러분의 몫입니다. 

[출처] 제 2강 : MSSQL2005에 등장한 각종 순위 함수 |작성자 윈마스터