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)
