Dremio Cloud
Dremio Software
 
On this page

    Categories: Percentile

    PERCENTILE_DISC

    Computes a specific percentile for sorted values in a column.

    Syntax

    PERCENTILE_DISC(fraction double precision) WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] ) → double precision

    • fraction: The fraction/percentile value to compute. The value for this must be a numeric literal in the range of 0 to 1 inclusive and represents a percentage.
    • order_by_expression: The expression to sort and compute the percentile. You can only provide one expression in the ORDER BY clause.

    Examples

    PERCENTILE_DISC example 
    SELECT PERCENTILE_DISC(0.6) WITHIN GROUP ( ORDER BY pop ASC ), 
PERCENTILE_DISC(0.6) WITHIN GROUP ( ORDER BY pop DESC )
FROM Samples."samples.dremio.com"."zips.json"

-- EXPR$0, EXPR$0
-- 4520.0, 1806.0

    Usage Notes

    This function computes a specific percentile for sorted values in a column. For each percentile value, PERCENTILE_DISC sorts the values using the ORDER BY clause. The function then returns the value with the smallest CUME_DIST value given that is greater or equal to the percentile value. For example, PERCENTILE_DISC (0.5) computes the 50th percentile (the median) of an expression. The result is equal to a specific column value. This information was originally provided via Microsoft’s Transact-SQL Reference Guide.

    This function is used with only numeric data types in Dremio. NULL values in the data set are ignored. However, passing NULL as fraction or as order_by_expression will cause an error.