CUME_DIST
Contents
Syntax
CUME_DIST() OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] )
Description
CUME_DIST() is a window function that returns the cumulative distribution of a given row. The following formula is used to calculate the value:
(number of rows <= current row) / (total rows)
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.