오라클에서는 RANK Function을 사용해서 순위를 간편하게 부여할 수 있습니다.
RANK Function는 oracle 8i(8.1.6) 부터 가능합니다.
8.1.6 이전 버전에서는 사용 할 수 없습니다. ORA-923 error 가 발생 합니다.
plsql 내에서는 oracle 9i 부터 가능합니다. 8.1.6에서는 ORA-900 error가 발생 합니다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- scott유저로 접속을 합니다. ]$ SQLPLUS scott/tiger -- RANK() 함수를 사용하여 급여 순으로 순위를 부여한 예제 입니다. -- RK의 출력값을 보면 급여가 같으면 같은 순위로 부여가 됩니다. SQL> SELECT empno, ename, sal, RANK() OVER (ORDER BY sal DESC ) as rk FROM emp; EMPNO ENAME SAL RK --------- ---------- ---------- ---------- 7839 KING 5000 1 7788 SCOTT 3000 2 7902 FORD 3000 2 7566 JONES 2975 4 7698 BLAKE 2850 5 7782 CLARK 2450 6 7499 ALLEN 1600 7 7844 TURNER 1500 8 7934 MILLER 1300 9 7521 WARD 1250 10 7654 MARTIN 1250 10 7876 ADAMS 1100 12 7900 JAMES 950 13 7369 SMITH 800 14 |
그룹별로 순위를 부여 하는 법
위 예제는 deptno를 파티션으로 나누어서 부서별로 순위를 부여 합니다.
특정한 그룹별로 순위를 부여하고 싶을때 사용 하면 편합니다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL> SELECT deptno, ename, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC ) as rk FROM 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 4 30 JAMES 950 6 |
DENSE_RANK() 함수
DENSE_RANK() – 중복 RANK의 수와 무관하게 numbering을 합니다.
1등, 2등, 2등 이렇게 2등이 중복되었는데 4등이 아니라 3등이 부여 됩니다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL>SELECT empno, ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC ) as rk FROM emp; EMPNO ENAME SAL RK --------- ---------- ---------- --------- 7839 KING 5000 1 7788 SCOTT 3000 2 7902 FORD 3000 2 7566 JONES 2975 3 7698 BLAKE 2850 4 7782 CLARK 2450 5 7499 ALLEN 1600 6 7844 TURNER 1500 7 7934 MILLER 1300 8 7521 WARD 1250 9 7654 MARTIN 1250 9 7876 ADAMS 1100 10 7900 JAMES 950 11 7369 SMITH 800 12 |
OVER analytic_clause
해당 함수가 쿼리 결과 집합에 대해 적용되라는 지시어로써 FROM, WHERE, GROUP BY와 HAVING구 이후에 계산되어 진다.
SELECT 구 또는 ORDER BY 구에 Analytic Function을 사용할 수 있다.
– PARTITION BY 구
하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고 하나 이상의 컬럼 또는 표현식에 의한 그룹으로 쿼리의 결과를 파티션한다. 이 구가 생략되면 단일 그룹처럼 쿼리 결과 집합이 처리된다.
– ORDER BY 구
하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고 하나 이상의 컬럼 또는 표현식을 기준으로 파티션 내의 데이터를 정렬한다. 표현식은 컬럼의 별칭 또는 위치를 나타내는 숫자를 사용할 수 없다.
부서별 직업별 급여 합계와, 직업별 급여 합계가 가장 큰 금액
|
1 2 3 4 |
SELECT deptno, job, SUM(SAL) sum_sal, MAX(SUM(sal)) OVER (PARTITION BY deptno) FROM emp GROUP BY deptno, job |