PERCENTILE_DISC window function - Amazon Redshift

PERCENTILE_DISC window function

PERCENTILE_DISC is an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns an element from the given set.

For a given percentile value P, PERCENTILE_DISC sorts the values of the expression in the ORDER BY clause and returns the value with the smallest cumulative distribution value (with respect to the same sort specification) that is greater than or equal to P.

You can specify only the PARTITION clause in the OVER clause.

PERCENTILE_DISC is a compute-node only function. The function returns an error if the query doesn't reference a user-defined table or Amazon Redshift system table.

Syntax

PERCENTILE_DISC ( percentile ) WITHIN GROUP (ORDER BY expr) OVER ( [ PARTITION BY expr_list ] )

Arguments

percentile

Numeric constant between 0 and 1. Nulls are ignored in the calculation.

WITHIN GROUP ( ORDER BY expr)

Specifies numeric or date/time values to sort and compute the percentile over.

OVER

Specifies the window partitioning. The OVER clause cannot contain a window ordering or window frame specification.

PARTITION BY expr

Optional argument that sets the range of records for each group in the OVER clause.

Returns

The same data type as the ORDER BY expression in the WITHIN GROUP clause.

Examples

The following examples uses the WINSALES table. For a description of the WINSALES table, see Overview example for window functions.

select sellerid, qty, percentile_disc(0.5) within group (order by qty) over() as median from winsales; sellerid | qty | median ----------+-----+-------- 1 | 10 | 20 3 | 10 | 20 1 | 10 | 20 4 | 10 | 20 3 | 15 | 20 2 | 20 | 20 2 | 20 | 20 3 | 20 | 20 1 | 30 | 20 3 | 30 | 20 4 | 40 | 20 (11 rows)
select sellerid, qty, percentile_disc(0.5) within group (order by qty) over(partition by sellerid) as median from winsales; sellerid | qty | median ----------+-----+-------- 2 | 20 | 20 2 | 20 | 20 4 | 10 | 10 4 | 40 | 10 1 | 10 | 10 1 | 10 | 10 1 | 30 | 10 3 | 10 | 15 3 | 15 | 15 3 | 20 | 15 3 | 30 | 15 (11 rows)