Inverse distribution functions family syntax

SELECT fn(<expr>) WITHIN GROUP(ORDER BY <value_expression> [asc|desc] 
[nulls {first | last}]) FROM <from_expr>[GROUP BY <group_expr>];

percentile_cont (expr)

This is computed by considering the pair of consecutive rows that are indicated by the argument, treated as a fraction of the total number of rows in the group, and interpolating the value of the value expression that is evaluated for these rows. The interpolated value x is obtained by using argument y. It is the one-sided inverse of percent_rank, and can be used only with numeric and interval data types. This is computed as follows:

X = xf( rc-r) + xc( r –rf)

Where

• Y is the argument to the function
• r = Y *(N-1) + 1
• N is the number of values in the ordered partition/group
• rf = floor(r), rc=ceil(r)
• xf = value of order by column at row rf
• xc = value of order by column at rc

percentile_cont(0.5) is commonly known as a median calculation. The NPS database also offers a built-in median() aggregate function to perform this calculation.

percentile_disc (expr)

This is computed by treating the group as a window partition of the CUME_DIST window function by using the specified ordering of the value expression as the window ordering, and returning the first value expression whose cumulative distribution value is greater than or equal to the argument. Works with all data types.

X =xc

Where

• r = Y*N
• rc = ceil(r)
• xc is value of order by column at row rc

Note: The value of percentile_disc(0) is null.