Purpose
PERCENTILE_DISC() returns the value of the smallest cumulative distribution (relative to the sorting specification) that is greater than or equal to the percentile value, based on a percentile value and a specified ordering specification. Nulls are ignored in the calculation.
Note
- When you use this function as an analytic function, use the
OVERclause to define a window over the data on which the function operates. The function operates on a group of rows to return a list of values. - When you use this function as an aggregate function, the function operates on a set of rows and returns a single value. You do not need to add the
OVERclause.
Syntax
PERCENTILE_DISC(expr1) WITHIN GROUP (ORDER BY expr2 [ DESC | ASC ])
[ OVER (query_partition_clause) ]
Parameters
| Parameter | Description |
|---|---|
| expr1 | The percentile value. It is a constant of the numeric data type in the range of [0, 1]. |
| expr2 | An expression for sorting and percentile calculation. The data type is numeric or datetime. |
| DESC | ASC | Optional. The sorting method of the list.
|
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions. |
Return type
The return type is the same as the data type of the expr2 parameter.
Examples
Assume that you have created the table tbl1.
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 dispersion 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 data by the col1 column and obtain the dispersion median of col4.
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