Kde…

Analytic functions:
All my findings regarding analytic functions will go in this post. In other words I will keep on adding my finding to this post instead of creating new one all the time. I will also make sure that everytime when I update this post I bring up to the top of the list so that people notice the change.

Below is a small but sweet example that shows the difference between using three different functions.


DENSE_RANK()
RANK()
ROW_NUMBER()



create table t1 (
a number,
b varchar2(10)
);

insert into t1 values (5, ‘ABC’);
insert into t1 values (6, ‘ABC’);
insert into t1 values (5, ‘ABC’);
insert into t1 values (3, ‘ABC’);
insert into t1 values (5, ‘ABC’);
insert into t1 values (3, ‘ABC’);
insert into t1 values (6, ‘ABC’);

insert into t1 values (4, ‘XYZ’);
insert into t1 values (8, ‘XYZ’);
insert into t1 values (4, ‘XYZ’);
insert into t1 values (8, ‘XYZ’);
insert into t1 values (4, ‘XYZ’);

commit;

SQL> select * from t1;

A B
———- ———-
5 ABC
6 ABC
5 ABC
3 ABC
5 ABC
3 ABC
6 ABC
4 XYZ
8 XYZ
4 XYZ
8 XYZ

A B
———- ———-
4 XYZ

12 rows selected.

SQL> select dense_rank() over (partition by b order by a) “denserank”,
rank() over (partition by b order by a) “rank”,
row_number() over (partition by b order by a) “rownumber”,
a,b
from t1;

denserank rank rownumber A B
———- ———- ———- ———- ———-
1 1 1 3 ABC
1 1 2 3 ABC
2 3 3 5 ABC
2 3 4 5 ABC
2 3 5 5 ABC
3 6 6 6 ABC
3 6 7 6 ABC
1 1 1 4 XYZ
1 1 2 4 XYZ
1 1 3 4 XYZ
2 4 4 8 XYZ

denserank rank rownumber A B
———- ———- ———- ———- ———-
2 4 5 8 XYZ

12 rows selected.

SQL>
Hope this helps.
Kirtan

##### UPDATES BELOW #####

The example below uses MAX() function to show highest salary in each department right next to each employee record. It also shows what an employee’s salary is compared to the highest salary in the department.
Here the key is (PARTITION BY DEPTNO) part of the analytic function. That’s what tells oracle to break data up based on the partition details.

SQL> SELECT
ENAME,
SAL,
DEPTNO,
MAX(SAL) OVER (PARTITION BY DEPTNO) MGR_SAL_BY_DEPT,
sal/MAX(SAL) OVER (PARTITION BY DEPTNO) *100 EMP_percent_SAL
FROM EMP
ORDER BY DEPTNO;

ENAME SAL DEPTNO MGR_SAL_BY_DEPT EMP_PERCENT_SAL
———- ———- ———- ————— —————
CLARK 2450 10 5000 49
KING 5000 10 5000 100
MILLER 1300 10 5000 26
JONES 2975 20 3000 99.1666667
FORD 3000 20 3000 100
ADAMS 1100 20 3000 36.6666667
SMITH 800 20 3000 26.6666667
SCOTT 3000 20 3000 100
WARD 1250 30 2850 43.8596491
TURNER 1500 30 2850 52.6315789
ALLEN 1600 30 2850 56.1403509
JAMES 950 30 2850 33.3333333
BLAKE 2850 30 2850 100
MARTIN 1250 30 2850 43.8596491

14 rows selected.

SQL>

Now by adding one more MAX() function to the query we can have max salary across departments and a comparision of what an empolyee is making compared to the maximum salary across all departments.

SQL> SELECT
ENAME, SAL,DEPTNO,
MAX(SAL) OVER (PARTITION BY DEPTNO) max_dept_sal
,sal/MAX(SAL) OVER (PARTITION BY DEPTNO) *100 dept_percent
,MAX(SAL) OVER () max_comp_sal
,sal/MAX(SAL) OVER () *100 comp_percent
FROM EMP
ORDER BY DEPTNO;

ENAME SAL DEPTNO MAX_DEPT_SAL DEPT_PERCENT MAX_COMP_SAL COMP_PERCENT
———- ———- ———- ———— ———— ———— ————
CLARK 2450 10 5000 49 5000 49
KING 5000 10 5000 100 5000 100
MILLER 1300 10 5000 26 5000 26
JONES 2975 20 3000 99.1666667 5000 59.5
FORD 3000 20 3000 100 5000 60
ADAMS 1100 20 3000 36.6666667 5000 22
SMITH 800 20 3000 26.6666667 5000 16
SCOTT 3000 20 3000 100 5000 60
WARD 1250 30 2850 43.8596491 5000 25
TURNER 1500 30 2850 52.6315789 5000 30
ALLEN 1600 30 2850 56.1403509 5000 32

ENAME SAL DEPTNO MAX_DEPT_SAL DEPT_PERCENT MAX_COMP_SAL COMP_PERCENT
———- ———- ———- ———— ———— ———— ————
JAMES 950 30 2850 33.3333333 5000 19
BLAKE 2850 30 2850 100 5000 57
MARTIN 1250 30 2850 43.8596491 5000 25

14 rows selected.

SQL>

##############UPDATES ###################

example showing the use of LAG and LEAD functions. Here, LAG is used for reading rows before the current row and LEAD is used for reading rows after the current row. Example below shows the difference between salaries of row and its previous row and row and its next row. The key here would be the ORDER BY clause in the OVER() section of the analytic function.