Oracle PERCENTILE_DISC Function

Sometimes a percentile must be an actual value from the data: a real fare that customers paid, a real response time that occurred, a salary someone earns. PERCENTILE_DISC returns the first value whose cumulative distribution reaches the percentile, so the answer always exists in the group, unlike the interpolated result of PERCENTILE_CONT.

Code for This Guide

The main examples are in the examples/aggregate-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

Syntax:

percentile_disc(p) within group (order by expr [asc | desc])

p is a fraction from 0 to 1. The values are ordered by WITHIN GROUP, and PERCENTILE_DISC returns the first one whose cumulative distribution is at least p.

Discrete Percentiles of Bookings

Example:

select percentile_cont(0.5) within group (order by total_amount) as median_cont,
       percentile_disc(0.5) within group (order by total_amount) as median_disc,
       percentile_cont(0.9) within group (order by total_amount) as p90,
       percentile_disc(0.9) within group (order by total_amount desc) as p10_desc
from   bookings
where  status <> 'CANCELLED';

Output:

   MEDIAN_CONT    MEDIAN_DISC        P90    P10_DESC
______________ ______________ __________ ___________
       1001.29        1001.29    3634.11      315.17

Here the discrete and continuous medians happen to be equal. The last column orders descending: the 90th percentile from the top is the 10th percentile from the bottom, 315.17.

Discrete Compared with Continuous

Example:

-- on four values, the median falls between two of them
select percentile_cont(0.5) within group (order by v) as cont_median,
       percentile_disc(0.5) within group (order by v) as disc_median,
       percentile_cont(0.25) within group (order by v) as cont_q1,
       percentile_disc(0.25) within group (order by v) as disc_q1
from   (values (10), (20), (30), (40)) t (v);

Output:

   CONT_MEDIAN    DISC_MEDIAN    CONT_Q1    DISC_Q1
______________ ______________ __________ __________
            25             20       17.5         10

For 10, 20, 30, and 40, PERCENTILE_DISC(0.5) returns 20, the first value at or above the 50 percent mark, where PERCENTILE_CONT interpolates 25.

Per Group

Example:

-- the 90th percentile of fares in each cabin
select cabin, percentile_cont(0.9) within group (order by fare) as p90_fare,
       percentile_disc(0.9) within group (order by fare) as p90_existing_fare
from   tickets
group  by cabin;

Output:

CABIN          P90_FARE    P90_EXISTING_FARE
___________ ___________ ____________________
BUSINESS       4671.295                 4671
ECONOMY        1225.004              1230.62

The discrete 90th percentile of economy fares is 1,230.62, a fare actually paid, slightly above the interpolated 1,225.004.

Things to Know

• PERCENTILE_DISC works on any sortable type, including dates and text, since it never interpolates.
• For large groups, the two functions give nearly the same numbers; the difference matters most for small groups.
• It also works as an analytic function with OVER (PARTITION BY ...).

Related Guides

• Oracle PERCENTILE_CONT Function
• Oracle MEDIAN Function

Conclusion

PERCENTILE_DISC returns an existing value at a percentile of a group: the first whose cumulative distribution reaches it. Use it when the answer must be a real data point, and PERCENTILE_CONT when an interpolated value is acceptable.