순위함수 RANK DENSE_RANK ROW_NUMBER 특징별 기억하면 좋고

순위함수

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

순위함수 - RANK

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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) 순위를 구하는 예제이다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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 함수의 차이점을 이해하자

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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는 다섯개의 그룹으로 분류하는 것을 알 수 있다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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 : 지정한 숫자만큼의 그룹으로 분류

태그

• RANK DENSE_RANK ROW_NUMBER NTILE 순위함수 분석함수

문서에 대하여

• - 강좌 URL : http://www.gurubee.net/lecture/2672
• - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• - 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.