CUME_DIST function

The CUME_DIST function is an OLAP ranking function that calculates a cumulative distribution as a percentile ranking for each row. The rank is expressed as a decimal fraction that ranges from 0 to 1.

Syntax


CUME_DIST function

                                                         (1)   
|--CUME_DIST -( ) -| OVER clause for Ranking functions |--------|

Notes:

See OVER clause for OLAP window expressions

Usage

The CUME_DIST function calculates the number of rows that are ranked lower than or equal to the current row, including the current row, which is divided by the total number of rows in the partition. Values closer to 1 represent higher rankings and values closer to 0 represent lower rankings.

This function takes no argument, but the empty parentheses must be specified. If the optional window PARTITION clause is also specified, the rankings are calculated within the subset of rows that each partition defines. If there is a single row in the partition, its CUME_DIST value is 1.

Example: CUME_DIST function

The following query shows the cumulative distribution of the amount of sales per sales person.

SELECT emp_num, sales,
  CUME_DIST() OVER (ORDER BY sales) AS cume_dist
FROM sales;

    emp_num      sales     cume_dist

        101      2,000    0.166666667
        102      2,400    0.500000000
        103      2,400    0.500000000
        104      2,500    0.833333333
        105      2,500    0.833333333
        106      2,650    1.000000000