순위 - RANK, DENSE_RANK

[형식]
RANK() OVER ( query_partition_clause  ORDER_BY clause )
- 중복 rank 값만큼 다음 순위는 생략

 

SQL> SELECT deptno, ename, sal, comm,
            RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) as rk
       FROM emp;

    DEPTNO ENAME             SAL         RK
---------- ---------- ---------- ----------
        10 KING             5000          1
        10 CLARK            2450          2
        10 MILLER           1300          3
       
        20                  3500          1
        20 SCOTT            3000          2
        20 FORD             3000          2
        20 JONES            2975          4
        20 ADAMS            1100          5
        20 SMITH             800          6
       
        30 BLAKE            2850          1
        30 ALLEN            1600          2
        30 TURNER           1500          3
        30 WARD             1250          4
        30 MARTIN           1250          5
       
        40 JAMES             777          1
                               9          1

 

[형식]
DENSE_RANK( ) OVER ( query_partition_clause ORDER_BY clause )
- 중복 rank 의 수와 무관하게 numbering

 

SQL> SELECT dname, ename, sal, DENSE_RANK() OVER (PARTITION BY dname ORDER BY sal) as drank
       FROM emp, dept
      WHERE emp.deptno = dept.deptno
        AND dname IN ('SALES', 'RESEARCH');

DNAME          ENAME             SAL      DRANK
-------------- ---------- ---------- ----------
RESEARCH       SMITH             800          1
RESEARCH       ADAMS            1100          2
RESEARCH       JONES            2975          3
RESEARCH       SCOTT            3000          4
RESEARCH       FORD             3000          4
RESEARCH                        3500          5

SALES          WARD             1250          1
SALES          MARTIN           1250          1
SALES          TURNER           1500          2
SALES          ALLEN            1600          3
SALES          BLAKE            2850          4

 

plsql 내에서 사용  가능 : oracle 9i 부터

 

SQL> create table
     rank_emp(deptno number(2), ename varchar2(20), sal number(5), rk number(2));

테이블이 생성되었습니다.

 

SQL> create or replace procedure window_plsql  AS
     query_str VArchar2(1000);
  
     begin

     query_str := 'insert into rank_emp
     SELECT deptno, ename, sal,
     RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) as rk
     FROM emp' ;
     Execute Immediate query_str;

     end;
  2  /

프로시저가 생성되었습니다.

 

SQL> exec window_plsql

PL/SQL 처리가 정상적으로 완료되었습니다.

 

SQL> select * from rank_emp;

    DEPTNO ENAME                       SAL         RK
---------- -------------------- ---------- ----------
        10 KING                       5000          1
        10 CLARK                      2450          2
        10 MILLER                     1300          3
        20 SCOTT                      3000          1
        20 FORD                       3000          1
        20 JONES                      2975          3
        20 ADAMS                      1100          4
        20 SMITH                       800          5
        30 BLAKE                      2850          1
        30 ALLEN                      1600          2
        30 TURNER                     1500          3
        30 WARD                       1250          4
        30 MARTIN                     1250          5
        30 JAMES                       950          6

14 개의 행이 선택되었습니다.