오라클 RANK 조건 - olakeul RANK jogeon

테이블에서 특정 값을 기준으로 순위를 매겨서 보고 싶을 때 ORDER BY 절을 사용합니다. 동일한 점수가 나온 경우 순위를 동일하게 하거나 또는 점수가 동일하더라도 순위를 다르게 매겨야 하는 경우가 있습니다. Oracle에서는 순위를 나타날 때 편하게 사용할 수 있도록 RANK, DENSE_RANK, ROW_NUMBER 함수를 제공합니다.

순위 함수

Oracle에서 제공하는 순위 함수는 아래와 같습니다. 해당 함수에 대한 설명보다는 실습을 통해 이해하는 것이 더 쉬울 수 있습니다.

• RANK : 동일한 값이면 중복 순위를 부여하고, 다음 순위는 해당 개수만큼 건너뛰고 반환한다.
• DENSE_RANK : 동일한 값이면 중복 순위를 부여하고, 다음 순위는 중복 순위와 상관없이 순차적으로 반환한다.
• ROW_NUMBER : 중복 관계없이 순차적으로 순위를 반환한다.

RANK 함수

RANK 함수는 동일한 값이면 중복 순위를 부여하고, 다음 순위는 해당 개수만큼 건너뛰고 반환합니다. RANK 함수를 사용해서 조회한 결과를 보면, SAL(급여) 값이 동일한 경우에는 순위 값이 '2'로 중복 순위를 부여합니다. 중복 순위 다음에는 순위 값이 '4'입니다. 앞에서 1등 1명, 2등 2명이 존재하기 때문에 다음 순위의 값은 '4'입니다.

SELECT 컬럼1, 컬럼2, 컬럼3, RANK() OVER (ORDER BY 기준_컬럼 DESC) AS 별명 FROM 테이블;

Oracle RANK 함수 사용 예제

DENSE_RANK 함수

DENSE_RANK 함수는 동일한 값이면 중복 순위를 부여하고, 다음 순위는 중복 순위와 상관없이 순차적으로 반환합니다. DENSE_RANK 함수를 사용해서 조회한 결과를 보면, SAL(급여) 값이 동일한 경우에는 순위 값이 '2'로 중복 순위를 부여합니다. 중복 순위 다음에는 순위 값이 '3'입니다. 앞에서 1등 1명, 2등 2명이 존재하지만, 다음 순위의 값은 '3'입니다. 이 점이 RANK 함수와 차이점입니다. RANK 함수에서는 순위 값을 '3'이 아니라 '4'를 부여했었습니다.

SELECT 컬럼1, 컬럼2, 컬럼3, DENSE_RANK() OVER (ORDER BY 기준_컬럼 DESC) AS 별명 FROM 테이블;

Oracle DENSE_RANK 함수 사용 예제

ROW_NUMBER 함수

ROW_NUMBER 함수는 중복 관계없이 순차적으로 순위를 반환합니다. 그렇기 때문에 동일한 점수여도 중복 순위가 없습니다. 아래 이미지에서 'SCOTT', 'FORD'는 SAL(급여) 값이 동일하더라도 순위 값이 '2', '3'으로 출력 되는 것을 확인할 수 있습니다.

지금까지 순위를 결정하는 컬럼을 1개만 사용했는데, 순위를 결정하는 다른 컬럼을 사용해도 됩니다. 기존 "ROW_NUMBER() OVER (ORDER BY SAL DESC) AS RANK" 문장을 "ROW_NUMBER() OVER (ORDER BY SAL DESC, HIRE_DATE DESC) AS RANK" 으로 변경합니다. 변경하고 다시 조회하면 먼저 SAL(급여)로 내림차순하고, 급여가 동일한 경우 HIRE_DATE(고용날짜)를 기준으로 다시 차등 순위를 매길 수 있습니다.

SELECT 컬럼1, 컬럼2, 컬럼3, ROW_NUMBER() OVER (ORDER BY 기준_컬럼 DESC) AS RANK FROM 테이블;

Oracle ROW_NUMBER 함수 사용 예제

그룹별 순위

Rank 함수(또는 DENSE_RANK, ROW_NUMBER)를 이용해서 그룹별 순위를 매길 수 있습니다. 예를 들어, 부서별 직원들 급여를 순위를 매길 때 아래 쿼리를 사용하면 됩니다. 어떤 순위 함수를 쓰는지에 따라 순위 결과 값은 다르게 나옵니다.

-- EMP 테이블에서 부서별 직원들 급여 순위 SELECT DEPTNO, EMPNO, ENAME, SAL, RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS RANK FROM SCOTT.EMP;

Oracle 그룹별 순위 함수 사용 예제

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절 컬럼이 유니크 하지 않을때는 같은 값에 대한 정렬을 어떻게 처리하는지 반드시 생각해야 합니다.