MS-SQL

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

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

먼저 예제 데이터를 만들어 보자.

아래는 SM 엔터테인먼트의 급여 내역 이다. 물론 직원은 소녀시대다~~@.@

CREATE TABLE 급여
(
    이름 nvarchar(10),
    부서 nvarchar(10),
    직책 nvarchar(10),
    급여 int
)
GO
INSERT 급여 VALUES ('유리','개발부','사원',300)
INSERT 급여 VALUES ('태연','지원부','사원',250)
INSERT 급여 VALUES ('서연','개발부','대리',350)
INSERT 급여 VALUES ('수영','영업부','과장',380)
INSERT 급여 VALUES ('윤아','지원부','사원',200)
INSERT 급여 VALUES ('효연','관리부','과장',320)
INSERT 급여 VALUES ('제시카','영업부','사원',220)
INSERT 급여 VALUES ('티파니','관리부','사원',220)
INSERT 급여 VALUES ('써니','개발부','대리',330)

1. ROW_NUMBER()

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

SELECT ROW_NUMBER() OVER (ORDER BY 급여 desc) as 순위, 
       이름, 부서, 직책, 급여
FROM 급여

- 결과..
  
  순위    이름    부서    직책    급여
 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()는 순수하게 순위에 대한 순번으로 동률일경우 같은 순번으로 처리된다. 

그럼 소녀시대의 급여 순위를 보자.

SELECT  RANK() OVER (ORDER BY 급여 desc) as 순위, 
       이름, 부서, 직책, 급여
FROM 급여

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

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

3. DENSE_RANK()

 RANK()함수 유사 하지만 동률 순번이 있을경우 그 다음 순번으로 매겨진다는 다른점이 있다.
    
    SELECT  DENSE_RANK() OVER (ORDER BY 급여 desc) as 순위, 
           이름, 부서, 직책, 급여
    FROM 급여 
        
    - 결과..


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

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


4. NTILE 함수

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

SELECT  NTILE(4) OVER (ORDER BY 급여 desc) as 순위, 
       이름, 부서, 직책, 급여
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 

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

SELECT  RANK() OVER (PARTITION BY 부서 ORDER BY 급여 desc) as 순위, 
       이름, 부서, 직책, 급여
FROM 급여

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

위의 결과는 부서별 급여 순위를 매긴 것이다. 보는 바와 같이 부서별로 급여에 따라 순위를 매기며 이를 응용하면 부서별,직책별 등의 다중 그룹에 대해 파티션을 나눈후 순위를 나타낼수 있다.

또한 인라인 뷰를 활용하면 부서별 특정 등수등의 좀더 세분화된 순위 데이터를 추출해 낼 수 있다.

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