PERCENTILE_CONT

The PERCENTILE_CONT function returns the value that corresponds to the specified percentile given a sort specification by using a continuous distribution model.

percentile-expression

Specifies the percentile to be calculated by the function. percentile-expression must return any built-in numeric, character-string, or graphic-string data type. If the argument is a character-string or graphic-string, it is cast to DECFLOAT(34) before evaluating the function. The value must be between 0 and 1. percentile-expression must not contain a scalar-fullselect, a column reference, or a user-defined function reference.

WITHIN GROUP

Specifies that the percentile is to be calculated over the rows identified in the group.

sort-expression

Specifies the set of values over which to calculate the percentile as well as the order of the set. sort-expression must return a built-in numeric, character string, or graphic string data type. If the sort-expression is a character-string or graphic-string, it is cast to DECFLOAT(34) before evaluating the function.

ASC: Specifies that the percentile is to be calculated using values from sort-expression in ascending order.
DESC: Specifies that the percentile is to be calculated using values from sort-expression in descending order.

The result is the value at the percentile specified by percentile-expression over the set of values identified by sort-expression. The set of values is treated as a continuous distribution. The calculated percentile is an interpolated value which might not have appeared in the input set. If the data type of sort-expression is DECFLOAT(n), the result of the function is DECFLOAT(34). Otherwise the data type of the result is double-precision floating point.

The function is applied to the set of values derived from the argument values by the elimination of null values.

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

Notes®

When used in an OLAP specification, only the window-partition-clause can be specified.

Results involving DECFLOAT special values: If the data type of the argument is decimal floating-point and a special value of sNaN or -sNaN, or both +Infinity and -Infinity are included in the aggregation, an error or warning is returned. Otherwise, if +NaN or -NaN is found, the result is +NaN or -NaN. If +Infinity or -Infinity is found, the result is +Infinity or -Infinity.

Column masks: If a column that is referenced in the sort-expression of the PERCENTILE_CONT function is defined to have a column mask, the column mask is not applied.

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

Examples

Example 1: Calculate the median salary of the employees in department D11.
```
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SALARY) FROM EMPLOYEE
WHERE WORKDEPT = 'D11'
```
The result is 24680.00. There are 11 employees in department D11. The middle row of a group of 11 values is the sixth row. Since there are an odd number of rows, PERCENTILE_CONT for the percentile 0.5 returns the value of the sixth row, which is 24680.00.
Example 2: Calculate the median commission of the employees in department E21.
```
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY COMM) FROM EMPLOYEE
WHERE WORKDEPT = 'E21'
```
The result is 1968.5. Since there are an even number of rows, the PERCENTILE_CONT is computed by interpolating a value between the middle two rows. The middle two rows are row three with value 1907.00 and row four with value 2030.00. PERCENTILE_CONT is computed by averaging those two values, which results in 1968.5.