1.2. 순위함수

순위함수

• - 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 실행결과
• 
• 
  

아래는 부서별(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 : 지정한 숫자만큼의 그룹으로 분류

출처 : http://www.gurubee.net/lecture/2672