PERCENT_RANK
Contents
Syntax
PERCENT_RANK() OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] )
Description
PERCENT_RANK() is a window function that returns the relative percent rank of a given row. The following formula is used to calculate the percent rank:
(rank - 1) / (number of rows in the window or partition - 1)
Examples
create table t1 ( pk int primary key, a int, b int ); insert into t1 values ( 1 , 0, 10), ( 2 , 0, 10), ( 3 , 1, 10), ( 4 , 1, 10), ( 8 , 2, 10), ( 5 , 2, 20), ( 6 , 2, 20), ( 7 , 2, 20), ( 9 , 4, 20), (10 , 4, 20); select pk, a, b, rank() over (order by a) as rank, percent_rank() over (order by a) as pct_rank, cume_dist() over (order by a) as cume_dist from t1; +----+------+------+------+--------------+--------------+ | pk | a | b | rank | pct_rank | cume_dist | +----+------+------+------+--------------+--------------+ | 1 | 0 | 10 | 1 | 0.0000000000 | 0.2000000000 | | 2 | 0 | 10 | 1 | 0.0000000000 | 0.2000000000 | | 3 | 1 | 10 | 3 | 0.2222222222 | 0.4000000000 | | 4 | 1 | 10 | 3 | 0.2222222222 | 0.4000000000 | | 5 | 2 | 20 | 5 | 0.4444444444 | 0.8000000000 | | 6 | 2 | 20 | 5 | 0.4444444444 | 0.8000000000 | | 7 | 2 | 20 | 5 | 0.4444444444 | 0.8000000000 | | 8 | 2 | 10 | 5 | 0.4444444444 | 0.8000000000 | | 9 | 4 | 20 | 9 | 0.8888888889 | 1.0000000000 | | 10 | 4 | 20 | 9 | 0.8888888889 | 1.0000000000 | +----+------+------+------+--------------+--------------+ select pk, a, b, percent_rank() over (order by pk) as pct_rank, cume_dist() over (order by pk) as cume_dist from t1 order by pk; +----+------+------+--------------+--------------+ | pk | a | b | pct_rank | cume_dist | +----+------+------+--------------+--------------+ | 1 | 0 | 10 | 0.0000000000 | 0.1000000000 | | 2 | 0 | 10 | 0.1111111111 | 0.2000000000 | | 3 | 1 | 10 | 0.2222222222 | 0.3000000000 | | 4 | 1 | 10 | 0.3333333333 | 0.4000000000 | | 5 | 2 | 20 | 0.4444444444 | 0.5000000000 | | 6 | 2 | 20 | 0.5555555556 | 0.6000000000 | | 7 | 2 | 20 | 0.6666666667 | 0.7000000000 | | 8 | 2 | 10 | 0.7777777778 | 0.8000000000 | | 9 | 4 | 20 | 0.8888888889 | 0.9000000000 | | 10 | 4 | 20 | 1.0000000000 | 1.0000000000 | +----+------+------+--------------+--------------+ select pk, a, b, percent_rank() over (partition by a order by a) as pct_rank, cume_dist() over (partition by a order by a) as cume_dist from t1; +----+------+------+--------------+--------------+ | pk | a | b | pct_rank | cume_dist | +----+------+------+--------------+--------------+ | 1 | 0 | 10 | 0.0000000000 | 1.0000000000 | | 2 | 0 | 10 | 0.0000000000 | 1.0000000000 | | 3 | 1 | 10 | 0.0000000000 | 1.0000000000 | | 4 | 1 | 10 | 0.0000000000 | 1.0000000000 | | 5 | 2 | 20 | 0.0000000000 | 1.0000000000 | | 6 | 2 | 20 | 0.0000000000 | 1.0000000000 | | 7 | 2 | 20 | 0.0000000000 | 1.0000000000 | | 8 | 2 | 10 | 0.0000000000 | 1.0000000000 | | 9 | 4 | 20 | 0.0000000000 | 1.0000000000 | | 10 | 4 | 20 | 0.0000000000 | 1.0000000000 | +----+------+------+--------------+--------------+
See Also
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.