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