9.6. Inverse Distribution Functions

9.6.1. PERCENTILE_CONT()

PERCENTILE_CONT is an inverse distribution function that applies a continuous distribution. It takes a percentile value and a sort specification and returns an element from the set defined by the sort specification. NULLs are ignored in the calculation.

Syntax 

PERCENTILE_CONT( percent ) <within-group-specification>
 
<within-group-specification> ::=
  !! See WITHIN GROUP syntax !!

Table 9.24. PERCENTILE_CONT Function Parameters
ParameterDescription

percent

Expression for the percentile of the value to return. Must be between 0 and 1 (inclusive). Must be constant within the group or — as a window function — partition.

within-group-specification

Must have a single sort-specification, which must be of a numeric type to perform interpolation.

The PERCENTILE_CONT function returns a value of type DOUBLE PRECISION or DECFLOAT(34) depending on the type of the argument in the within-group-specification. A value of type DECFLOAT(34) is returned if its ORDER BY contains an expression of type INT128, NUMERIC(38, x), or DECFLOAT(16 | 34), otherwise it returns DOUBLE PRECISION.

The result of PERCENTILE_CONT is computed by linear interpolation between values after ordering. Using the percentile value percent and the number of rows (n) in the group, you can compute the row number you are interested in after ordering the rows with respect to the sort specification. This row number (RN) is computed according to the formula RN = (1 + (percent * (n - 1)).

The final result of the aggregate function is computed by linear interpolation between the values from rows at row numbers CRN = ceiling(RN) and FRN = floor(RN).

Pseudo-code 

function f(N) ::= value of expression from row at N
 
if (CRN = FRN = RN) then
  return f(RN)
else
  return (CRN - RN) * f(FRN) + (RN - FRN) * f(CRN)

Example

PERCENTILE_CONT vs PERCENTILE_DISC 

select
  DEPT_NO,
  percentile_cont(0.5) within group (order by SALARY) as MEDIAN_CONT,
  percentile_disc(0.5) within group (order by SALARY) as MEDIAN_DISC
from EMPLOYEE
group by DEPT_NO;
 
DEPT_NO             MEDIAN_CONT           MEDIAN_DISC
======= ======================= =====================
000           133321.5000000000              53793.00
100           77631.25000000000              44000.00
110           65221.40500000000              61637.81
...

Using PERCENTILE_CONT as a window function 

select
  DEPT_NO,
  SALARY,
  percent_rank() over (partition by DEPT_NO order by SALARY) as "PERCENT_RANK",
  percentile_cont(0.5) within group (order by SALARY)
    over (partition by DEPT_NO) as MEDIAN_CONT
from EMPLOYEE
where DEPT_NO < 600
order by 1, 2;
 
DEPT_NO                SALARY            PERCENT_RANK             MEDIAN_CONT
======= ===================== ======================= =======================
000                  53793.00       0.000000000000000       133321.5000000000
000                 212850.00       1.000000000000000       133321.5000000000
100                  44000.00       0.000000000000000       77631.25000000000
100                 111262.50       1.000000000000000       77631.25000000000
110                  61637.81       0.000000000000000       65221.40500000000
110                  68805.00       1.000000000000000       65221.40500000000
...

See alsoSection 9.6.2, “PERCENTILE_DISC()”, Section 9.2, “WITHIN GROUP Clause for Aggregate Functions”

9.6.2. PERCENTILE_DISC()

PERCENTILE_DISC is an inverse distribution function that applies a discrete distribution. It takes a percentile value and a sort specification and returns an element from the set defined by the sort specification. NULLs are ignored in the calculation.

Syntax 

PERCENTILE_DISC( percent ) <within-group-specification>
 
<within-group-specification> ::=
  !! See WITHIN GROUP syntax !!

Table 9.25. PERCENTILE_DISC Function Parameters
ParameterDescription

percent

Expression for the percentile of the value to return. Must be between 0 and 1 (inclusive). Must be constant within the group or — as a window function — partition.

within-group-specification

Must have a single sort-specification, of any type that can be sorted.

The function PERCENTILE_DISC returns a value of the same type as the expression in ORDER BY of the within-group-specification.

For a given percentile value percent, PERCENTILE_DISC sorts the values of the expression in the ORDER BY clause and returns the value with the smallest CUME_DIST value (with respect to the same sort specification) that is greater than or equal to percent.

Example

See also the example in Section 9.6.1, “PERCENTILE_CONT()”.

Using PERCENTILE_DISC as a window function 

select
  DEPT_NO,
  SALARY,
  cume_dist() over (partition by DEPT_NO order by SALARY) as "CUME_DIST",
  percentile_disc(0.5) within group (order by SALARY)
    over (partition by DEPT_NO) as MEDIAN_DISC
from EMPLOYEE
where DEPT_NO < 600
order by 1, 2;
 
DEPT_NO                SALARY               CUME_DIST           MEDIAN_DISC
======= ===================== ======================= =====================
000                  53793.00      0.5000000000000000              53793.00
000                 212850.00       1.000000000000000              53793.00
100                  44000.00      0.5000000000000000              44000.00
100                 111262.50       1.000000000000000              44000.00
110                  61637.81      0.5000000000000000              61637.81
110                  68805.00       1.000000000000000              61637.81
...

See alsoSection 9.6.1, “PERCENTILE_CONT()”, Section 9.2, “WITHIN GROUP Clause for Aggregate Functions”