PERCENTILE_CONT aggregate function

The PERCENTILE_CONT function returns a percentile of a set of values. The set of values is treated as a continuous distribution. The calculated percentile is an interpolated value that might not have appeared in the input set.

The schema is SYSIBM.

percentile-expr: Specifies the percentile that is to be calculated by the function. percentile-expr must return a built-in numeric, character, or graphic string data type that is not a LOB. If the value is not a numeric data type is cast to DECFLOAT(34) before the function is evaluated. The value must be in the range 0–1.
WITHIN GROUP: Specifies that the percentile is to be calculated over the rows that are identified in the group.
sort-expression: Specifies the set of values over which to calculate the percentile and the order of the set. sort-expression must return a built-in numeric data type. A string value is implicitly cast to a numeric value. sort-expression must not include a scalar fullselect, or any function that is non-deterministic or has an external action. sort-key-expression must not reference a column for which a column mask is defined.
ASC: Specifies that the percentile is calculated using values from sort-expression in ascending order.
DESC: Specifies that the percentile is calculated using values from sort-expression in descending order.

The result is the value at the percentile that is specified by percentile-expr over the set of values identified by sort-expression.

For PERCENTILE_CONT, the set of values is treated as a continuous distribution. The calculated percentile is a interpolated value that might not have appeared in the input set. If the argument is DECFLOAT(n), the result of the function is DECFLOAT(34). Otherwise, the result of the function is a double-precision floating point number.

The function is applied to the set of values that are derived from sort-expression by the elimination of null values.

The result can be null. If percentile-expr is null, or if the function is applied to an empty set, the result is the null value.

The result of using PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sort-expression) is equivalent to MEDIAN(numeric-expression).

Examples for PERCENTILE_CONT

This example calculates the median salary of the employees in department D11 from the EMP table.
```
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SALARY)
FROM DSN8C10.EMP
WHERE WORKDEPT = ‘D11’;
```
The result is 24,680.00. Department D11 has 11 employees. The middle row of a group of 11 values in the sixth row. Because there are an odd number of rows, the PERCENTILE_CONT function for the percentile 0.5 returns the value of the sixth row, which is 24,680.00.
This example calculates the median commission of the employees in department E21 from the EMP table.
```
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY COMM)
FROM DSN8C10.EMP
WHERE WORKDEPT = ‘E21’;
```
The result is 1968.50. This example has an even number of rows, so the PERCENTILE_CONT function is computed by interpolating a value between the two middle rows. The two middle rows are row three with a value of 1907.00 and row four with a value of 2030.00. PERCENTILE_CONT is computed by averaging those two values, which is 1968.50.

This example calculates the 95th percentile of total salary per department from the EMP table.

SELECT PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY SALARY+BONUS)
FROM DSN8C10.EMP
GROUP BY WORKDEPT;