Oracle Analytic Functions:

1. Here is a way to get ranking of data and cummulative distribution, totals using Oracle analytical functions.  Ranking of data using RANK function results in same rank assigned to equal valued columns, but is considered as (rank + 1) when the next column is ranked (e.g. the ranking can be 1,2,3,3,5) with the maximum rank being 5.
The same query using the DENSE_RANK function results in actual ranking of data (e.g. the above will be 1,2,3,3,4) with maximum rank being 4.
 
The CUME_DIST function computes the cummulative distribution of the data with value ranging from >= 0 to <= 1.
CUME_DIST = rank/MAX(rank) (i.e. Rownum rank/highest rank)
 
The Minimum and Maximum (MIN(), MAX()) value of data for the partition can be computed as shown using DENSE_RANK, FIRST and LAST functions with the keyword KEEP.
 
 

Oracle Analytic Functions Queries:

1. Lead/Lag Queries

2. Using Lag To Analyze Sequence Gaps

3. Annual Calendar Script

4. Annual Sales Analysis

5. Ratio_To_Report
For corrections or feedback, please contact webmaster

Oracle registered trademark of Oracle Corporation.


Last Revised On: October 13th, 2014

  83