Skip to content

Latest commit

 

History

History
31 lines (21 loc) · 1.87 KB

percentile_disc.md

File metadata and controls

31 lines (21 loc) · 1.87 KB
description
Computes a specific percentile for sorted values in a column.

PERCENTILE_DISC

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

{% code title="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

{% endcode %}

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.