percent_rank ranking window function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Computes the percentage ranking of a value within the partition.

Syntax 

percent_rank()

Arguments

The function takes no arguments

Returns

A DOUBLE.

The function is defined as the rank within the window minus one divided by the number of rows within the window minus 1. If the there is only one row in the window the rank is 0.

As an expression the semantic can be expressed as:

nvl((rank() OVER(PARTITION BY p ORDER BY o) - 1) / nullif(count(1) OVER(PARTITION BY p) -1), 0), 0)

This function is similar, but not the same as cume_dist analytic window function.

You must include ORDER BY clause in the window specification.

Examples 

> SELECT a, b, percent_rank(b) OVER (PARTITION BY a ORDER BY b)
    FROM VALUES ('A1', 2), ('A1', 1), ('A1', 3), ('A1', 6), ('A1', 7), ('A1', 7), ('A2', 3), ('A1', 1) tab(a, b)
 A1 1  0.0
 A1 1  0.0
 A1 2  0.3333333333333333
 A1 3  0.5
 A1 6  0.6666666666666666
 A1 7  0.8333333333333334
 A1 7  0.8333333333333334
 A2 3  0.0