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: Specifies the values to sort and compute the percentile. You can only provide one expression in the ORDER BY clause. By default, the sort order is ascending (ASC).

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 (i.e., 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. Note: This function may only be used with numeric data types in Dremio. Null values in the data set are ignored. However any null values in the command (null fraction or null order_by_expression) will throw an exception in Dremio.