Oracle Advanced SQL 강좌
순위함수 2 5 17,437

by 마농 RANK DENSE_RANK ROW_NUMBER NTILE 순위함수 분석함수 [2014.01.14]


순위함수

  • - RANK 함수는 순위를 부여하는 함수로 동일순위 처리가 가능하다. (중복 순위 다음 순서 건너뜀 - 1,2,2,4)
  • - DENSE_RANK 함수는 RANK 함수와 같은 역할을 하지만 동일 등수가 순위에 영향이 없다.(중복순위 다음 순위 연속 - 1,2,2,3)
  • - ROW_NUMBER 함수는 특정 순위로 일련번호를 제공하는 함수로 동일순위 처리가 불가능하다. (중복순위 없이 유일값 - 1,2,3,4)
  • - 순위 함수 사용시 ORDER BY절은 필수로 입력해야 한다.

순위함수 - RANK

아래는 급여가 높은 순서대로 순위를 구하는 예제이다.

SELECT deptno, empno, sal
     , RANK() OVER(ORDER BY sal DESC) rk
  FROM emp
;

  DEPTNO      EMPNO        SAL         RK
-------- ---------- ---------- ----------
      10       7839       5000          1
      20       7788       3000          2
      20       7902       3000          2
      20       7566       2975          4
      30       7698       2850          5
      10       7782       2450          6
      30       7499       1600          7
      30       7844       1500          8
      10       7934       1300          9
      30       7521       1250         10
      30       7654       1250         10
      20       7876       1100         12
      30       7900        950         13
      20       7369        800         14

급여가 같은 경우 아래와 같이 동일 순위로 처리되는 것을 확인 할 수 있다.

  • [그림] RANK 실행결과
  • RANK 실행결과

아래는 부서별(PARTITION BY deptno)로 급여가 높은 순서대로(ORDER BY sal DESC) 순위를 구하는 예제이다.

SELECT deptno, empno, sal
     , RANK() OVER(PARTITION BY deptno
                       ORDER BY sal DESC) rk
  FROM emp
;


  DEPTNO      EMPNO        SAL         RK
-------- ---------- ---------- ----------
      10       7839       5000          1
      10       7782       2450          2
      10       7934       1300          3
      20       7788       3000          1
      20       7902       3000          1
      20       7566       2975          3
      20       7876       1100          4
      20       7369        800          5
      30       7698       2850          1
      30       7499       1600          2
      30       7844       1500          3
      30       7654       1250          4
      30       7521       1250          4
      30       7900        950          6

노트 : 순위함수의 특징
  • - ORDER BY는 생략할 수 없다.
  • - WINDOWING 절은 사용 할 수 없다.

순위함수 비교

아래 예제를 통해서 RANK, DENSE_RANK, ROW_NUMBER 함수의 차이점을 이해하자

SELECT deptno, empno, sal
     , RANK()       OVER(ORDER BY sal DESC) rk
     , DENSE_RANK() OVER(ORDER BY sal DESC) dr
     , ROW_NUMBER() OVER(ORDER BY sal DESC) rn
  FROM emp
;

 DEPTNO      EMPNO        SAL         RK         DR         RN
------- ---------- ---------- ---------- ---------- ----------
     10       7839       5000          1          1          1
     20       7788       3000          2          2          2
     20       7902       3000          2          2          3
     20       7566       2975          4          3          4
     30       7698       2850          5          4          5
     10       7782       2450          6          5          6
     30       7499       1600          7          6          7
     30       7844       1500          8          7          8
     10       7934       1300          9          8          9
     30       7521       1250         10          9         10
     30       7654       1250         10          9         11
     20       7876       1100         12         10         12
     30       7900        950         13         11         13
     20       7369        800         14         12         14

  • [그림] 순위함수 비교 실행결과
  • 순위함수 비교 실행 결과

RANK : 중복 순위 다음 순서 건너뜀.(1,2,2,4)

DENSE_RANK : 중복순위 다음 순위 연속.(1,2,2,3)

ROW_NUMBER : 중복순위 없이 유일값. (1,2,3,4)

순위함수 - NTILE (분류)

NTILE 함수는 쿼리의 결과를 n개의 그룹으로 분류하는 기능을 제공한다. 아래 예제에서 GRP2는 두 개의 그룹으로, GRP3는 세 개의 그룹으로, GRP5는 다섯개의 그룹으로 분류하는 것을 알 수 있다.

SELECT empno
     , NTILE(2) OVER(ORDER BY empno) grp2
     , NTILE(3) OVER(ORDER BY empno) grp3
     , NTILE(5) OVER(ORDER BY empno) grp5
  FROM emp
;


   EMPNO       GRP2       GRP3       GRP5
-------- ---------- ---------- ----------
    7369          1          1          1
    7499          1          1          1
    7521          1          1          1
    7566          1          1          2
    7654          1          1          2
    7698          1          2          2
    7782          1          2          3
    7788          2          2          3
    7839          2          2          3
    7844          2          2          4
    7876          2          3          4
    7900          2          3          4
    7902          2          3          5
    7934          2          3          5

NTILE : 지정한 숫자만큼의 그룹으로 분류

- 강좌 URL : http://www.gurubee.net/lecture/2672

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 아발란체 [2014.01.15 16:07:12]
SQL 전문가 가이드 단골 문제 유형 ~ ! ㅋ

by 도라지요 [2014.02.17 17:41:01]

PARTITION BY 는 group by 같은 느낌인가요? 으음

by 키티마징가 [2014.05.27 14:03:45]

감사합니다~


by 박민철 [2014.08.28 16:26:08]

비율로 분류할때 사용하면 되겠네요~ 


by 오라클 [2015.06.04 18:25:13]

책에서 나오는 올림픽랭킹이란 게 셋 중 뭘 말하는 건가요?

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입