오라클 SQL과 PL/SQL ( 랭킹/순위 함수)

RANK()  함수 - 순위출력함수

RANK함수는 주어진 컬럼 값의 그룹에서 값의 순위를 계산한 후 순위를 출력해 줍니다.
같은 순위를 가지는 순위 기준에 대해서는 같은 출력 값을 가지기 때문에 RANK함수의 출력결과가 연속하지 않을 경우가 있습니다.
이 함수는 TOP-N 과 BOTTOM-N 등 순위를 출력하는 리포팅 작업에 아주 유용하게 사용할 수 있습니다.
이 함수는 특정 데이터의 순위만 볼 수도있고 전체 데이터의 순위를 다 볼 수도 있는데, 두 가지의 경우 문법이 약간 다릅니다. 예제로 확인해보겠습니다.

특정 데이터의 순위 확인하기
RANK(조건값) WITHIN GROUP (ORDER BY 조건값 컬럼명 [ASC | DESC] )

 

SELECT ENAME FROM EMP ORDER BY ENAME;

SELECT RANK('SMITH') WITHIN GROUP (ORDER BY ENAME) "RANK"
FROM EMP;

 

11번째의 SMITH 가 잘 출력되는 것을 볼 수 있습니다.

위 예는 ENAME로 ORDER BY한 후 SMITH가 몇번째 나오는지 순위를 구한 것입니다.
이때 주의사항은 RANK 뒤에 나오는 데이터와 ORDER BY 뒤에 나오는 데이터는 같은 컬럼이어야 합니다.
즉 위의 예처럼 이름으로 정렬한 후 이름의 순위를 구해야 하는데 다른 컬럼일 경우 에러가 발생합니다.

전체 순위 확인하기
RANK() 뒤가 WITHIN GROUP가 아니고 OVER로 바뀝니다.

RANK(조건값) OVER (ORDER BY 조건값 컬럼명 [ASC | DESC] )

 

사용 예1. EMP 테이블에서 사원들의 EMPNO, ENAME, SAL, 급여 순위를 출력하세요.

SELECT 	EMPNO, ENAME, SAL, 
	RANK() OVER (ORDER BY SAL) AS RANK_ASC, 
	RANK() OVER (ORDER BY SAL DESC) AS RANK_DESC
FROM EMP;

사용 예2. EMP테이블에서 10번 부서에 속한 직원들의 사번과 이름 급여 해당부서 내의 급여 순위를 출력하세요.

SELECT EMPNO, ENAME, SAL, RANK () OVER(ORDER BY SAL DESC) "RANK"
FROM EMP
WHERE DEPTNO = 10;

 

사용 예3. EMP 테이블을 조회하여 사번, 이름, 급여, 부서번호, 부서별 급여 순위를 출력하세요.

SELECT 	EMPNO, ENAME, SAL, DEPTNO, 
		RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK"
FROM EMP;

위의 조건을 보면 PARTITION BY라는 구문이 등장합니다. 이 구문 뒤에 그룹핑을 할 컬럼을 적어주면 됩니다.
위의 예는 부서별 순위를 구하기 위해 DEPTNO를 적어준 것입니다.

 

 

사용 예4. EMP테이블을 조회하여 EMPNO, ENAME, SAL, DEPTNO, 같은 부서 내 JOB별로 급여 순위를 출력하세요.

SELECT 	EMPNO, ENAME, SAL, DEPTNO,
		RANK() OVER(PARTITION BY DEPTNO, JOB ORDER BY SAL DESC) "RANK"
FROM EMP;

위의 결과화면을 보면 같은 부서번호 내에서 JOB별로 RANK가 나오는 것을 확인 할 수있습니다.

 

DENSE_RANK 순위 함수

RANK와 비슷합니다. 하지만 동일한 순위를 하나의 건수로 취급하므로 연속된 순위를 보여줍니다.

SELECT EMPNO, ENAME, SAL,
	RANK() OVER (ORDER BY SAL DESC) "SAL_RANK",
	DENSE_RANK () OVER (ORDER BY SAL DESC) "SAL_DENSE_RANK"
FROM EMP;

블록처리한 부분을 잘 비교하면 순위가 다름을 알 수 있습니다.

 

ROW_NUMBER() 순위 함수

RANK나 DENSE_RANK함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해 ROW_NUMBER함수는 동일한 값이라도 고유한 순위를 부여합니다. 오라클에 경우 ROWID가 작은 값에 먼저 순위를 부여합니다.
중복된 순위가 없어서 RANK나 DENSE_RANK함수보다 더 많이 사용을 하는 편입니다.

 

SELECT EMPNO, ENAME, SAL,
	RANK() OVER (ORDER BY SAL DESC) "SAL_RANK",
	DENSE_RANK () OVER (ORDER BY SAL DESC) "SAL_DENSE_RANK",
	ROW_NUMBER () OVER (ORDER BY SAL DESC) "SAL_ROW_NUM"
FROM EMP;

 

이번에는 위에서 배운 순위 함수를 다 사용해서 순위를 매겨봅시다.
부서 번호가 10, 20번인 사원에서 부서별로 급여가 낮은 순으로 순위를 부여합니다.

SELECT 	DEPTNO, SAL, EMPNO,
		ROW_NUMBER 	() OVER (PARTITION BY DEPTNO ORDER BY SAL) 		"ROW_NUMBER1",
		RANK		() OVER (PARTITION BY DEPTNO ORDER BY SAL) 		"RANK1",
		DENSE_RANK	() OVER (PARTITION BY DEPTNO ORDER BY SAL) 		"DNESE_RANK1",
		ROW_NUMBER 	() OVER (PARTITION BY DEPTNO ORDER BY SAL, EMPNO) 	"ROW_NUMBER2",
		RANK		() OVER (PARTITION BY DEPTNO ORDER BY SAL, EMPNO) 	"RANK2",
		DENSE_RANK	() OVER (PARTITION BY DEPTNO ORDER BY SAL, EMPNO) 	"DNESE_RANK2"
FROM EMP
WHERE DEPTNO IN('10', '20')
ORDER BY DEPTNO , SAL , EMPNO;

 

정렬이 다른 두 가지의 경우 차이점을 보면 1번 그룹 정렬에서 SAL컬럼으로만 정렬을 했기 때문에 중복된 데이터가 발생할 수 있습니다. 실제로 부서번호 20번인 경우 3000 급여가 중복되었습니다.
SAL 컬럼 값이 중복 발생했을 때 정렬처리를 고려하지 않았습니다.
그래서 1번 그룹의 RANK와 DENSE_RANK는 중복된 순위가 발생하였습니다.
유일한 순위를 부여한다면 ROW_NUMBER함수를 사용하면 됩니다. 오라클은 ROWID로 순위를 부여하기 때문입니다.

하지만 ROWID가 바뀐다면 항상 같은 결과의 순위를 보여줄 수 없습니다.
ROWID는 오라클에서 고유한 번호(학번이나 주민등록번호) 같은 개념입니다.

ROWID가 바뀌는 것은 해당 DATA를 지우고 다시 INSERT하는 경우입니다.
보통 데이터 마이그레이션 후에 정렬된 순서가 틀린 경우가 있는데, 그것은 정렬 순서를 명확하게 지정하지 않아서 입니다. 즉 정렬 컬럼의 중복된 값에 대한 처리를 하지 않았습니다.

항상 같은 정렬순서를 보장하기 위해서 반드시 유니크한 컬럼 (PK 등) 을 기술하는 습관을 들이도록 합시다.
그리고 ORDER BY절 컬럼이 유니크 하지 않을때는 같은 값에 대한 정렬을 어떻게 처리하는지 반드시 생각해야 합니다.