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 eth.recent_blocks 

-- 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 Spice. NULL values in the data set are ignored. However, passing NULL as fraction or as order_by_expression will cause an error.