순위 함수의 부재로 인해 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>
from 절이 생성한 결과 집합을 ROW_NUMBER 함수가 적용되는 파티션으로 나눕니다.
<order_by_clause>
파티션에서 ROW_NUMBER 값이 행에 할당되는 순서를 결정합니다. 자세한 내용은 ORDER BY 절(Transact-SQL)를 참조하십시오. 순위 함수에 <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) 들의 이용과 응용은 여러분의 몫입니다.
|
