PERCENTILE_CONT aggregate function
The PERCENTILE_CONT function returns the value that corresponds to the specified percentile given a sort specification by using a continuous distribution model.
The schema is SYSIBM.
- 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 through 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.
- 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).
- Processes the sort-key in ascending order.
- Processes the sort-key in descending order.
If the data type of sort-key is DECFLOAT(n), the data type of the result is DECFLOAT(34). Otherwise, the data type of the result is DOUBLE.
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.
Set the host variable
PCto the value that corresponds to the 75th percentile of the salaries of the employees in department 'E21' using a continuous distribution model.
SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SALARY) INTO :PC FROM EMPLOYEE WHERE WORKDEPT = 'E21'
PCis set to a value of 44987.50.
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.