Quantile - Functions
Table of Contents
About
This page regroups all known quantile function.
Articles Related
List
Oracle
Specific Oracle function with Quantile.
Oracle has also the ntile function.
The MEDIAN function is a specific case of (PERCENTILE_CONT|DISC) where the percentile value defaults to 0.5.
PERCENTILE_DISC (Discrete Value)
PERCENTILE_DISC (Oracle) is an inverse distribution function that assumes a discrete distribution model (It will return a value of the list) It takes a percentile value and a sort specification and returns an element from the set. Nulls are ignored in the calculation.
SELECT
PERCENTILE_DISC( 0.5 ) WITHIN GROUP(ORDER BY myCol DESC ) AS "median_limit"
, PERCENTILE_DISC( 0.1 ) WITHIN GROUP(ORDER BY myCol DESC ) AS "10_perc_limit"
, PERCENTILE_DISC( 0.2 ) WITHIN GROUP(ORDER BY myCol DESC ) AS "20_perc_limit"
FROM
my_table
Output:
MEDIAN_LIMIT | 10_perc_limit | 20_perc_limit |
---|---|---|
58370 | 432274 | 430842 |
PERCENTILE_CONT
You can see in the results that we got decimal which shows that they were extrapolated because the distribution is supposed to be continuous.
PERCENTILE_CONT (Oracle) is an inverse distribution function that assumes a continuous distribution model (It will extrapolate and return a calculated value of the list) It takes a percentile value and a sort specification, and returns an interpolated value that would fall into that percentile value with respect to the sort specification. Nulls are ignored in the calculation.
SELECT
PERCENTILE_CONT( 0.5 ) WITHIN GROUP(ORDER BY myCol DESC ) AS "median_limit"
, PERCENTILE_CONT( 0.1 ) WITHIN GROUP(ORDER BY myCol DESC ) AS "10_perc_limit"
, PERCENTILE_CONT( 0.2 ) WITHIN GROUP(ORDER BY myCol DESC ) AS "20_perc_limit"
FROM
my_table
Output:
MEDIAN_LIMIT | 10_perc_limit | 20_perc_limit |
---|---|---|
58370 | 432273,4 | 430841,8 |