Purpose
This function returns the minimum cumulative distribution value that is greater than or equal to the specified percentile value, based on the specified sorting criteria. 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 value for a set of rows and returns multiple values. - When used as an aggregate function, it computes the value for a set of rows and returns only one value. In this case, you do not need to use the
OVERclause.
Syntax
PERCENTILE_DISC(expr1) WITHIN GROUP (ORDER BY expr2 [ DESC | ASC ])
[ OVER (query_partition_clause) ]
Parameters
Parameter |
Description |
|---|---|
| expr1 | The value of the percentile, which is a constant of a numeric data type. The value must be in the range of [0,1]. |
| expr2 | The expression to be used for sorting and calculating the percentile. The data type of this expression must be a numeric or datetime type. |
| DESC | ASC | The sorting order. This parameter is optional.
|
| OVER | Use the OVER clause to define the window for the calculation. For more information, see Analytic Function Description. |
Return type
The same data type as the expr2 parameter.
Examples
The tbl1 table 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
Aggregate function example
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
Analytic function example
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