PERCENTILE_DISC aggregate function

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

The schema is SYSIBM.

percentile

An expression that specifies the percentile. The expression must return a value that is a built-in numeric data type, CHAR, or VARCHAR data type. In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type. CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported by using implicit casting. If the expression is not a numeric data type, it is cast to DECFLOAT(34) before the function is evaluated. The expression must be a constant, a variable, or a cast of a constant or variable (SQLSTATE 428I9). The value must be 0 - 1 (SQLSTATE 22003).

WITHIN GROUP

Indicates that the aggregation follows the specified ordering within the grouping set.

ORDER BY: Specifies the order of the rows from the same grouping set that are processed in the aggregation.
sort-key: The sort key must be a column name or an expression that contains a column reference. sort-key must be a built-in numeric data type (SQLSTATE 42822). If the single column result set specified by sort-key contains one or more NULL values, they are not considered and a warning is returned (SQLSTATE 01003).
ASC: Processes the sort-key in ascending order.
DESC: Processes the sort-key in descending order.

The data type of the result is the same as the data type of sort-key.

The result can be null. If percentile is null or the single column result set specified by sort-key is empty, the result is NULL.

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

Example

Set the host variable PD to the value that corresponds to the 75th percentile of the salaries of the employees in department 'E21' using a discrete distribution model.

   SELECT PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY SALARY) 
      INTO :PD FROM EMPLOYEE WHERE WORKDEPT = 'E21'

PD is set to a value of 45370.00.

The following result set is shown for reference.

   SELECT SALARY FROM EMPLOYEE WHERE WORKDEPT = 'E21' ORDER BY SALARY

SALARY     
-----------
   31840.00
   35370.00
   39950.00
   43840.00
   45370.00
   86150.00

  6 record(s) selected.