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. |
|
|