mysql percent rank_mysql 8.0 窗口函数：cume_dist,percent_rank介绍

MySQL 8.0 引入的窗口函数，本节主要介绍row_number,cume_dist,percent_rank

row_number,cume_dist,percent_rank

row_number()：显示行号

cume_dist：计算某个值在一组有序的数据中累计的分布

计算结果为相对位置/总行数,返回值为(0,1]

注意：对于重复值，计算的时候，取重复值的最后一行的位置

percent_rank：和cume_dist 的不同点在于计算分布结果的方法

计算方法为(相对位置-1)/(总行数-1)

注意：对于重复值，计算的时候，取重复值的第一行的位

root@my3308.sock-8.0.11>[test]>select ROW_NUMBER() OVER w AS 'row_number',

c1,

CUME_DIST() OVER w AS 'cume_dist',PERCENT_RANK() OVER w AS 'percent_rank'

from x window w as (order by c1);

+------------+------+-----------+---------------------+

| row_number | c1 | cume_dist | percent_rank |

+------------+------+-----------+---------------------+

| 1 | 1 | 0.125 | 0 |

| 2 | 7 | 0.375 | 0.14285714285714285 |

| 3 | 7 | 0.375 | 0.14285714285714285 |

| 4 | 10 | 0.625 | 0.42857142857142855 |

| 5 | 10 | 0.625 | 0.42857142857142855 |

| 6 | 11 | 0.75 | 0.7142857142857143 |

| 7 | 24 | 1 | 0.8571428571428571 |

| 8 | 24 | 1 | 0.8571428571428571 |

+------------+------+-----------+---------------------+

8 rows in set (0.00 sec)

#cume_dist

root@my3308.sock-8.0.11>[test]>select 1/8 as 'cume_dist_c1=1';

+----------------+

| cume_dist_c1-1 |

+----------------+

| 0.1250 |

+----------------+

1 row in set (0.00 sec)

root@my3308.sock-8.0.11>[test]>select 5/8 as 'cume_dist_c1=10';

+-----------------+

| cume_dist_c1-10 |

+-----------------+

| 0.6250 |

+-----------------+

1 row in set (0.00 sec)

#percent_rank

root@my3308.sock-8.0.11>[test]>select (2-1)/(8-1) as 'percent_rank-c1=7';

+-------------------+

| percent_rank-c1-7 |

+-------------------+

| 0.1429 |

+-------------------+

1 row in set (0.00 sec)

root@my3308.sock-8.0.11>[test]>select (7-1)/(8-1) as 'percent_rank-c1=24';

+--------------------+

| percent_rank-c1=24 |

+--------------------+

| 0.8571 |

+--------------------+

1 row in set (0.00 sec)