PERCENT_RANK
Returns the percent rank of the current row within the partition as specified by the order by clause.
The result is calculated with the following formula: (rank - 1)/(total_rows - 1)
Syntax
PERCENT_RANK () OVER ( [PARTITION BY (col | expr), ...] [ORDER BY (col | expr), ...] )
Return Type
A DOUBLE
data type.
Examples
CREATE TABLE pr_example (a int, b int); INSERT INTO pr_example VALUES(1,2),(2,2),(3,3),(4,3);
SELECT a,b, PERCENT_RANK() OVER(ORDER BY a,b) FROM pr_example; **** +------+------+-----------------------------------+ | a | b | percent_rank() over(order by a,b) | +------+------+-----------------------------------+ | 1 | 2 | 0 | | 2 | 2 | 0.3333333333333333 | | 3 | 3 | 0.6666666666666666 | | 4 | 3 | 1 | +------+------+-----------------------------------+
SELECT a,b, PERCENT_RANK() OVER(ORDER BY b) FROM pr_example; **** +------+------+---------------------------------+ | a | b | percent_rank() over(order by b) | +------+------+---------------------------------+ | 1 | 2 | 0 | | 2 | 2 | 0 | | 3 | 3 | 0.6666666666666666 | | 4 | 3 | 0.6666666666666666 | +------+------+---------------------------------+