Outdated Version
You are viewing an older version of this section. View current production version.
PERCENT_RANK
Ranking function. 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 example (a int, b int);
insert into example values(1,2),(2,2),(3,3),(4,3);
select a,b, percent_rank() over(order by a,b) from 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 example;
+------+------+---------------------------------+
| a | b | percent_rank() over(order by b) |
+------+------+---------------------------------+
| 1 | 2 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0.6666666666666666 |
| 4 | 3 | 0.6666666666666666 |
+------+------+---------------------------------+
