Quantile - Functions

About

This page regroups all known quantile function.

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

Powered by ComboStrap