PERCENTILE_DISC
Purpose
PERCENTILE_DISC is an inverse distribution function and returns the value from the group set which has the smallest cumulative distribution value (corresponding to the given sort order), which is larger than or equal to the specified percentile value. NULL values are ignored for the calculation.
Syntax
percentile_disc::=
Usage Notes
- NULL values are ignored for the computation.
- The expr must be a numeric value between 0 and 1. The expr must be constant within a partition. This implies that expr may depend on the partitioning attribute, so that it may vary in different partitions but must remain constant for all rows within a partition. Exasol returns an error when this condition is not satisfied.
- The order_clause is necessary for PERCENTILE_DISC function.
- The over_clause must not contain an order_clause and must not contain a window_frame_clause.The specified percentile value must be constant (between 0 and 1).
- For information about the over_clause and analytic functions in general, refer to the Analytic Functions section.
- For additional information, also refer to the inverse distribution functions PERCENTILE_CONT and MEDIAN.
Example
Analytic Function
SELECT
id, department, current_salary,
PERCENTILE_DISC(0.7) WITHIN GROUP (ORDER BY current_salary)
OVER (PARTITION BY department) PERCENTILE_DISC
FROM employee_table ORDER BY department, current_salary;
Result
ID | DEPARTMENT | CURRENT_SALARY | PERCENTILE_DISC |
2001 | ACCOUNTS | 55000 | 80000 |
2002 | ACCOUNTS | 65000 | 80000 |
2004 | ACCOUNTS | 70000 | 80000 |
2003 | ACCOUNTS | 80000 | 80000 |
2005 | ACCOUNTS | 80000 | 80000 |
1001 | HR | 55000 | 70000 |
1002 | HR | 70000 | 70000 |
1004 | HR | 70000 | 70000 |
1003 | HR | 90000 | 70000 |