Analytical
Functions In Oracle
Analytical Functions :
1.
ROW_NUMBER()
2.
DENSE_RANK()
3.
RANK()
1. ROW_NUMBER():
ROW_NUMBER assigns a unique number to each row of same
window in the ordered sequence of rows specified by order_by_clause.
Example :
select
deptno, ename, sal,
row_number() over
(order by sal desc) "row_number"
from
emp;
|
DEPTNO |
ENAME |
SAL |
ROW_NUMBER |
|
10 |
KING |
5000 |
1 |
|
20 |
SCOTT |
3000 |
2 |
|
20 |
FORD |
3000 |
3 |
|
20 |
JONES |
2975 |
4 |
|
30 |
BLAKE |
2850 |
5 |
|
10 |
CLARK |
2450 |
6 |
|
30 |
ALLEN |
1600 |
7 |
|
30 |
TURNER |
1500 |
8 |
|
10 |
MILLER |
1300 |
9 |
|
30 |
MARTIN |
1250 |
10 |
|
30 |
WARD |
1250 |
11 |
|
20 |
ADAMS |
1100 |
12 |
|
30 |
JAMES |
950 |
13 |
|
20 |
SMITH |
800 |
14 |
2. RANK()
RANK is almost same as ROW_NUMBER but
rows with equal values, with in same window, for on which order by clause is
specified receive the same rank but next row receives RANK as per it
ROW_NUMBER.
Example:
select
deptno, ename, sal,
rank() over
(partition By DEPTNO order by sal desc)
"RANK"
from
emp
order by deptno asc;
|
DEPTNO |
ENAME |
SAL |
RANK |
|
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 |
3. DENSE_RANK()
DENSE_RANK is almost same as the RANK, but
it does not leaves gap between rows if one or more values are same. Like in
following example TURNER next to WARD in same group receives DENSE_RANK 3.
Example:
select
deptno,
ename,
sal,
dense_rank() over (partition by
deptno order by sal desc) DENSE_RANK
from
emp;
|
DEPTNO |
ENAME |
SAL |
DENSE_RANK |
|
10 |
KING |
5000 |
1 |
|
10 |
CLARK |
2450 |
2 |
|
10 |
MILLER |
1300 |
3 |
|
20 |
SCOTT |
3000 |
1 |
|
20 |
FORD |
3000 |
1 |
|
20 |
JONES |
2975 |
2 |
|
20 |
ADAMS |
1100 |
3 |
|
20 |
SMITH |
800 |
4 |
|
30 |
BLAKE |
2850 |
1 |
|
30 |
ALLEN |
1600 |
2 |
|
30 |
TURNER |
1500 |
3 |
|
30 |
WARD |
1250 |
4 |
|
30 |
MARTIN |
1250 |
4 |
|
30 |
JAMES |
950 |
5 |
Combined Query in which all above functions used:
SELECT
empno,
ename,
DEPTNO,
sal,
ROW_NUMBER()
OVER(PARTITION BY DEPTNO ORDER BY sal DESC) ROW_NUMBER,
RANK()
OVER(PARTITION BY DEPTNO ORDER BY sal DESC) rank,
DENSE_RANK()
OVER(PARTITION BY DEPTNO ORDER BY sal DESC) Dense_rank
FROM
emp;
|
EMPNO |
ENAME |
DEPTNO |
SAL |
ROW_NUMBER |
DENSE_RANK |
RANK |
|
7839 |
KING |
10 |
5000 |
1 |
1 |
1 |
|
7782 |
CLARK |
10 |
2450 |
2 |
2 |
2 |
|
7934 |
MILLER |
10 |
1300 |
3 |
3 |
3 |
|
7788 |
SCOTT |
20 |
3000 |
1 |
1 |
1 |
|
7902 |
FORD |
20 |
3000 |
2 |
1 |
1 |
|
7566 |
JONES |
20 |
2975 |
3 |
3 |
2 |
|
7876 |
ADAMS |
20 |
1100 |
4 |
4 |
3 |
|
7369 |
SMITH |
20 |
800 |
5 |
5 |
4 |
|
7698 |
BLAKE |
30 |
2850 |
1 |
1 |
1 |
|
7499 |
ALLEN |
30 |
1600 |
2 |
2 |
2 |
|
7844 |
TURNER |
30 |
1500 |
3 |
3 |
3 |
|
7521 |
WARD |
30 |
1250 |
4 |
4 |
4 |
|
7654 |
MARTIN |
30 |
1250 |
5 |
4 |
4 |
|
7900 |
JAMES |
30 |
950 |
6 |
6 |
5 |
