Returns the first value in an ascending-sorted column whose cumulative distribution is greater than or equal to the specified percentile. The return value is always an actual value from the column.
Syntax
-- Use as an aggregate function
PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])
-- Use as a window function
PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])Parameters
| Parameter | Required | Type | Description |
|---|---|---|---|
col_name | Yes | Column reference | The column to sort and return a value from. This column serves as both the sort key and the source of the return value. |
percentile | Yes | DOUBLE constant | The target percentile. Must be in the range [0, 1]. |
isIgnoreNull | No | BOOLEAN constant | Whether to ignore NULL values. Default: TRUE. Set to FALSE to treat NULL as the minimum value during sorting. |
partition_clause, orderby_clause | No | Window clause | Window definition clauses used with OVER. For syntax details, see Window functions. |
Return value
Returns a value from col_name. The return type matches the data type of col_name.
Examples
Calculate min, median, and max using a window function (NULL values ignored)
By default, isIgnoreNull is TRUE, so NULL rows are excluded from the percentile calculation.
SELECT
x,
PERCENTILE_DISC(x, 0) OVER() AS min, -- 0th percentile = minimum
PERCENTILE_DISC(x, 0.5) OVER() AS median, -- 50th percentile = median
PERCENTILE_DISC(x, 1) OVER() AS max -- 100th percentile = maximum
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 |
Calculate min, median, and max with NULL treated as the minimum value
Set isIgnoreNull to FALSE to include NULL in the sort order. NULL is treated as less than any non-NULL value, so it becomes the minimum.
SELECT
x,
PERCENTILE_DISC(x, 0, false) OVER() AS min, -- NULL is the minimum
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 |
Related functions
PERCENTILE_DISC works as both an aggregate function and a window function.