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 3678The 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.99The 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
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.
