mysql 8.0 視窗函數：cume_dist,percent_rank介紹

MySQL 8.0 引入的視窗函數，本節主要介紹row_number,cume_dist,percent_rank

row_number,cume_dist,percent_rank

1. row_number()：顯示行号
2. cume_dist：計算某個值在一組有序的資料中累計的分布

   計算結果為相對位置/總行數,傳回值為(0,1]
   注意：對于重複值，計算的時候，取重複值的最後一行的位置           

3. percent_rank：和cume_dist 的不同點在于計算分布結果的方法

   計算方法為（相對位置-1）/（總行數-1）

   注意：對于重複值，計算的時候，取重複值的第一行的位
              

[email protected]>[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
[email protected]>[test]>select 1/8 as 'cume_dist_c1=1';
+----------------+
| cume_dist_c1-1 |
+----------------+
|         0.1250 |
+----------------+
1 row in set (0.00 sec)

[email protected]>[test]>select 5/8 as 'cume_dist_c1=10';
+-----------------+
| cume_dist_c1-10 |
+-----------------+
|          0.6250 |
+-----------------+
1 row in set (0.00 sec)

#percent_rank
[email protected]>[test]>select (2-1)/(8-1) as 'percent_rank-c1=7';
+-------------------+
| percent_rank-c1-7 |
+-------------------+
|            0.1429 |
+-------------------+
1 row in set (0.00 sec)

[email protected]>[test]>select (7-1)/(8-1) as 'percent_rank-c1=24';
+--------------------+
| percent_rank-c1=24 |
+--------------------+
|             0.8571 |
+--------------------+
1 row in set (0.00 sec)