[MSSQL] 순위함수 RANK, NTILE, DENSE_RANK, ROW_NUMBER

순위 함수

SQL Server 2005부터 RANK(), NTILE(), DENSE_RANK(), ROW_NUMBER() 등 4가지 순위 함수를 제공한다.

이 기능은 순번을 처리할 때 필요했던 복잡한 과정들을 단순화 시켜서 쿼리의 작성 시간을 단축시켜 준다.

순위 함수는 한 마디로 결과에 순번 또는 순위(등수)를 매기는 역할을 하는 함수이다.

순위 함수의 형식은 다음과 같다

<순위함수이름>( ) OVER(

[PARTITION BY <partition_by_list>]

ORDER BY <order_by_list>)

순위 함수의 가장 큰 장점은 구문이 단순하다는 점이다. 단순한 구문은 코드를 명확하게 만들고, 쉽게 수정할 수 있다. 또한, 효율성(성능)도 뛰어나다.

즉, SQL Server에 부하를 최소화하면서 순위를 매기는 결과를 준다.

<예시>

1. 회원 테이블(userTbl)에서 키가 큰 순으로 순위를 정하고 싶을 경우에는 ROW_NUMBER() 함수를 사용하면 된다.

SELECT ROW_NUMBER() OVER(ORDER BY height DESC) [키큰순위], name, addr, height FROM userTbl ;

그런데 같은 키의 경우에는 특별한 출력 순서를 지정하지 않았다. 키가 같은 경우에는 이름 가나다순으로 정렬하게 수정해 보자.

SELECT ROW_NUMBER() OVER(ORDER BY height DESC, name ASC) [키큰순위], name, addr, height FROM userTbl ;

2. 이번에는 전체 순위가 아닌 각 지역별로 순위를 주고 싶은 경우를 생각해 보자. 즉, 경기별, 경남별 등 지역으로 나눈 후

키 큰 순위를 매기는 경우이다. 이 경우에는 PARTITION BY 절을 사용하면 된다.

SELECT addr, ROW_NUMBER() OVER(PARTITION BY addr ORDER BY height 

DESC, name ASC) [지역별키큰순위], name, height 

FROM userTbl ;

경기, 경남, 서울의 경우에는 각 지역별로 별도의 순위가 매겨져 있다. 데이터의 개수가 작아서 그리 효과적이지 않은 듯 하지만

대량의 데이터에서는 큰 효과를 느낄 수 있다.

3. 이번에는 전체 순위 결과를 다시 살펴보자.

앞 1번의 결과를 보면 '임재범'과 '이승기'는 키가 같은 182인데도 키 순위가 2등과 3등으로 나뉘어져 있다. 같은 키인데도     3등이 된 사람의 입장에서는 상당히 불공평하게 느껴질 것이다. 이럴 경우에 두 개의 데이터를 같은 등수로 처리하는 

함수가 DENSE_RANK() 함수이다.

SELECT DENSE_RANK() OVER(ORDER BY height DESC) [키큰순위], name, addr, height FROM userTbl ;

4. 앞의 결과가 만족스러울 수도 있겠지만, 2등이 두 명 나온 후에 3등(김경호)이 나왔다. 어떤 경우에는 2등이 두 명이라면 

2등, 2등, 4등 식으로 3등을 빼고 4등부터 순위를 매길 필요도 있다. 이럴 때는 RANK() 함수를 사용하면 된다.

SELECT RANK() OVER(ORDER BY height DESC) [키큰순위], name, addr, height FROM userTbl ;

5. 이번에는 전체 인원을 키순으로 세운 후에, 몇 개의 그룹으로 분할하고 싶은 경우이다. 예를 들면 10명의 사용자를 키

순으로 세운 후에, 2개의 반으로 분반하고 싶은 경우를 들수 있다. 이럴 때는 단순히 5명씩 나눠지면 된다. 

이때 사용하는 함수가 NTILE(나눌 그룹 개수) 함수다.

SELECT NTILE(2) OVER(ORDER BY height DESC) [반번호], name, addr, height FROM userTbl ;

그런데 반을 3개로 분리하면 어떻게 될까? 답부터 이야기하면 우선 똑같이 나눈 후에, 나머지 인원을 처음 그룹부터 하나씩 배당하게 된다. 이 예에서는

1반 3명, 2반 3명, 3반 3명으로 한 후에 남은 1명을 처음인 1반에 할당한다. 만약 4개로 분리하면 1반 2명, 2반 2명, 3반 2명, 4반 2명으로 나눈 후에, 

남은 2명을 1반과 2반에 한 명씩 할당한다. 결국 1반 3명, 2반 3명, 3반 2명, 4반 2명으로 배정된다.

SELECT NTILE(4) OVER(ORDER BY height DESC) [반번호], name, addr, height FROM userTbl ;