Skip to main content
Version: 24.3.x

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$1
-- 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.