[MS SQL] ROW_NUMBER() ,DENSE_RANK(), RANK() 순위함수 + NTILE(*), PARTITION BY()

MS SQL에서는 순위를 매기는 것에 대해 도와주는 함수들이 있습니다.

 

오늘은 순위를 매겨주는 함수 순위함수에 대해 작성하고자 합니다.

 

Create Table #TEST1(    Name        Varchar(50),   --이름  age         int,              --나이  Assets      int,             --자산  Liabilities int,                --부채  job  nvarchar(10)    --직업   )

Insert Into #TEST1 values ('이희은', 28, 3000, 1300, '선생님') Insert Into #TEST1 values ('최종은', 27, 5000, 1300, '학원강사') Insert Into #TEST1 values ('이수민', 31, 2000, 2300, '선생님') Insert Into #TEST1 values ('최덕희', 27, 1000, 3000, '연구원') Insert Into #TEST1 values ('이정훈', 36, 2000, 1400, '학원강사')

 

데이터는 소계와 합계에서 사용하던것을 그대로 사용하겠습니다.

 

1. ROW_NUMBER()은 중복 값도 DB 내에서 순서를 만들어서 순차적으로 값을 나타내주는 함수입니다. 

 

예시로

select ROW_NUMBER() OVER(ORDER BY assets DESC) AS '순위', * from #TEST1

 

 

위의 그림 처럼 같은 금액의 두 사람에게 공동 순위가 아닌 각각의 순위가 입력되었습니다.

 

이것이 ROW_NUMBER()를 통한 순위함수입니다.

 

(순차적이라고 했는데 같은 값의 경우 등수는 제가 등록한 이름이나 나이에 대한 순차적인 내용은 아닌거 같습니다.

조사할 필요가 있는거 같습니다)

 

 

2. DENSE_RANK() 중복 순위가 있을 경우에도 강제로 순차적으로 순위 결정

 

간단히 말해 3등이 두명이면 5등으로 가는게 아닌 4등부터 다시 순차적으로 진행한다는 것입니다.

 

select DENSE_RANK()  OVER(ORDER BY assets DESC) AS '순위', * from #TEST1

 

네 위 그림처럼 3등이 두명일 경우 인원 수가 맞다면 5번째 인원이 5등을 하는게 맞지만 그것을 무시하고

 

4등부터 다시 순차적으로 진행하는 것이 이 DENSE_RANK() 함수의 기능입니다.

 

3. RANK()는 DENSE_RANK()와 다르게 중복 등수가 등장할 경우 중복 인원 수 만큼 건너 뛰고 출력하는 함수입니다.

select RANK() OVER(ORDER BY assets DESC) AS '순위', * from #TEST1

 

 

위 그림 처럼 DENSE_RANK()에서 처럼 3등이 두명이 발생하였습니다. 다음 등수는 중복 인원이 1명이 발생하였기 때문에

 

4등을 건너뛴 5등부터 다시 순차적으로 진행됩니다. 이것이 RANK()의 기능입니다.

 

순위함수의 경우 이렇게 총 3가지로 구성이 되어있습니다. 

 

사실 순위 함수라 순위를 알고자할때만 사용하지 않고 게시글의 숫자를 입력할 때라던가 아니면 인원파악을 위해도 사용하고 있습니다.

 

그래서 그것에 대해 더욱 도움을 줄 수 있는 함수를 소개하고자 합니다.

 

4. NTILE(*) 입니다. (* : 숫자 값) 

  NTILE는 그룹 분리를 해주는 함수입니다. 바로 예시부터 보고 이야기 하겠습니다.

 

 

이것은 순위를 매기는 것이 아니라 그룹을 매기는 것이기 때문에 저는 여기에 별칭으로 순위가 아닌 순위 그룹으로 출력했습니다.

 

select NTILE(3) OVER(ORDER BY assets DESC) AS '순위그룹', * from #TEST1

 

실행문은 위와 같습니다. NTILE()에서 가로안에 원하는 숫자를 넣어주면 그 만큼 순차적으로 인원을 분리하여 그룹을 만들어 줍니다.

 

여기도 앞서 말씀드린 것 처럼 중복 값의 순차의 경우 일정한걸 보니 조건이 있는 거 같은데 확인할 필요가 있을 거 같습니다.

 

인원을 정확하게 분배할 때 참 좋을거 같습니다.

 

5. PARTITION BY는 앞선 NTILE과 다르게 혼자서도 쓸수 있는게 아닌 앞의 순위함수를 완전히 보조하는 함수입니다.

 

순위를 나뉘고 싶지만 전체 순위 뿐 아니라 그룹별 순위 직업별 순위등 다양한 그룹을 통해 그룹별 순위를 확인 하고 싶을 때 사용하는 

함수입니다. 

 

 

select ROW_NUMBER() OVER(PARTITION BY job ORDER BY assets DESC) AS '순위', * from #TEST1

 

ROW_NUMBER()와 동시에 PARTITION BY 사용하였습니다. 조건은 직업입니다. 

 

 

 

...

오늘은 순위 함수와 그를 보조 하는 함수들을 배웠습니다.

 

아직까지 제가 모르는 많은 함수들이 아직도 MS SQL에 존재한다고 생각합니다.

 

더욱 더 알아가기 위해 노력하겠습니다. 

 

찾던 정보이기를 바랍니다.