[Oracle] RANK, DENSE_RANK, ROW_NUMBER, RANK OVER, RANK WITHIN GROUP (오라클 순위함수, 분석함수, 집계함수)

오라클에서 순위함수에 알아보는 시간입니다. 순위함수는 크게 분석함수로 사용하는 방법과 집계함수로 사용하는 방법이 있습니다. 순위함수를 각각의 방법에 대해 사용할 때에 어떠한 차이가 있는지 안내해 드리도록 하겠습니다. 목차는 다음과 같습니다.

순위함수 알아보기
RANK
DENSE_RANK
ROW_NUMBER
ROWNUM

RANK Function(Analytic Syntax)

순위함수에서 분석적 함수는 RANK, DENSE_RANK, ROW_NUMBER가 있습니다. 그리고 분석이라는 말을 생략하고 순위함수라고 지칭할 때에는 해당 함수와 같이 사용하겠다는 의미이기도 하답니다.

ROWNUM

흔히 사용할 수 있는 ROWNUM 수도칼럼(pseudo column)입니다.

예시자료 좌측은 ROWNUM을 조회 화면, 우측은 테이블 데이터 명세입니다.

ROWNUM은 오라클 내부정렬(예시의 경우 데이터 순서)로 순위가 반영되기 때문에 정확한 순위를 원하시면 인라인뷰를 사용하는 등 보완적인 작업이 필요합니다. 그리하여 대체로 사용할 수 있는 순위함수를 소개해드립니다.

SELECT 
       ROWNUM,
       T.*
  FROM EMP T

RANK Function(Analytic Syntax)

RANK 함수 3가지를 조회해 보았습니다.

각각 ROW_NUMBER, RANK, DENSE_RANK 입니다.

Rank Function 구문(syntax)

RANK () OVER ([query_partition_clause] order_by_clause)

대괄호[] 는 생략이 가능한 부분입니다. 파티션은 아래에서 설명드릴게요.

ORDER BY 기준으로 정렬하여 순위를 보여드리는 함수입니다.

ROW_NUMBER()

동일한 순위인 경우 내부정렬에 의해 순서가 겹치지 않게끔 정렬합니다. ROWNUM과 비슷한 기능을 하는데, 순서를 의도적으로 가공할 수 있다는 장점이 있습니다.

RANK()

동일한 순위인 경우 공동순위를 매기고 후순위를 공동순위만큼 건너띄어 순서로 설정합니다.

예시자료에서 'SCOTT' 과 'FORD'는 [SAL]이 3000으로 동일하여 공동 2위이며, 다음으로 [SAL]이 2975인 'JONES'는 4위입니다.

DENSE_RANK()

동일한 순위인 경우 공동순위를 매기는 방법입니다.

예시자료에서 'SCOTT' 과 'FORD'는 [SAL]이 3000으로 동일하여 같은 공동 2위이며, 다음으로 [SAL]이 2975인 'JONES'는 3위입니다.

SELECT 
       ROWNUM,
       ROW_NUMBER () OVER (ORDER BY SAL DESC) ROW_NUMBER,
       RANK () OVER (ORDER BY SAL DESC) RANK,
       DENSE_RANK () OVER (ORDER BY SAL DESC) DENSE_RANK,
       T.*
  FROM EMP T

PARTITION BY => RANK() OVER ([ query_partition_clause ] order_by_clause)

PARTITION BY 로 순위 파트를 구분할 수 있습니다.

PARTITION BY job ORDER BY sal DESC로 하였기에

job그룹별로 sal 순위를 보실 수 있습니다.

SELECT 
       RANK () OVER (PARTITION BY JOB ORDER BY SAL DESC) RANK,
       JOB,
       SAL,
       ENAME || '(' || EMPNO || ')'
  FROM EMP T

RANK Function(Aggregate Syntax)

상단에서는 분석(analytic)적 측면에서 접근하였다면, 이번에는 집계(aggregate)함수 측면에서 살펴보겠습니다.

[SAL]이 5000일 때 1위, 3000일 때 2위, ... 쭉 내려가보면 2450일 때 6위입니다.

만약 [SAL] 이 2500인 사람이 있었다면, 그 사람이 6위일 테고 2450인 'CLARK'는 7위가 되었을 것입니다.

그에 대한 함수를 소개해드리겠습니다.

RANK WITHIN GROUP

[SAL]이 2500 인 직원은 몇 위일지 집계하는 함수입니다. (2500이 6위이고 2450이 7위가 되겠죠?)

이 부분은 RANK 함수에 대해 적다가 집계구문도 알고 가시면 좋을 것 같아서 남깁니다.

RANK (Aggregate Syntax)

RANK (expr) WITHIN GROUP (order_by_clause [nulls_order_clause])

표현 expr 이 order by 절에서 어느 순위인지 확인하는 함수입니다.

SELECT RANK (2500) WITHIN GROUP (ORDER BY SAL DESC NULLS LAST) RANKWITHINGROUP
  FROM EMP T