PERCENT_RANK

The PERCENT_RANK() is a window function that calculates the percentile ranking of rows in a result set.

Syntax:

PERCENT_RANK (expr)
  OVER ([partition_by_clause] order_by_clause)

Calculates the rank, expressed as a percentage, of each row within a group of rows. If rank is the value for that same row from the RANK() function (from 1 to the total number of rows in the partition group), then the PERCENT_RANK() value is calculated as (rank - 1) / (rows_in_group - 1) . If there is only a single item in the partition group, its PERCENT_RANK() value is 0.

The ORDER BY clause is required. The PARTITION BY clause is optional. The window clause is not allowed.

Usage notes:

This function is similar to the RANK and CUME_DIST() functions: it returns an ascending sequence representing the position of each row within the rows of the same partition group. The actual numeric sequence is calculated differently, and the handling of duplicate (tied) values is different.

The return values range from 0 to 1 inclusive. The first row in each partition group always has the value 0. A NULL value is considered the lowest possible value. In the case of duplicate input values, all the corresponding rows in the result set have an identical value: the lowest PERCENT_RANK() value of those tied rows. (In contrast to CUME_DIST(), where all tied rows have the highest CUME_DIST() value.)

Examples:

The following example uses the same ANIMALS table as the examples for CUME_DIST() and NTILE(), with a few additional rows to illustrate the results where some values are NULL or there is only a single row in a partition group.

insert into animals values ('Komodo dragon', 'Reptile', 70);
insert into animals values ('Unicorn', 'Mythical', NULL);
insert into animals values ('Fire-breathing dragon', 'Mythical', NULL);

As with CUME_DIST(), there is an ascending sequence for each kind of animal. For example, the “Birds” and “Mammals” rows each have a PERCENT_RANK() sequence that ranges from 0 to 1. The “Reptile” row has a PERCENT_RANK() of 0 because that partition group contains only a single item. Both “Mythical” animals have a PERCENT_RANK() of 0 because a NULL is considered the lowest value within its partition group.

select name, kind, percent_rank() over (partition by kind order by kilos) from animals;
+-----------------------+----------+--------------------------+
| name                  | kind     | percent_rank() OVER(...) |
+-----------------------+----------+--------------------------+
| Mouse                 | Mammal   | 0                        |
| Housecat              | Mammal   | 0.2                      |
| Horse                 | Mammal   | 0.4                      |
| Polar bear            | Mammal   | 0.6                      |
| Giraffe               | Mammal   | 0.8                      |
| Elephant              | Mammal   | 1                        |
| Komodo dragon         | Reptile  | 0                        |
| Owl                   | Bird     | 0                        |
| California Condor     | Bird     | 0.25                     |
| Andean Condor         | Bird     | 0.25                     |
| Condor                | Bird     | 0.25                     |
| Ostrich               | Bird     | 1                        |
| Fire-breathing dragon | Mythical | 0                        |
| Unicorn               | Mythical | 0                        |
+-----------------------+----------+--------------------------+