PERCENT_RANK aggregate function
The PERCENT_RANK function returns the relative percentile rank of a row that is hypothetically inserted into a group of rows.
The schema is SYSIBM.
- expression
- An expression that specifies a row that is hypothetically inserted into a group of rows. The expression must return a value that is a built-in data type. The expression must be a constant, a variable, or a cast of a constant or variable (SQLSTATE 428I9).
- order-by-clause
-
- ORDER BY
- Specifies the order of the rows from the same grouping set that are processed in the aggregation.
- sort-key
- The sort key can be a column name or a sort-key-expression. If the sort key is a constant, it does not refer to the position of the output column. A constant implies no sort key, unlike a constant in the ordinary ORDER BY clause.
- ASC
- Uses the values of the sort-key in ascending order.
- DESC
- Uses the values of the sort-key in descending order.
- NULLS FIRST
- The ordering considers null values before all non-null values in the sort order.
- NULLS LAST
- The ordering considers null values after all non-null values in the sort order.
The number of expressions must be the same as the number of sort-key expressions (SQLSTATE 42822). The data type of each expression and the data type of the corresponding sort-key expression must be compatible (SQLSTATE 42822).
The data type of the result is DECFLOAT(34). The actual result is greater than 0.0 and less than or equal to 1.0.
Example
Set the host variable PR to the relative percentile rank of a hypothetical new employee's salary
of 47000 within the salaries of the employees in department
'A00'.
SELECT PERCENT_RANK(47000) WITHIN GROUP (ORDER BY SALARY)
INTO :PR FROM EMPLOYEE WHERE WORKDEPT = 'A00'The following result set is shown for
reference.
SELECT SALARY FROM EMPLOYEE WHERE WORKDEPT = 'A00' ORDER BY SALARY
SALARY
-----------
39250.00
46500.00
49250.00
66500.00
152750.00
5 record(s) selected.