오라클 RANK , DENSE_RANK 이것만 알면 실무에서도 한사람 역할은 한다

 

ORACLE RANK

 

RANK 함수는 일반적으로 매출, 영업, 성적 등의 순위를 표현할 때 사용됩니다.

어렵지 않지만 자주 사용되는 함수이니 사용법을 잘 숙지해두시면

평소 업무도 뚝딱 해결할 수 있으니 같이 공유해요^^

ORACLE 에서 제공하는 RANK 문법은 아래와 같습니다.

 

 

RANK 문법

 

 

RANK 함수 사용하는 방법은

아래의 EMP 테이블을 예제로 설명드리겠습니다.

잠깐!

간단한 질문과 결과를 올려놓았으니 

잠시 생각하시고 SQL 문을 확인하시면 조금 더 도움이 되실꺼에요!

 

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980-12-17	800	(null)	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30
7566	JONES	MANAGER	7839	1981-04-02	2975	(null)	20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850	(null)	30
7782	CLARK	MANAGER	7839	1981-06-09	2450	(null)	10
7788	SCOTT	ANALYST	7566	1987-04-19	3000	(null)	20
7839	KING	PRESIDENT	(null)	1981-11-17	5000	(null)	10
7844	TURNER	SALESMAN	7698	1981-09-08	1500	0	30
7876	ADAMS	CLERK	7788	1987-05-23	1100	(null)	20
7900	JAMES	CLERK	7698	1981-12-03	950	(null)	30
7902	FORD	ANALYST	7566	1981-12-03	3000	(null)	20
7934	MILLER	CLERK	7782	1982-01-23	1300	(null)	10

 

 

자 그럼 기본적인 사용법부터 확인해볼까요?

---

RANK()

1. 직원의 급여가 높은 순서대로 순위를 아래와 같이 조회해보세요.

  ( 항목 추출 : EMPNO, ENAME, SAL, 순위 )

< 결과 >

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

SELECT EMPNO
     , ENAME
     , SAL
     , RANK() OVER(ORDER BY SAL DESC) AS RK 
  FROM EMP

확인해보니 정말 간단하죠??

어떤항목으로 입력하느냐에 따라 순위가 정해집니다.

DENSE_RANK()

2,3번째 row를 보면 FORD 와 SCOTT 은 같은급여를 받고 있습니다.

다음 순위의 급여를 받는 JONES의 순위가 3위가 아니라 4위가 되었습니다.

3위로 출력하고 싶은 경우는 어떻게 할까요?

2. 순위의 공백이 없도록 DENSE_RANK 를 사용하여 조회해보세요.

< 결과 >

EMPNO	ENAME	SAL	RK
7839	KING	5000	1
7902	FORD	3000	2
7788	SCOTT	3000	2
7566	JONES	2975	3
7698	BLAKE	2850	4
7782	CLARK	2450	5
7499	ALLEN	1600	6
7844	TURNER	1500	7
7934	MILLER	1300	8
7521	WARD	1250	9
7654	MARTIN	1250	9
7876	ADAMS	1100	10
7900	JAMES	950	11
7369	SMITH	800	12

SELECT EMPNO
     , ENAME
     , SAL
     , DENSE_RANK() OVER(ORDER BY SAL DESC) AS RK 
  FROM EMP

위에서 작성한것중 RANK 함수를 사용하느냐 DENSE_RANK 를 사용하느냐의 차이만 존재하네요^^

ROW_NUMBER()

동일한 급여에도 순위가 나뉘도록 조회해보겠습니다.

3. 동일한 순위를 인정하지 않도록 조회해보세요.

< 결과 >

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

SELECT EMPNO
     , ENAME
     , SAL
     , ROW_NUMBER() OVER(ORDER BY SAL DESC) AS RK 
  FROM EMP

ROW_NUMBER 함수를 사용하면 중복된 순위가 나오지 않는다는 것만 확인하시면 되겠네요!

자, 위와 같이 조회하면 어떤 기준으로 2등과 3등이 나뉜지 알 수 없습니다.

"2등과 3등의 차이가 뭐야?" 라고 물어본다면 대답할 수 없으니

우리는 명시적으로 그 차이를 지정하는 기준을 만들어볼께요!

4. 급여는 같지만 입사일이 빠른경우를 상위순위로 배치하도록 조회해보세요.

순위를 산정하는 방식이 2가지 인것을 생각해보면 쉬워요~ ( 급여, 입사일 )

< 결과 >

EMPNO	ENAME	SAL	HIREDATE	RK
7839	KING	5000	1981-11-17	1
7902	FORD	3000	1981-12-03	2
7788	SCOTT	3000	1987-04-19	3
7566	JONES	2975	1981-04-02	4
7698	BLAKE	2850	1981-05-01	5
7782	CLARK	2450	1981-06-09	6
7499	ALLEN	1600	1981-02-20	7
7844	TURNER	1500	1981-09-08	8
7934	MILLER	1300	1982-01-23	9
7521	WARD	1250	1981-02-22	10
7654	MARTIN	1250	1981-09-28	11
7876	ADAMS	1100	1987-05-23	12
7900	JAMES	950	1981-12-03	13
7369	SMITH	800	1980-12-17	14

SELECT EMPNO
     , ENAME
     , SAL
     , HIREDATE
     , DENSE_RANK() OVER(ORDER BY SAL DESC, HIREDATE) AS RK 
  FROM EMP

ORDER BY 뒤에 몇개의 항목이든 입력하면 순서대로 순위를 정해요

만약 입사일이 늦은경우를 상위순위로 산정하고 싶다면 HIREDATE DESC 항목을 입력하면 되겠죠?

여기까지 보셨다면 기본적인 RANK 의 사용법을 알게 되었습니다.

아래부터는 RANK 함수를 응용한 내용을 공유해보겠습니다.

---

RANK 응용1

 - 급여가 가장 높은 사람이 누구인지 1명만 조회하세요.

  ( 지금까지는 모든 사용자들을 전부 조회했지만 이번엔 1명만 조회되었네요! )

< 결과 >

ENAME

KING

SELECT ENAME
  FROM (
        SELECT ENAME
             , RANK() OVER(ORDER BY SAL DESC) RN
          FROM EMP
  ) 
 WHERE RN = 1

서브쿼리를 사용하여 RANK 를 구한 뒤 마지막에 1등인 사람만을 조회하도록 처리했어요!

위와 같은 결과지만 좀 더 쉽게 조회 할 수 있는 방법도 있습니다.

바로 오라클의 KEEP / FIRST / LAST 문법을 사용하는것인데 MAX 값을 조회 후

다른 컬럼의 값을 바로 조회할 수 있습니다!

SELECT MAX(ENAME) KEEP(DENSE_RANK FIRST ORDER BY SAL DESC) AS ENAME
  FROM EMP

위에서는 서브쿼리를 사용했지만 이번에는 한번에 해결했어요!

조금 더 자세한 내용을 알고 싶다면 FIRST, LAST, KEEP 포스트를 확인해주세요~ ( 바로확인하기 )

RANK 응용2

 - 총급여가 많은 부서별 순위를 조회하세요.

부서별로 급여의 순위는 단순하게 처리가 가능합니다.

부서로 GROUP BY 후 ORDER BY 에서 지정한 정렬컬럼을 SUM 처리하면 됩니다.

< 결과 >

DEPTNO	DNAME	SAL	RK
20	RESEARCH	10875	1
30	SALES	9400	2
10	ACCOUNTING	8750	3

SELECT T1.DEPTNO
     , T2.DNAME
     , SUM(SAL) AS SAL
     , RANK() OVER(ORDER BY SUM(SAL) DESC) AS DEPT_RANK
  FROM EMP T1
 INNER JOIN DEPT T2
    ON T1.DEPTNO = T2.DEPTNO
 GROUP BY T1.DEPTNO
        , T2.DNAME

부서명을 조회하기 위해 DEPT 테이블과 JOIN 하여 처리했지만

여기서 중요하게 확인할 부분은 RANK 의 ORDER BY 부분에서 집계함수(SUM)를 사용할 수 있다는 점을 확인하시면 됩니다!

RANK 응용3

 - 부서별로 급여를 가장 많이 받는 사람을 조회하세요.

RANK 함수를 사용할 때 PARTITION BY 를 사용하면 해당 컬럼을 파티션으로 묶어 순위를 부여할 수 있습니다.

< 결과 >

DEPTNO	DNAME	EMPNO	ENAME	SAL	RK
10	ACCOUNTING	7839	KING	5000	1
10	ACCOUNTING	7782	CLARK	2450	2
10	ACCOUNTING	7934	MILLER	1300	3
20	RESEARCH	7902	FORD	3000	1
20	RESEARCH	7788	SCOTT	3000	1
20	RESEARCH	7566	JONES	2975	3
20	RESEARCH	7876	ADAMS	1100	4
20	RESEARCH	7369	SMITH	800	5
30	SALES	7698	BLAKE	2850	1
30	SALES	7499	ALLEN	1600	2
30	SALES	7844	TURNER	1500	3
30	SALES	7521	WARD	1250	4
30	SALES	7654	MARTIN	1250	4
30	SALES	7900	JAMES	950	6

SELECT T1.DEPTNO
     , T2.DNAME
     , T1.EMPNO
     , T1.ENAME
     , T1.SAL
     , RANK() OVER(PARTITION BY T1.DEPTNO ORDER BY T1.SAL DESC) AS DEPT_RANK
  FROM EMP T1
 INNER JOIN DEPT T2
    ON T1.DEPTNO = T2.DEPTNO

이번에는 RANK 함수에서 PARTITION BY 문법을 사용하여 부서별로 파티션을 나눠 순위를 부여하였습니다.

PARTITION BY 역시 여러항목을 묶어서 사용할 수 있다는 것을 짚고 넘어가시면 될 것 같습니다!

이상으로 오라클 RANK 함수에 대해 알아보았습니다.

도움이 되셨거나 추가질문이 있다면 댓글 남겨주세요~^^