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

memsql> create table example (a int, b int);

memsql> insert into example values(1,2),(2,2),(3,3),(4,3);

memsql> 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 |
+------+------+-----------------------------------+


memsql> 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 |
+------+------+---------------------------------+