[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
