rank()

RANK와 DENSE_RANK는 어떤 한 GROUP에서 항목들의 순위를 매길 수 있는 기능을 제공한다.

SYNTAX :

RANK() OVER (
[PARTITION BY [, ...]]
ORDER BY [collate clause] [ASC|DESC]
[NULLS FIRST|NULLS LAST] [, ...] )
DENSE_RANK() OVER (
[PARTITION BY [, ...]]
ORDER BY [collate clause] [ASC|DESC]
[NULLS FIRST|NULLS LAST] [, ...] )

1.RANK의 특징

• 정렬 순서는 ascending이 default이며 descending으로 지정이 가능하다.
• PARTITION BY는 선택항목이며 QUERY의 RESULT SET을 RANK FUNCTION을 적용할 GROUP으로 나눈다.다시 말해 RANK는 GROUP이 바뀔 때 마다 새로운 SET에 대하여 연산을 수행하게 된다.
• PARTITION BY 절이 생략되면 RANK는 QUERY의 전체 RESULT SET에 적용된다.
• ORDER BY는 순위가 매겨지는 기준을 정의하는 절이다.
• NULLS FIRST나 NULL LAST는 ORDER SEQUENCE에서 NULL의 위치를 나타낸다.

RANK와 DENSE_RANK의 차이는 순위가 같을 때 RANK는 ranking sequence에 gap을 허용하지만 DENSE_RANK는 gap을 허용하지 않는다.

2.RANKING ORDER
다음은 [ASC | DESC]이 어떻게 ranking order를 변화시키는지를 보여주는 사례이다.
SELECT deptno,empno, sal,
RANK() OVER (ORDER BY sal) AS asc_rank,
RANK() OVER (ORDER BY sal desc nulls last) as desc_rank
FROM emp;

DEPTNO	EMPNO	SAL	ASC_RANK	DESC_RANK
10	7839	5000	14	1
20	7788	3000	12	2
20	7902	3000	12	2
20	7566	2975	11	4
30	7698	2850	10	5
10	7782	2450	9	6
30	7499	1600	8	7
30	7844	1500	7	8
10	7934	1300	6	9
30	7521	1250	4	10
30	7654	1250	4	10
20	7876	1100	3	12
30	7900	950	2	13
20	7369	800	1	14

 
3.Ranking on multiple Expressions
Result Set에서 Ranking을 매기고자 하는 value가 동률을 이룰 때 이것을 해결하기 위해 다른 expression을 사용할 수 있다.
SELECT deptno,empno, sal,  
     RANK() OVER (ORDER BY sal desc,empno nulls last) as desc_rank  
FROM emp
WHERE DEPTNO = 30;

DEPTNO	EMPNO	SAL	DESC_RANK
30	7698	2850	1
30	7499	1600	2
30	7844	1500	3
30	7521	1250	4
30	7654	1250	5
30	7900	950	6

 

4.Group Ranking
PARTITION BY 옵션에 Group expression을 지정하면 dataset 은 group으로 나누어져 Group 이내에서 Ranking Function이 수행된다.
SELECT deptno,empno, sal,  
      RANK() OVER (PARTITION BY deptno
                   ORDER BY sal desc,empno nulls last) as desc_rank  
 FROM emp;

DEPTNO	EMPNO	SAL	DESC_RANK
10	7839	5000	1
10	7782	2450	2
10	7934	1300	3
20	7788	3000	1
20	7902	3000	2
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	7521	1250	4
30	7654	1250	5
30	7900	950	6

 
5.CUME_DIST Function
일련의 값에 대하여 지정된 값이 가지는 위치를 계산하는 FUNCTION으로 백분율로 표시되며 값의 범위는 0보다 크고 1보다 같거나 작은 값들로 표시된다.

CUME_DIST(x) = 

number of values (different from, or equal to, x) in S coming before x in the specified order/ N
여기에서 N : 집합 S의 크기
Its syntax is:
CUME_DIST() OVER
([PARTITION BY [, ...]]
ORDER BY [collate clause] [ASC|DESC]
[NULLS FIRST | NULLS LAST] [, ...])

SELECT deptno,empno, sal,
CUME_DIST() OVER (PARTITION BY deptno
ORDER BY sal nulls last) as desc_rank
FROM emp;

 

DEPTNO	EMPNO	SAL	RANK
10	7934	1300	0.33
10	7782	2450	0.66
10	7839	5000	1
20	7369	800	0.2
20	7876	1100	0.4
20	7566	2975	0.6
20	7788	3000	1
20	7902	3000	1
30	7900	950	0.16
30	7521	1250	0.5
30	7654	1250	0.5
30	7844	1500	0.66
30	7499	1600	0.83
30	7698	2850	1

 
6.PERCENT_RANK
CUME_DIST Function과 매우 유사하지만 row counts를 사용하지 않고 rank value를 사용한다.

PERCENT_RANK of a row is calculated as:
(rank of row in its partition - 1) / (number of rows in the partition - 1)

PERCENT_RANK returns values in the range zero to one. The first row will have a PERCENT_RANK of zero.

Its syntax is:
PERCENT_RANK() OVER
([PARTITION BY [, ...]]
ORDER BY [collate clause] [ASC|DESC]
[NULLS FIRST | NULLS LAST] [, ...])

7.ROW_NUMBER

Partition 이내에서 각각의 Row에 대해 unique 한 숫자를 부여한다.

syntax:
ROW_NUMBER() OVER
([PARTITION BY [, ...]]
ORDER BY [collate clause] [ASC|DESC]
[NULLS FIRST | NULLS LAST] [, ...])

SELECT deptno,empno, sal,
row_number() OVER (PARTITION BY deptno
ORDER BY sal nulls last) as rank
FROM emp;

DEPTNO	EMPNO	SAL	RANK
10	7934	1300	1
10	7782	2450	2
10	7839	5000	3
20	7369	800	1
20	7876	1100	2
20	7566	2975	3
20	7788	3000	4
20	7902	3000	5
30	7900	950	1
30	7521	1250	2
30	7654	1250	3
30	7844	1500	4
30	7499	1600	5
30	7698	2850	6

 
8.기타
위에서 기술한 Function 외에 RANK 분류에서 지원되는 기능은 TOP_N, BOTTOM_N, NTILE 등이 있으므로 참고하기 바란다.