percentile_disc ​

Selects an actual input value at the requested percentile. Unlike percentile_cont, this function does not interpolate between values. NULL values are skipped.

Syntax

percentile_disc(fraction) WITHIN GROUP (ORDER BY value [ASC|DESC])
percentile_disc(fraction, value[, descending])

Arguments

• fraction — A number from zero through one, independent of the group's input rows. It is validated even when the input is empty.
• value — An ordered SQL value. Comparison uses its type and collation; NULL values are skipped.
• descending — An optional constant boolean selecting descending order in the direct form. The default order is ascending.

Use FILTER to select input rows. Window calls with OVER are not supported.

Returned value

The original input value at position ceil(fraction * count), counting from one. A fraction of zero selects the first value. A NULL fraction or no non-NULL inputs returns NULL.

Examples

CREATE TABLE readings (value Int32);
INSERT INTO readings VALUES (10), (20), (30), (40), (NULL);

SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY value)
FROM readings
FORMAT csv;

20