Purpose
You can call this function to return 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 it 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 it 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 | Specifies the percentile value. It is a constant of the numeric data type in the range of [0,1]. |
| expr2 | Specifies 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 about analytic functions, see Analytic functions. |
Return types
This function returns the same value type as 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
Aggregate example
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
Analytic example
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