OLAP Functions Tips
=> OLAP functions are also called as "Analytical Functions" or "Window Functions".
We can perform the following operations:
(i) Rank the records.
(ii) Get the record number.
(i) RANK & DENSE_RANK
=> Ranking will be done based on some value.
=> These function accepts only sorted set of values.
=> Rank() generate gaps,
Dense_Rank() doesn't generate gaps.
i.e Rank: { 1,2,3,3,5,6,.... } 4 is skipped.
Dense_Rank: { 1,2,3,3,4,5,6,.... } 4 is involved.
Syntax:
=> RANK() over (expr)
=> DENSE_RANK() over (expr)
Examples:
1. Display Ranks of the employees based on their Salaries ?
=> Select ename, sal, RANK() OVER( ORDER BY SAL) as "Rank" from Emp;
2. Display Ranks of the employees based on their Hiredate ?
=> Select ename, sal, RANK() OVER( ORDER BY hiredate) as "Rank" from Emp;
(ii) ROW_NUMBER
=> Returns the record number.
Syntax:
=> ROW_NUMBER() OVER (expr)
Examples:
=> Select ename, sal, ROW_NUMBER() over (ORDER BY sal DESC) as "Row Number" from Emp;