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