[MSSQL] 한번에 끝내는 순위 함수 ROW_NUMBER, RANK, DENSE_RANK

소개

gibles-deepmind.tistory.com/5

 

SQL 관련 포스팅 링크를 모아둔 아카이브 페이지가 있습니다. 

 

글의 목표

gibles-deepmind.tistory.com/13

[MSSQL] ROW_NUMBER에 대한 고찰

지난 시간에 ROW_NUMBER에 대해서 다루었습니다. 이번 시간에는 ROW_NUMBER와 RANK, DENSE_RANK 함수를 비교해볼 것입니다.

1. 샘플 테이블 구성
2. 순위 함수 비교
3. ROW_NUMBER 사용 시 주의 사항

---

1. 샘플 테이블 구성

MSSQL에는 AdventureWorks라는 유명한 샘플데이터베이스가 있습니다. 

docs.microsoft.com/ko-kr/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms

AdventureWorks 예제 데이터베이스 - SQL Server

개념이 조금 헷갈리실 수도 있는데 설치 = 복원 입니다. 공식 문서 홈페이지에 설치 방법이 잘 설명되어 있으니 한번 따라해보시길 바랍니다.

SELECT [BusinessEntityID]
      ,[NationalIDNumber]
      ,[LoginID]
      ,[OrganizationNode]
      ,[OrganizationLevel]
      ,[JobTitle]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[Gender]
      ,[HireDate]
      ,[SalariedFlag]
      ,[VacationHours]
      ,[SickLeaveHours]
      ,[CurrentFlag]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2019].[HumanResources].[Employee]

오늘 사용해볼 샘플 테이블은 Employee입니다. 여러 컬럼들이 있는데 그 중 VacationHours라는 열을 이용해서 순위 함수에 대해 비교해볼 것입니다.

 

2. 순위 함수 비교

 

2-1. RANK

SELECT [BusinessEntityID]
	 , VacationHours
     , rank() over(order by [VacationHours]) as rank_num 
  FROM [AdventureWorks2019].[HumanResources].[Employee]

RANK는 ROW_NUMBER와 마찬가지로 순위를 부여하는 함수입니다. 

RANK에는 ROW_NUMBER와는 다른 재밌는 특징이 있는데요.

바로, 동순위일 경우 동일한 순위를 부여한다는 사실입니다.
하지만, 순위의 기준이 되는 열의 값이 변할 때 동순위 값의 개수 만큼을 합산하여 순위를 부여합니다.
이 내용은 1에서 4로 순위가 변하는 것에서 알 수 있습니다.

 

2-2. DENSE_RANK

SELECT [BusinessEntityID]
	 , VacationHours
     , dense_rank() over(order by [VacationHours]) as dense_rank_num 
  FROM [AdventureWorks2019].[HumanResources].[Employee]

DENSE_RANK역시 ROW_NUMBER와 마찬가지로 순위를 부여하는 함수입니다. 

하지만, RANK와는 다른 결정적인 차이가 있는데요. 혹시 발견하셨나요?

동순위일 경우 동일한 순위를 부여한다는 점에서는 DENSE_RANK와 RANK는 동일합니다.
하지만, 순위의 기준이 되는 열의 값이 변할 때 동순위 값의 개수를 합산하지 않고 순위를 부여합니다.
이 내용은 1에서 2로 순위가 변하는 것에서 알 수 있습니다.

1과4, 1과2 뭐가 더 가깝나요? 여기서 DENSE_라는 의미를 이해하셨을거라 생각합니다.

 

2-3. RANK, DENSE_RANK 누가 더 ROW_NUMBER와 닮았을까?

정답은 RANK입니다. 왜냐하면, RANK의 경우 동순위 값의 개수를 합산하여 다음 순위를 부여합니다. 그렇기 때문에 id만큼 순위가 부여되게 됩니다.

--각 함수별 마지막 순위
select top(1) rank() over(order by [VacationHours]) as rank_num
  FROM [AdventureWorks2019].[HumanResources].[Employee]
  order by 1 desc

select top(1) row_number() over(order by [VacationHours]) as row_num
  FROM [AdventureWorks2019].[HumanResources].[Employee]
  order by 1 desc

select top(1) dense_rank() over(order by [VacationHours]) as dense_rank_num
  FROM [AdventureWorks2019].[HumanResources].[Employee]
  order by 1 desc

row_number 함수는 행의 개수만큼 순위를 부여합니다. 각 함수별 마지막 순위를 비교했을 때 row_number의 순위와 rank의 순위가 가장 유사한 것을 알 수 있습니다.

 

3. ROW_NUMBER 사용시 주의 사항

ROW_NUMBER와 RANK, DENSE_RANK 간의 가장 큰 차이는 무엇일까요?

바로, ROW_NUMBER()에는 동순위에 대한 처리 규칙이 없다는 것입니다.

그렇다면, ROW_NUMBER()는 동순위에 대해서 어떻게 처리할까요? 쿼리 환경마다 다릅니다. 

select *
from (
	SELECT [BusinessEntityID]
		 , VacationHours
		 , row_number() over(order by [VacationHours]) as row_num 
	  FROM [AdventureWorks2019].[HumanResources].[Employee]
	) A
where row_num = 1

예를 들어, 다음과 같이 VacationHouers가 가장 적은 사원아이디를 구한다고 가정해봅시다. 어떤 환경에서는 BusinessEntityID가 키값으로 고정되어 동일한 값이 계속 출력될 수도 있습니다.

 

하지만, VacationHours가 0인 사원은 3명입니다. 어떤 환경에서는 출력할 때마다 BusinessEntityID가 다르게 나올 수 있습니다. 결론적으로 ROW_NUMBER()는 쿼리 결과의 일관성을 보장하지 못할 수도 있습니다.

 

이럴 경우 동순위 처리 규칙을 부여해야 합니다. 대표적인 방법중 하나로 정렬 컬럼을 하나 더 추가하는 방법이 있습니다. 즉, 두가지 컬럼을 기준으로 정렬하여 아예 동순위가 되는 상황을 없애버리는 것입니다.