Purpose
The PERCENTILE_DISC function returns the minimum cumulative distribution value that is greater than or equal to the specified percentile value, based on the specified sort specification. Null values are ignored in the calculation.
Note
- When used as an analytic function, you must use the
OVERclause to define the window for the calculation. It computes the result for a set of rows and returns multiple values. - When used as an aggregate function, it aggregates a set of rows and returns only one value. In this case, the
OVERclause is not required.
Syntax
PERCENTILE_DISC(expr1) WITHIN GROUP (ORDER BY expr2 [ DESC | ASC ])
[ OVER (query_partition_clause) ]
Parameters
Parameter |
Description |
|---|---|
| expr1 | The percentile value, which is a constant of the numeric data type. The value ranges from [0,1]. |
| expr2 | The expression to be used for sorting and calculating the percentile. The data type is numeric or datetime. |
| DESC | ASC | The sort order, which is optional.
|
| OVER | Use the OVER clause to define the window for the calculation. For more information, see Analytic Function Description. |
Return type
The data type of the result is the same as that of the expr2 parameter.
Examples
A table named tbl1 has been created.
obclient> SELECT * FROM tbl1;
+------+------+------+------+
| COL1 | COL2 | COL3 | COL4 |
+------+------+------+------+
| 1 | A1 | 8 | 12 |
| 1 | A2 | 10 | 15 |
| 1 | A3 | 11 | 16 |
| 2 | B1 | 9 | 14 |
| 2 | B2 | 10 | 15 |
| 2 | B3 | 8 | 13 |
| 2 | B4 | 11 | 16 |
| 3 | C1 | 8 | 18 |
| 3 | C2 | 9 | 16 |
| 3 | C3 | 10 | 15 |
| 3 | C4 | 11 | 12 |
| 3 | C5 | 12 | 10 |
+------+------+------+------+
12 rows in set
Example of an aggregate function
Calculate the median of the col4 column.
obclient> SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY col4) "PERCENTILE_DISC" FROM tbl1;
+-----------------+
| PERCENTILE_DISC |
+-----------------+
| 15 |
+-----------------+
1 row in set
Example of an analytic function
Group by the col1 column and calculate the median of the col4 column.
obclient> SELECT col1,col4,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY col4) OVER(PARTITION BY col1) "PERCENTILE_DISC"
FROM tbl1;
+------+------+-----------------+
| COL1 | COL4 | PERCENTILE_DISC |
+------+------+-----------------+
| 1 | 12 | 15 |
| 1 | 15 | 15 |
| 1 | 16 | 15 |
| 2 | 14 | 14 |
| 2 | 15 | 14 |
| 2 | 13 | 14 |
| 2 | 16 | 14 |
| 3 | 18 | 15 |
| 3 | 16 | 15 |
| 3 | 15 | 15 |
| 3 | 12 | 15 |
| 3 | 10 | 15 |
+------+------+-----------------+
12 rows in set