Oracle CUME_DIST Function

"This fare is higher than 97 percent of business fares" is a statement of cumulative distribution. CUME_DIST returns, for each row, the fraction of rows in its partition that come before it or tie with it in the order, a number between 0 and 1 that is never 0. It is the natural way to find the top or bottom percent of a group.

Code for This Guide

The main examples are in the examples/analytic-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

cume_dist() over ([partition by expr [, ...]] order by expr [, ...])

CUME_DIST = (number of rows with values up to and including the current one) / (number of rows in the partition).

Cumulative Distribution of Salaries

Example:

select last_name, salary,
       round(percent_rank() over (order by salary), 3) as percent_rank,
       round(cume_dist()    over (order by salary), 3) as cume_dist
from   employees
where  department_id = 40
order  by salary;

Output:

LAST_NAME       SALARY    PERCENT_RANK    CUME_DIST
____________ _________ _______________ ____________
Tanaka            7600               0          0.1
Gupta             7800           0.111          0.2
Hussain           8000           0.222          0.3
Johnson           8200           0.333          0.4
Martínez         13000           0.444          0.5
Patel            13800           0.556          0.6
Khan             14200           0.667          0.7
Thompson         14500           0.778          0.8
Kapoor           21000           0.889          0.9
Evans            34000               1            1

10 rows selected.

With ten employees, CUME_DIST goes 0.1, 0.2, up to 1: the lowest earner is at or below 10 percent of the department, the highest at or below all of it. PERCENT_RANK, by contrast, starts at 0.

The Top Few Percent

Filtering on CUME_DIST keeps the rows above a threshold, here business fares above 97 percent of the others, using QUALIFY.

Example:

-- the top 3 percent of business fares
select ticket_id, fare, round(cume_dist() over (order by fare), 3) as cume_dist
from   tickets
where  cabin = 'BUSINESS'
qualify cume_dist() over (order by fare) > 0.97
order  by fare;

Output:

   TICKET_ID       FARE    CUME_DIST
____________ __________ ____________
         919    5403.01         0.97
         489    5467.81        0.974
        1058    5477.89        0.978
         472    5502.52        0.981
         656     5525.9        0.985
         558    5740.93        0.989
        1003    6070.58        0.993
         716    6276.59        0.996
        1046    6334.82            1

9 rows selected.

CUME_DIST Compared with PERCENT_RANK

Related Guides

• Oracle PERCENT_RANK Function
• Oracle PERCENTILE_DISC Function

Conclusion

CUME_DIST returns the fraction of rows at or before each row in its partition, from just above 0 up to 1. Use it to describe where values fall in a distribution and, with QUALIFY or an inline view, to select the top or bottom percent of a group.