PERCENTILE_DISC
PERCENTILE_DISC calculates a specific percentile value. It sorts the values in a specified column in ascending order and returns the first value whose cumulative distribution is greater than or equal to the specified percentile.
PERCENTILE_DISC is both an aggregate function and a window function.
Syntax
-- Aggregate function
PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])
-- Window function
PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])Parameters
Parameter | Required | Type | Description |
| Yes | — | A column that contains sortable values. |
| Yes | DOUBLE constant | The target percentile, in the range [0, 1]. |
| No | Boolean constant | Specifies whether to ignore NULL values. Default: |
| — | — | See Window functions. |
Return value
Returns the percentile value. The data type matches the data type of col_name.
Examples
Example 1: Calculate percentile values while ignoring NULL (default behavior)
SELECT
x,
PERCENTILE_DISC(x, 0) OVER() AS min,
PERCENTILE_DISC(x, 0.5) OVER() AS median,
PERCENTILE_DISC(x, 1) OVER() AS max
FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);Result:
+------------+------------+------------+------------+
| x | min | median | max |
+------------+------------+------------+------------+
| c | a | b | c |
| NULL | a | b | c |
| b | a | b | c |
| a | a | b | c |
+------------+------------+------------+------------+NULL is excluded from sorting. The three non-NULL values a, b, c are sorted in ascending order, so the 0th percentile is a, the 50th percentile is b, and the 100th percentile is c.
Example 2: Calculate percentile values with NULL treated as the minimum
SELECT
x,
PERCENTILE_DISC(x, 0, false) OVER() AS min,
PERCENTILE_DISC(x, 0.5, false) OVER() AS median,
PERCENTILE_DISC(x, 1, false) OVER() AS max
FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);Result:
+------------+------------+------------+------------+
| x | min | median | max |
+------------+------------+------------+------------+
| c | NULL | a | c |
| NULL | NULL | a | c |
| b | NULL | a | c |
| a | NULL | a | c |
+------------+------------+------------+------------+With isIgnoreNull set to false, NULL is ranked below a, b, and c. The 0th percentile becomes NULL, and the 50th percentile shifts from b to a.