percent_rank
ranking window function
Applies to: Databricks SQL Databricks Runtime
Computes the percentage ranking of a value within the partition.
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