Oracle APPROX_MEDIAN Function

MEDIAN and PERCENTILE_CONT sort all the values of a group, which is costly on very large data. APPROX_MEDIAN and APPROX_PERCENTILE estimate the median and any percentile from a compact summary instead, typically within a small error, and can also tell you how accurate the estimate is.

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:

approx_median(expr [deterministic] [, {'ERROR_RATE' | 'CONFIDENCE'}])
approx_percentile(p [deterministic] [, {'ERROR_RATE' | 'CONFIDENCE'}])
  within group (order by expr [asc | desc])

DETERMINISTIC makes the result the same in every run, at some cost in speed. With 'ERROR_RATE' or 'CONFIDENCE' as the second argument, the function returns the accuracy of its estimate instead of the estimate.

Estimates Compared with Exact Values

Example:

select approx_median(total_amount)                                  as approx_median,
       median(total_amount)                                         as exact_median,
       approx_percentile(0.9) within group (order by total_amount)   as approx_p90,
       approx_percentile(0.9 deterministic) within group (order by total_amount) as det_p90
from   bookings;

Output:

   APPROX_MEDIAN    EXACT_MEDIAN    APPROX_P90    DET_P90
________________ _______________ _____________ __________
         1020.23        1020.345       3690.36       3678

The approximate median, 1,020.23, is within a fraction of a percent of the exact 1,020.345. The deterministic 90th percentile differs slightly from the non-deterministic one, since they use different methods.

How Accurate Is It

Example:

select approx_median(total_amount, 'ERROR_RATE')  as median_error_rate,
       approx_median(total_amount, 'CONFIDENCE')  as median_confidence
from   bookings;

Output:

   MEDIAN_ERROR_RATE    MEDIAN_CONFIDENCE
____________________ ____________________
                0.02                 0.99

The estimate has an error rate of 0.02, that is 2 percent, with a confidence of 0.99.

Things to Know

• Setting APPROX_FOR_PERCENTILE to ALL makes Oracle use the approximate algorithms for MEDIAN and the percentile functions automatically.
• APPROX_PERCENTILE_DETAIL, APPROX_PERCENTILE_AGG, and TO_APPROX_PERCENTILE store and merge percentile sketches, like the distinct-count detail functions.
• Use the exact functions when results must be reproducible to the last digit and data volumes allow it.

Related Guides

• Oracle MEDIAN Function
• Oracle PERCENTILE_CONT Function
• Oracle APPROX_COUNT_DISTINCT Function

Conclusion

APPROX_MEDIAN and APPROX_PERCENTILE estimate medians and percentiles quickly on large data, with DETERMINISTIC for repeatable results and ERROR_RATE or CONFIDENCE to measure their accuracy.