[MSSQL] RANK를 사용하여 순위 매기기

[MSSQL] RANK를 사용하여 순위 매기기

WITH TEMP_TABLE_1 AS
(
SELECT '1반' AS [반정보], '일월~' AS [이름], 90 AS [국어], 80 AS [영어], 70 AS [수학]
 UNION ALL
SELECT '1반' AS [반정보], 'Feb!' AS [이름], 70 AS [국어], 60 AS [영어], 100 AS [수학]
 UNION ALL
SELECT '1반' AS [반정보], '3월@' AS [이름], 80 AS [국어], 80 AS [영어], 50 AS [수학]
 UNION ALL
SELECT '1반' AS [반정보], '사월#' AS [이름], 50 AS [국어], 80 AS [영어], 75 AS [수학]
 UNION ALL
SELECT '2반' AS [반정보], 'May$' AS [이름], 70 AS [국어], 65 AS [영어], 45 AS [수학]
 UNION ALL
SELECT '2반' AS [반정보], '6월%' AS [이름], 85 AS [국어], 90 AS [영어], 75 AS [수학]
 UNION ALL
SELECT '2반' AS [반정보], '칠월~' AS [이름], 95 AS [국어], 65 AS [영어], 70 AS [수학]
 UNION ALL
SELECT '2반' AS [반정보], 'Aug!' AS [이름], 50 AS [국어], 70 AS [영어], 95 AS [수학]
 UNION ALL
SELECT '3반' AS [반정보], '9월@' AS [이름], 85 AS [국어], 85 AS [영어], 85 AS [수학]
 UNION ALL
SELECT '3반' AS [반정보], '십월*' AS [이름], 60 AS [국어], 85 AS [영어], 70 AS [수학]
 UNION ALL
SELECT '3반' AS [반정보], 'Nov&' AS [이름], 80 AS [국어], 60 AS [영어], 65 AS [수학]
 UNION ALL
SELECT '3반' AS [반정보], '12월^' AS [이름], 100 AS [국어], 60 AS [영어], 75 AS [수학]
)

 

SELECT [반정보]
           , [이름]
           , [국어]
           , [영어]
           , [수학]
           , (CONVERT(INT, [국어]) + CONVERT(INT, [영어]) + CONVERT(INT, [수학])) AS 총점
           , (CONVERT(INT, [국어]) + CONVERT(INT, [영어]) + CONVERT(INT, [수학]))/3 AS 평균

          -- PARTITION BY를 사용하여 전체대상으로 총점으로 등수(석차) 매기기
           , RANK()OVER( ORDER BY (CONVERT(INT, [국어]) + CONVERT(INT, [영어]) + CONVERT(INT, [수학])) DESC, [국어] DESC, [영어] DESC, [수학] DESC) AS 전체등수

          -- PARTITION BY를 사용하여 반 마다 총점으로 등수(석차) 매기기
           , RANK()OVER(PARTITION BY [반정보] ORDER BY (CONVERT(INT, [국어]) + CONVERT(INT, [영어]) + CONVERT(INT, [수학])) DESC, [국어] DESC, [영어] DESC, [수학] DESC) AS 반별등수  
  FROM TEMP_TABLE_1

 

위 의 쿼리 실행 결과

 

SELECT   [반정보] 
           , [이름] 
           , (CONVERT(INT, [국어]) + CONVERT(INT, [영어]) + CONVERT(INT, [수학])) AS 총점 

           -- RANK() : 동일 점수일 경우 동일한 순위 표시 후 다음 순위는 동일한 순위의 수 만큼 건너뛰고 이어서 표시
           , RANK()OVER( ORDER BY (CONVERT(INT, [국어]) + CONVERT(INT, [영어]) + CONVERT(INT, [수학])) DESC) AS [RANK]

          -- DENSE_RANK() : 동일 점수일 경우 동일한 순위 표시 후 다음 순위는 바로 이어서 표시
           , DENSE_RANK()OVER( ORDER BY (CONVERT(INT, [국어]) + CONVERT(INT, [영어]) + CONVERT(INT, [수학])) DESC) AS [DENSE_RANK]

          -- ROW_NUMBER() : 동일 점수일 경우 동일한 순위로 표시하지 않고 테이블의 ROWNUM 순으로 순위 표시
           , ROW_NUMBER()OVER( ORDER BY (CONVERT(INT, [국어]) + CONVERT(INT, [영어]) + CONVERT(INT, [수학])) DESC) AS [ROW_NUMBER]

          -- NTILE() : 괄호 안의 수만큼 순위 표시
           , NTILE(3)OVER( ORDER BY (CONVERT(INT, [국어]) + CONVERT(INT, [영어]) + CONVERT(INT, [수학])) DESC) AS [NTILE(3)]
  FROM TEMP_TABLE_1
 ORDER
       BY (CONVERT(INT, [국어]) + CONVERT(INT, [영어]) + CONVERT(INT, [수학])) DESC

 

RANK, DENSE_RANK, ROW_NUMBER, NTILE 차이 쿼리 실행