Oracle Analytic functions compute an aggregate value based on a group of rows called window which determines the range of rows used to perform the calculations for the current row. Following are most used Analytic functions.
– RANK, DENSE_RANK and ROW_NUMBER
– LAG and LEAD
– FIRST_VALUE and LAST_VALUE
I would be discussing about RANK, DENSE_RANK and ROW_NUMBER analytics functions.They are quite similar in nature and we need to use on the basis of the requirement.I would also be explaining the difference between them
Here is the general syntax
analytic_function([ arguments ]) OVER ([ query_partition_clause ] [ order_by_clause ])
ROW_NUMBER function in Oracle
ROW_NUMBER assigns a unique number to each row of same window in the ordered sequence of rows specified by order_by_clause.
Lets first create sample data
CREATE TABLE "DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
)
CREATE TABLE "EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "DEPT" ("DEPTNO") ENABLE
);
SQL> desc emp
Name Null? Type
---- ---- -----
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> desc dept
Name Null? Type
---- ----- ----
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
insert into DEPT values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'RESEARCH', 'DELHI');
insert into dept values(40, 'RESEARCH', 'MUMBAI');
commit;
insert into emp values( 7839, 'Clark', 'MANAGER', 7839, to_date('9-6-2008','dd-mm-yyyy'), 28573, null, 10 );
insert into emp values( 7782, 'Clara', 'MANAGER', 7839, to_date('9-6-2008','dd-mm-yyyy'), 0, null, 10 );
insert into emp values( 7934, 'Blake', 'MANAGER', 7839, to_date('1-5-2007','dd-mm-yyyy'), 0, null, 10 );
insert into emp values( 7788, 'Scott', 'ANALYST', 7788, to_date('9-6-2012','dd-mm-yyyy'), 30000, null, 20 );
insert into emp values( 7902, 'Bill', 'ANALYST', 7832, to_date('9-6-2012','dd-mm-yyyy'), 30000, null, 20 );
insert into emp values( 7876, 'TPM', 'ANALYST', 7566, to_date('9-6-2017','dd-mm-yyyy'), 11000, null, 20 );
insert into emp values( 7369, 'TPM1', 'ANALYST', 7566, to_date('9-6-2017','dd-mm-yyyy'), 8000, null, 20 );
insert into emp values( 7698, 'A1', 'ANALYST', 7788, to_date('9-6-2017','dd-mm-yyyy'), 28500, null, 30 );
insert into emp values( 7499, 'A2', 'ANALYST', 7698, to_date('9-7-2017','dd-mm-yyyy'), 16000, null, 30 );
insert into emp values( 7844, 'A3', 'ANALYST', 7698, to_date('9-7-2017','dd-mm-yyyy'), 15000, null, 30 );
insert into emp values( 7654, 'A4', 'ANALYST', 7698, to_date('9-7-2017','dd-mm-yyyy'), 12500, null, 30 );
insert into emp values( 7521, 'A5', 'ANALYST', 7698, to_date('9-7-2017','dd-mm-yyyy'), 12500, null, 30 );
insert into emp values( 7900, 'A6', 'ANALYST', 77698, to_date('9-7-2017','dd-mm-yyyy'), 0, null, 30 );
commit;
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select deptno ,count(*) from emp group by deptno;
DEPTNO COUNT(*)
---------- ----------
30 6
20 4
10 3
SQL> select
deptno, ename, sal,
row_number() over (partition by deptno order by sal) "row_number"
from
emp;
DEPTNO ENAME SAL row_number
---------- ---------- ---------- ----------
10 CLARK 0 1
10 MILLER 0 2
10 allen 28573 3
20 SMITH 8000 1
20 ADAMS 11000 2
20 SCOTT 30000 3
20 FORD 30000 4
30 JAMES 9500 1
30 MARTIN 12500 2
30 WARD 12500 3
30 TURNER 15000 4
30 ALLEN 16000 5
30 BLAKE 28500 6
13 rows selected.
RANK Function in Oracle
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.
SQL> select deptno, ename, sal, rank() over (partition by deptno order by sal) "RANK" from emp; DEPTNO ENAME SAL RANK ---------- ---------- ---------- ---------- 10 CLARK 0 1 10 MILLER 0 2 10 allen 28573 3 20 SMITH 8000 1 20 ADAMS 11000 2 20 SCOTT 30000 3 20 FORD 30000 3 30 JAMES 9500 1 30 MARTIN 12500 2 30 WARD 12500 2 30 TURNER 15000 4 30 ALLEN 16000 5 30 BLAKE 28500 6 13 rows selected.
Dense_rank Function in Oracle
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.
SQL> select deptno, ename, sal, dense_rank() over (partition by deptno order by sal) "DENSE_RANK" from emp; DEPTNO ENAME SAL DENSE_RANK ---------- ---------- ---------- ---------- 10 CLARK 0 1 10 MILLER 0 2 10 allen 28573 3 20 SMITH 8000 1 20 ADAMS 11000 2 20 SCOTT 30000 3 20 FORD 30000 3 30 JAMES 9500 1 30 MARTIN 12500 2 30 WARD 12500 2 30 TURNER 15000 3 30 ALLEN 16000 4 30 BLAKE 28500 5 13 rows selected.
We can put all the three in the single query also
select
deptno, ename, sal,
row_number() over (partition by deptno order by sal) "row_number",
rank() over (partition by deptno order by sal) "rank",
dense_rank() over (partition by deptno order by sal) "dense_rank"
from
emp;
DEPTNO ENAME SAL row_number rank dense_rank
---------- ---------- ---------- ---------- ---------- ----------
10 CLARK 0 1 1 1
10 MILLER 0 2 1 1
10 allen 28573 3 3 2
20 SMITH 8000 1 1 1
20 ADAMS 11000 2 2 2
20 SCOTT 30000 3 3 3
20 FORD 30000 4 3 3
30 JAMES 9500 1 1 1
30 MARTIN 12500 2 2 2
30 WARD 12500 3 2 2
30 TURNER 15000 4 4 3
30 ALLEN 16000 5 5 4
30 BLAKE 28500 6 6 5
13 rows selected.
We can use Row_number and RANK function in deleting the duplicating rows
delete from t
where rowid IN ( select rid
from (select rowid rid,
row_number() over (partition by
column_name
order by rowid) rn
from t)
where rn <> 1);
These function are very useful for for top-N and bottom-N queries.
The below SQL can be used to find the top salary in each dept
SQL> select * (select deptno, ename, sal, row_number() over (partition by deptno order by sal) "row_number" from emp ) where row_number=1;
Hope you like explanation on RANK, DENSE_RANK and ROW_NUMBER like Oracle Analytic functions and how we can used in the query to analyze the data. We have to very careful while using these functions in the queries else the result would be different.
Related Articles
LEAD function in Oracle
Analytic functions in oracle
Oracle Interview questions
Oracle Set Operators
Oracle Sql tutorial
Dense rank oracle documentation