순위함수

ROW_NUMBER() : 행별로  순위를 정함
DENSE_RANK() : 동일 순번일 경우 같은 순위를 줌
RANK()      : 동일 순위가 여럿일 경우, 다음 순위에서 중복된 순위만큼 제거(1, 2, 3, 3, 5....)
NTILE(2)      : 순위별로 나열한 다음, 그룹으로 나눠야 할경우에 씀 숫자 2는 2개로 구분한다는 의미익 5일경우 5개로 N빵함.
--order by 로 순위 지정
select ROW_NUMBER() over(order by document_yymm asc, document_ser desc)
,   *
from  MASTER_TBL

--partition by로 부문별로 순위 지정(순위는 별도로 partition별로 지정됨)
select ROW_NUMBER() over( partition by deptcd order by  yymm asc, ser desc)
,   *
from  MASTER_TBL

 

 

기존의 상관쿼리나 셀프조인을 사용할 경우 논리적 읽기수가 많아지는데 .순위함수를 사용하면 논리적읽기수가 많이 줄어듭니다

ex) 입사일 순서로 순위를 매기고 싶을 때
SELECT 사원번호, 사원명
 ,rank() OVER (ORDER BY 입사일) AS rank
 ,dense_rank() OVER (ORDER BY 입사일) AS rank2 -- 같은 날 입사가 있어도 누적을 시키지 않은 등수
 ,row_number() OVER (ORDER BY 입사일) AS rank -- 연속된 순위
 , ntile(4) OVER (ORDER BY 입사일) AS rank -- 4개 등급으로 나눠 순위를 매기고 싶을 때
FROM 사원
ORDER BY 입사일

ex) 부서별 입사일 순서로 순위를 매기고 싶을 때
SELECT 사원번호, 사원명
 ,rank() OVER (partion by 부서번호 ORDER BY 입사일) AS rank
 ,dense_rank() OVER (partion by 부서번호 ORDER BY 입사일) AS rank2 -- 같은 날 입사가 있어도 누적을 시키지 않은 등수
 ,row_number() OVER (partion by 부서번호 ORDER BY 입사일) AS rank -- 연속된 순위
FROM 사원
ORDER BY 부서, 입사일 DESC

 

 

 

퍼온글

 

SQL Server를 사용하다보면 업무상 특정 데이터들에 대한 순번 또는 순위를 매겨야 하는 경우가 종종  
발생한다. 
이럴경우 SQL Server 2000버젼을 사용할때 참으로 난감해 하면서 "서브쿼리+카르티션곱 조인" 콤보를 
사용한 슈퍼쿼리로 순번 처리를 하게된다. 물론 쿼리성능은 안드로메다로 날려보내며 머리 속에는
오라클의 ROWNUM을 비롯한 다수의 분석함수들을 그리워 한다.

이런 SQL Server 사용자들의 불만을 알았는지 몰랐는지 MS에서는 SQL Server 2005버젼에서 다양한
분석함수들을 제공해준다. 

먼저 예제 데이터를 만들어 보자.
아래는 SM 엔터테인먼트의 급여 내역 이다. 물론 직원은 소녀시대다~~@.@ 

01.CREATE TABLE 급여

02.(

03.이름 nvarchar(10),

04.부서 nvarchar(10),

05.직책 nvarchar(10),

06.급여 int

07.)

08.GO

09.INSERT 급여 VALUES ('유리','개발부','사원',300)

10.INSERT 급여 VALUES ('태연','지원부','사원',250)

11.INSERT 급여 VALUES ('서연','개발부','대리',350)

12.INSERT 급여 VALUES ('수영','영업부','과장',380)

13.INSERT 급여 VALUES ('윤아','지원부','사원',200)

14.INSERT 급여 VALUES ('효연','관리부','과장',320)

15.INSERT 급여 VALUES ('제시카','영업부','사원',220)

16.INSERT 급여 VALUES ('티파니','관리부','사원',220)

17.INSERT 급여 VALUES ('써니','개발부','대리',330)

1. ROW_NUMBER()
ROW_NUMBER()함수는 데이터들의 각각의 행에 대한 일련의 번호를 붙여주는 기능이다. 주로 웹 개발시
페이징에 필요한 유용한 함수 이다. 

1.SELECT ROW_NUMBER() OVER (ORDER BY 급여 desc) as 순위,

2.이름, 부서, 직책, 급여

3.FROM 급여

 

- 결과..ROW_NUMBER() 

순위	이름	부서	직책	급여
1	수영	영업부	과장	380
2	서연	개발부	대리	350
3	써니	개발부	대리	330
4	효연	관리부	과장	320
5	유리	개발부	사원	300
6	태연	지원부	사원	250
7	제시카	영업부	사원	220
8	티파니	관리부	사원	220
9	윤아	지원부	사원	200

2. RANK() 함수
ROW_NUMBER와 같은 일종의 순번이지만 ROW_NUMBER는 무조건 1에서 부터 순차적인 순번을 매기는 
일방적인 방식이지만 RANK()는 순수하게 순위에 대한 순번으로 동률일경우 같은 순번으로 처리된다. 
그럼 소녀시대의 급여 순위를 보자. 

1.SELECT  RANK() OVER (ORDER BY 급여 desc) as 순위,

2.이름, 부서, 직책, 급여

3.FROM 급여

- 결과..RANK() 

순위	이름	부서	직책	급여
1	수영	영업부	과장	380
2	서연	개발부	대리	350
3	써니	개발부	대리	330
4	효연	관리부	과장	320
5	유리	개발부	사원	300
6	태연	지원부	사원	250
7	제시카	영업부	사원	220
7	티파니	관리부	사원	220
9	윤아	지원부	사원	200

결과를 보니 제시카와 티파니가 나란히 7위를 알수 있다.

3. DENSE_RANK()
RANK()함수 유사 하지만 동률 순번이 있을경우 그 다음 순번으로 매겨진다는 다른점이 있다. 

1.SELECT  DENSE_RANK() OVER (ORDER BY 급여 desc) as 순위,

2.이름, 부서, 직책, 급여

3.FROM 급여

- 결과..DENSE_RANK() 

순위	이름	부서	직책	급여
1	수영	영업부	과장	380
2	서연	개발부	대리	350
3	써니	개발부	대리	330
4	효연	관리부	과장	320
5	유리	개발부	사원	300
6	태연	지원부	사원	250
7	제시카	영업부	사원	220
7	티파니	관리부	사원	220
8	윤아	지원부	사원	200

RANK()함수와는 다르게 7위로 동률인 제시카,티파니 뒤에 윤아가 8위로 나오는걸 알수 있다.

4. NTILE 함수
앞의 함수들 과는 조금 다른 개념이다. 이 함수는 지정된 숫자에 맞게 정렬된 데이터는 균등하게 
그룹핑하여 순번을 매기며 남는 개수는 앞에서 부터 하나씩 더해서 그룹핑이 된다. 

1.SELECT  NTILE(4) OVER (ORDER BY 급여 desc) as 순위,

2.이름, 부서, 직책, 급여

3.FROM 급여

 

- 결과..NTILE() 

순위	이름	부서	직책	급여
1	수영	영업부	과장	380
1	서연	개발부	대리	350
1	써니	개발부	대리	330
2	효연	관리부	과장	320
2	유리	개발부	사원	300
3	태연	지원부	사원	250
3	제시카	영업부	사원	220
4	티파니	관리부	사원	220
4	윤아	지원부	사원	200

총 9개의 데이터를 균등하게 4로 나눈후 나머지 1의 데이터는 위에 순번에 포함 시킨걸 알수 있다.

5. PARTITION BY 
위의 함수들과 결합 하여 사용하는 일종의 옵션으로 대상 행들의 결과 집합을 파티션을 나누어 
그 파티션 안에서 순위를 매기기 위해 사용된다.  

1.SELECT  RANK() OVER (PARTITION BY 부서 ORDER BY 급여 desc) as 순위,

2.이름, 부서, 직책, 급여

3.FROM 급여

 

- 결과..PATITION BY 

순위	이름	부서	직책	급여
1	서연	개발부	대리	350
2	써니	개발부	대리	330
3	유리	개발부	사원	300
1	효연	관리부	과장	320
2	티파니	관리부	사원	220
1	수영	영업부	과장	380
2	제시카	영업부	사원	220
1	태연	지원부	사원	250
2	윤아	지원부	사원	200

위의 결과는 부서별 급여 순위를 매긴 것이다. 보는 바와 같이 부서별로 급여에 따라 순위를 매기며
이를 응용하면 부서별,직책별 등의 다중 그룹에 대해 파티션을 나눈후 순위를 나타낼수 있다.
또한 인라인 뷰를 활용하면 부서별 특정 등수등의 좀더 세분화된 순위 데이터를 추출해 낼 수 있다.

이상으로 SQL Server 2005의 새로운 순위함수(분석함수)를 알아보았다!!!

 

 

<script src="http://gdbt.tistory.com/plugin/CallBack_bootstrapper?&src=http://s1.daumcdn.net/cfs.tistory/v/0/blog/plugins/CallBack/callback&id=50&callbackId=gdbttistorycom50479&destDocId=callbacknestgdbttistorycom50479&host=http://gdbt.tistory.com&float=left&random=646"></script>