PERCENTILE_DISC

The PERCENTILE_DISC function returns the value that corresponds to the specified percentile given a sort specification by using a discrete 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.

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. Each value in the input set is treated as a discrete value. The calculated percentile is always a value that appeared in the input set. The data type of the result is the same as the data type of the result of sort-expression.

If a collating sequence other than *HEX is in effect when the statement that contains the PERCENTILE_DISC function is executed, and the sort-expression is SBCS data, mixed data, or Unicode data, then the result is determined by comparing the weighted values. The weighted values are derived by applying the collating sequence to the sort-expression.

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_DISC function is defined to have a column mask, the column mask is not applied.

Examples

Example 1: Calculate the median salary as a discrete value of the employees in department D11.
```
SELECT PERCENTILE_DISC(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_DISC for the percentile 0.5 returns the value of the sixth row, which is 24680.00.
Example 2: Calculate the median commission as a discrete value of the employees in department E21.
```
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY COMM)
FROM EMPLOYEE
WHERE WORKDEPT = 'E21'
```
The result is 1907.00. Since there are an even number of rows, PERCENTILE_DISC is computed by returning the first of the two middle rows, which is row three with value 1907.00.