오라클 『RANK() OVER()』

■ RANK() OVER()

RANK()  OVER() → 등수 (순위)를 반환하는 함수이다.

기본 예제를 살펴보자.

 

RANK() 예제 1) 

SELECT EMPNO 사원번호, ENAME 사원명, DEPTNO 부서번호, SAL 급여
     , RANK() OVER(ORDER BY SAL DESC) "전체 급여 순위"
FROM EMP;

위 코드에서 RANK() OVER(ORDER BY SAL SESC) 라는 구문은 SAL(급여)가 높은순서로 등수를 반환하겠다는 뜻이다.

결과 값은 다음과 같다.

 

RANK() 예제 1 결과)

	                       부서 전체
7839	KING	10	5000	1	1
7902	FORD	20	3000	1	2
7788	SCOTT	20	3000	1	2
7566	JONES	20	2975	3	4
7698	BLAKE	30	2850	1	5
7782	CLARK	10	2450	2	6
7499	ALLEN	30	1600	2	7
7844	TURNER	30	1500	3	8
7934	MILLER	10	1300	3	9
7521	WARD	30	1250	4	10
7654	MARTIN	30	1250	4	10
7876	ADAMS	20	1100	4	12
7900	JAMES	30	950 	6	13
7369	SMITH	20	800	5	14

PARTITION BY 함수를 사용해보자.

PARTITION BY 말 그대로 BY 다음으로 오는 컬럼으로 나누고 조건으로 등수를 반환하겠다는 것이다.

예제를 보면 간단하게 이해할 수 있다.

 

RANK() 예제 2)

SELECT EMPNO 사원번호, ENAME 사원명, DEPTNO 부서번호, SAL 급여
     , RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "부서별급여순위"
     , RANK() OVER(ORDER BY SAL DESC) "전체급여순위"
FROM EMP
ORDER BY DEPTNO;

RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)라는 구문은

부서번호가 같은 사람들을 비교하여 급여가 높은 순서대로 등수를 반환한다는 뜻이다.

 

RANK() 예제 2 결과)

	     부서번호          부서   전체
7839	KING	10	5000	1	1
7782	CLARK	10	2450	2	6
7934	MILLER	10	1300	3	9
7902	FORD	20	3000	1	2
7788	SCOTT	20	3000	1	2
7566	JONES	20	2975	3	4
7876	ADAMS	20	1100	4	12
7369	SMITH	20	800	5	14
7698	BLAKE	30	2850	1	5
7499	ALLEN	30	1600	2	7
7844	TURNER	30	1500	3	8
7654	MARTIN	30	1250	4	10
7521	WARD	30	1250	4	10
7900	JAMES	30	950 	6	13

결과를 보면 부서번호가 같은 사람들의 급여를 비교하여 등수를 반환하는 것을 알 수 있다. 

등수와 관련된 함수중 또 다른 함수도 있다.

 

바로 DENSE_RANK() 이다. 

서열을 반환하는 함수로 1등이 2명이어도 3등이 아닌 2등으로 반환해주는 함수이다.

 

DENSE_RANK()  예제 1)

SELECT EMPNO 사원번호, ENAME 사원명, DEPTNO 부서번호, SAL 급여
     , DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "부서별급여서열"
     , DENSE_RANK() OVER(ORDER BY SAL DESC) "전체급여서열"
FROM EMP
ORDER BY 3,4 DESC;

RANK() 함수와 작성하는 방법은 크게 다르지 않다. RANK() 함수 자리에 DENSE_RANK() 를 넣으면 된다.

바로 결과를 알아보자.

 

DENSE_RANK()  예제1 결과)

7839	KING	10	5000	1	1
7782	CLARK	10	2450	2	5
7934	MILLER	10	1300	3	8

7902	FORD	20	3000	1	2
7788	SCOTT	20	3000	1	2
7566	JONES	20	2975	2	3        -- 앞에 동일한 결과가 있어도 1 → 3 이 아니라 1 → 2가 온다.
7876	ADAMS	20	1100	3	10      
7369	SMITH	20	800	4	12


7698	BLAKE	30	2850	1	4
7499	ALLEN	30	1600	2	6
7844	TURNER	30	1500	3	7
7654	MARTIN	30	1250	4	9       
7521	WARD	30	1250	4	9
7900	JAMES	30	950	5	11	-- 앞에 동일한 결과가 있어도 4 → 6 이 아니라 4 → 5가 온다.

20번 부서의 FORD 와 SCOTT 이 공동 1등이지만 JONES의 등수는 3등이 아닌 2등이라는 것을 알 수 있다.

위의 RANK() 함수와는 다른 결과 값이다.

마찬가지로 30번 부서의 MARTIN 과 WARD가 공동 4등이지만 JAMES의 등수는 5등으로 나온다.

 

RANK() 함수를 사용하여 다양한 예제를 풀어보자.

EMP 테이블의 사원 데이터를 사원명, 부서번호, 연봉, 부서내 연봉 순위, 전체 연봉 순위 항목으로 조회해보자

 

RANK() 예제 3-1)

SELECT ENAME 사원명, DEPTNO 부서번호, SAL*12+NVL(COMM,0) "연봉"
     , RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL*12+NVL(COMM,0) DESC) "부서내연봉순위" 
     , RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) "전체연봉순위"
FROM EMP
ORDER BY DEPTNO;

RANK() 예제 3-2) 서브쿼리문 사용

SELECT T.* 
      ,RANK() OVER(PARTITION BY T.부서번호 ORDER BY T.연봉 DESC) "부서내연봉순위"
      ,RANK() OVER(ORDER BY T.연봉 DESC)"전체연봉순위"
FROM
(
    SELECT ENAME 사원명, DEPTNO 부서번호, SAL*12+NVL(COMM,0) "연봉"
    FROM EMP
) T
ORDER BY T.부서번호;

RANK() 예제 3 결과)

KING	10	60000	1	1
CLARK	10	29400	2	6
MILLER	10	15600	3	10
FORD	20	36000	1	2
SCOTT	20	36000	1	2
JONES	20	35700	3	4
ADAMS	20	13200	4	12
SMITH	20	9600	5	14
BLAKE	30	34200	1	5
ALLEN	30	19500	2	7
TURNER	30	18000	3	8
MARTIN	30	16400	4	9
WARD	30	15500	5	11
JAMES	30	11400	6	13

EMP 테이블에서 전체연봉순위가 1등부터 5등까지만 사원명, 부서번호, 연봉, 전체 연봉순위 항목으로 조회한다.

 

RANK() 예제 4) 서브쿼리문 사용

SELECT T.*
FROM
(
    SELECT ENAME 사원명, DEPTNO 부서번호 , SAL*12+NVL(COMM,0) 연봉
          ,RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) "전체연봉순위"
    FROM EMP
) T
WHERE 전체연봉순위 < 6;

RANK() 예제 4 결과)

KING	10	60000	1
SCOTT	20	36000	2
FORD	20	36000	2
JONES	20	35700	4
BLAKE	30	34200	5