ROW_NUMBER() / RANK() 사용법 및 차이점

오늘은 RANK(), ROW_NUMBER() 비교하여 사용하여 보았다.

--테이블 
WITH CTE AS
(
SELECT 'LEVEL1' AS A,'LEVEL11' AS B,'O' AS C UNION
SELECT 'LEVEL1','LEVEL12','O' UNION
SELECT 'LEVEL1','LEVEL13','O' UNION
SELECT 'LEVEL1','LEVEL14','O' UNION
SELECT 'LEVEL1','LEVEL15','A' UNION
SELECT 'LEVEL1','LEVEL11','B' UNION
SELECT 'LEVEL1','LEVEL11','C' UNION
SELECT 'LEVEL1','LEVEL12','D' UNION
SELECT 'LEVEL1','LEVEL13','E' UNION
SELECT 'LEVEL1','LEVEL14','F' UNION
SELECT 'LEVEL1','LEVEL15','G' UNION
SELECT 'LEVEL1','LEVEL11','H'
)
SELECT 'RANK', A, B, C, RANK() OVER (PARTITION BY A ORDER BY B) AS D FROM CTE
UNION
SELECT 'ROW_NUMBER', A, B, C, ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) AS D FROM CTE
UNION
SELECT 'DENSE_RANK', A, B, C, DENSE_RANK() OVER (PARTITION BY A ORDER BY B) AS D FROM CTE

차이가 보이는가 RANK()는 동일한게 나오면 같은 순위를 매기지만 ROW_NUMBER는 ORDER BY 순으로 순위를 매긴다.

DENSE_RANK는 동일순위가 나와도 그다음 번호로 순위를 매긴다.

 

           RANK                                                            ROW_NUMBER 

 

 

DENSE_RANK()