[오라클/ORACLE] 순위함수 RANK(), ROW_NUMBER(), DENSE_RANK() 분석함수 사용법 차이점 개념

1. 분석 함수의 구문 형식과 기본

---

SELECT RANK() || ROW_NUMBER() || DENSE_RANK()
       OVER ( [ PARTITION BY 컬럼명 ]  ORDER BY 절  [ WINDOWING 절 ] )
FROM 테이블명 ;

 

순위 함수는 ORDER BY를 포함한 쿼리문에서 특정 정렬에 대한 순위를 구하는 함수이다.

이 때 WINDOWING절의 PARTITION BY를 사용해서 구분 내 순위를 구할 수도,

전체 데이터에 대한 순위를 구할 수도 있다.

 

이제 간단한 예제와 아래 실습용 데이타를 통해서 사용 방법을 알아보겠습니다.

Windowin 절이란?  Click Me

실습용 TABLE ( VIEW_EMPINFO ) 의 데이타

2. 순위 함수 사용 예제

---

1. RANK 함수       - 동일한 값에 대해서는 동일한 순서를 반환 

2. DENSE_RANK   - 동일한 순위를 하나의 건수로 취급 

3. ROW_NUMBER - 동일한 값이여도 고유의 순위를 부여 

 

SELECT EMP_NO, SAL, 
       RANK() OVER (ORDER BY SAL ) AS "RANK()",
       DENSE_RANK() OVER (ORDER BY SAL ) AS "DENSE_RANK()",
       ROW_NUMBER() OVER ( ORDER BY SAL ) AS "ROW_NUMBER()"
FROM VIEW_EMPINFO;

쿼리 실행 결과

쿼리의 전체 실행은 SAL을 기준으로 정렬 조건 ( ORDER BY SAL ) 을 부여하였다.

 

첫번째로 RANK( ) 의 경우 SAL이 동일할 경우 같은 순번을 부여 다음 순번이 순서를 건너 뛰는 형식으로 숫자를 부여함

[EMP_NO]가 3, 4번인 경우 동일하게 [3순위]를 부여 [EMP_NO]가 5번인 사람은 다음 순위인 4가아닌 전체 5순위를 부여

 

두번째로 DENSE_RANK( ) 의 경우 동일할 경우 같은 순번을 부여 다음 순번이 순서를 유지하는 형식으로 순번을 부여

[EMP_NO]가 3, 4번인 경우 동일하게 [3순위]를 부여 [EMP_NO]가 5번인 사람은 다음 순위인 4를 부여

   

세번째로 ROW_NUMBER( ) 의 경우 값이 동일 해도 다른 순번을 부여

만약 값이 같을 경우 조건을 주고 싶다면 WINDOWING 절을 이용

 

---

나아가 해당 데이터에서 부서별로 급여의 랭킹을 부여하고 싶다면 PARTITION 을 활용할 수가 있다.

위 예제처럼 전체 순위에 대한 순번이 아닌 DEPT_NO 별로 SAL의 순번을 부여한다.

DENSE_RANK( ) / ROW_NUMBER( ) 도 동일한 방식으로 사용 가능

 

SELECT EMP_NO
      ,DEPT_NO
      ,SAL
      ,RANK() OVER ( PARTITION BY DEPT_NO ORDER BY SAL ) PARTITION_RANK 
FROM VIEW_EMPINFO ;

3. 조회 순번 매기기 ( ROWNUM ) 사용

---

FROM절에서 서브쿼리를 실행 ( 인라인 뷰 ) 를 이용하면 자동적으로 쿼리 결과에 대한 ROWNUM이 적용이 된다.

그 ROWNUM에 조건을 주어서 원하는 순위를 추출 아래 예제는 SAL이 제일큰 5명을 추출한다.

 

SELECT ROWNUM , EMP_NO, SAL
FROM
(
 SELECT EMP_NO, SAL
 FROM VIEW_EMPINFO
 ORDER BY SAL DESC
)
WHERE ROWNUM BETWEEN 1 AND 5;

쿼리 실행 결과