Quantile - Functions

This page regroups all known quantile function.

• Quantile - Ntile (N Quantile)

Specific Oracle function with Quantile.

Oracle has also the ntile function.

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:

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: