Oracle PERCENTILE_CONT Function

Service levels and price bands are often stated as percentiles: 90 percent of bookings cost less than this amount, the median fare is that. PERCENTILE_CONT returns the value at any percentile of a group, interpolating between the two nearest values, so the answer reflects the whole distribution rather than one data point.

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_cont(p) within group (order by expr [asc | desc])

p is a fraction from 0 to 1: 0.5 is the median, 0.9 the 90th percentile. WITHIN GROUP orders the values. The result can be a value that does not occur in the data, because it is interpolated.

Median and 90th Percentile 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

The median booking is 1,001.29, and 90 percent of bookings are below 3,634.11. The last column uses PERCENTILE_DISC in descending order, covered in its own guide.

Interpolation on a Small Set

With four values, the median lies between the second and third, and PERCENTILE_CONT returns the point between them.

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

The continuous median of 10, 20, 30, and 40 is 25, and the first quartile 17.5. PERCENTILE_DISC returns existing values instead: 20 and 10.

Percentiles 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 90th percentile of business fares is about 4,671 and of economy fares 1,225.

Things to Know

• PERCENTILE_CONT(0.5) is the same as MEDIAN.
• It works as an analytic function with OVER (PARTITION BY ...), returning the percentile on every row.
• For huge tables, APPROX_PERCENTILE estimates percentiles much faster.

Related Guides

• Oracle MEDIAN Function

Conclusion

PERCENTILE_CONT returns the value at a percentile of a group, interpolating between the nearest values. Use it for medians, quartiles, and service-level percentiles, and PERCENTILE_DISC when the answer must be a value that exists.