MySQL8.0新增了窗口函数，大大的方便了做数据排名统计的人，很多朋友还不清楚怎么用MySQL8.0做数据统计排名，下面就来为大家分享一篇心得文章。
MySQL8.0之前，做数据排名统计等相当痛苦，因为没有像Oracle、SQL SERVER 、PostgreSQL等其他数据库那样的窗口函数。但随着MySQL8.0中新增了窗口函数之后，针对这类统计就再也不是事了，本文就以常用的排序实例介绍MySQL的窗口函数。
1、准备工作
创建表及测试数据
mysql> use testdb;Database changed/*创建表 */mysql> create table tb_score(id int primary key auto_increment，stu_no varchar(10)，course varchar(50)，score decimal(4，1)，key idx_stuNo_course(stu_no，course));
Query OK， 0 rows affected (0.03 sec)
mysql> show tables;+------------------+| Tables_in_testdb |+------------------+| tb_score |+------------------+/* 新增一批测试数据 */mysql> insert into tb_score(stu_no，course，score)values('2020001'，'mysql'，90)，('2020001'，'C++'，85)，('2020003'，'English'，100)，('2020002'，'mysql'，50)，('2020002'，'C++'，70)，('2020002'，'English'，99);
Query OK， 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql> insert into tb_score(stu_no，course，score)values('2020003'，'mysql'，78)，('2020003'，'C++'，81)，('2020003'，'English'，80)，('2020004'，'mysql'，80)，('2020004'，'C++'，60)，('2020004'，'English'，100);
Query OK， 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql> insert into tb_score(stu_no，course，score)values('2020005'，'mysql'，98)，('2020005'，'C++'，96)，('2020005'，'English'，70)，('2020006'，'mysql'，60)，('2020006'，'C++'，90)，('2020006'，'English'，70);
Query OK， 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql> insert into tb_score(stu_no，course，score)values('2020007'，'mysql'，50)，('2020007'，'C++'，66)，('2020007'，'English'，76)，('2020008'，'mysql'，90)，('2020008'，'C++'，69)，('2020008'，'English'，86);
Query OK， 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql> insert into tb_score(stu_no，course，score)values('2020009'，'mysql'，70)，('2020009'，'C++'，66)，('2020009'，'English'，86)，('2020010'，'mysql'，75)，('2020010'，'C++'，76)，('2020010'，'English'，81);
Query OK， 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql> insert into tb_score(stu_no，course，score)values('2020011'，'mysql'，90)，('2020012'，'C++'，85)，('2020011'，'English'，84)，('2020012'，'English'，75)，('2020013'，'C++'，96)，('2020013'，'English'，88);
Query OK， 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 02、统计每门课程分数的排名
根据每门课程的分数从高到低进行排名，此时，会出现分数相同时怎么处理的问题，下面就根据不同的窗口函数来处理不同场景的需求
ROW_NUMBER
由结果可以看出，分数相同时按照学号顺序进行排名
mysql> select stu_no，course，score， row_number()over(partition by course order by score desc ) rn-> from tb_score;+---------+---------+-------+----+| stu_no | course | score | rn |+---------+---------+-------+----+| 2020005 | C++ | 96.0 | 1 || 2020013 | C++ | 96.0 | 2 || 2020006 | C++ | 90.0 | 3 || 2020001 | C++ | 85.0 | 4 || 2020012 | C++ | 85.0 | 5 || 2020003 | C++ | 81.0 | 6 || 2020010 | C++ | 76.0 | 7 || 2020002 | C++ | 70.0 | 8 || 2020008 | C++ | 69.0 | 9 || 2020007 | C++ | 66.0 | 10 || 2020009 | C++ | 66.0 | 11 || 2020004 | C++ | 60.0 | 12 || 2020003 | English | 100.0 | 1 || 2020004 | English | 100.0 | 2 || 2020002 | English | 99.0 | 3 || 2020013 | English | 88.0 | 4 || 2020008 | English | 86.0 | 5 || 2020009 | English | 86.0 | 6 || 2020011 | English | 84.0 | 7 || 2020010 | English | 81.0 | 8 || 2020003 | English | 80.0 | 9 || 2020007 | English | 76.0 | 10 || 2020012 | English | 75.0 | 11 || 2020005 | English | 70.0 | 12 || 2020006 | English | 70.0 | 13 || 2020005 | mysql | 98.0 | 1 || 2020001 | mysql | 90.0 | 2 || 2020008 | mysql | 90.0 | 3 || 2020011 | mysql | 90.0 | 4 || 2020004 | mysql | 80.0 | 5 || 2020003 | mysql | 78.0 | 6 || 2020010 | mysql | 75.0 | 7 || 2020009 | mysql | 70.0 | 8 || 2020006 | mysql | 60.0 | 9 || 2020002 | mysql | 50.0 | 10 || 2020007 | mysql | 50.0 | 11 |+---------+---------+-------+----+36 rows in set (0.00 sec)
DENSE_RANK
为了让分数相同时排名也相同，则可以使用DENSE_RANK函数，结果如下：
mysql> select stu_no，course，score， DENSE_RANK()over(partition by course order by score desc ) rn -> fromtb_score; +---------+---------+-------+----+| stu_no | course | score | rn |+---------+---------+-------+----+| 2020005 | C++ | 96.0 | 1 || 2020013 | C++ | 96.0 | 1 || 2020006 | C++ | 90.0 | 2 || 2020001 | C++ | 85.0 | 3 || 2020012 | C++ | 85.0 | 3 || 2020003 | C++ | 81.0 | 4 || 2020010 | C++ | 76.0 | 5 || 2020002 | C++ | 70.0 | 6 || 2020008 | C++ | 69.0 | 7 || 2020007 | C++ | 66.0 | 8 || 2020009 | C++ | 66.0 | 8 || 2020004 | C++ | 60.0 | 9 || 2020003 | English | 100.0 | 1 || 2020004 | English | 100.0 | 1 || 2020002 | English | 99.0 | 2 || 2020013 | English | 88.0 | 3 || 2020008 | English | 86.0 | 4 || 2020009 | English | 86.0 | 4 || 2020011 | English | 84.0 | 5 || 2020010 | English | 81.0 | 6 || 2020003 | English | 80.0 | 7 || 2020007 | English | 76.0 | 8 || 2020012 | English | 75.0 | 9 || 2020005 | English | 70.0 | 10 || 2020006 | English | 70.0 | 10 || 2020005 | mysql | 98.0 | 1 || 2020001 | mysql | 90.0 | 2 || 2020008 | mysql | 90.0 | 2 || 2020011 | mysql | 90.0 | 2 || 2020004 | mysql | 80.0 | 3 || 2020003 | mysql | 78.0 | 4 || 2020010 | mysql | 75.0 | 5 || 2020009 | mysql | 70.0 | 6 || 2020006 | mysql | 60.0 | 7 || 2020002 | mysql | 50.0 | 8 || 2020007 | mysql | 50.0 | 8 |+---------+---------+-------+----+36 rows in set (0.00 sec)
RANK
DENSE_RANK的结果是分数相同时排名相同了，但是下一个名次是紧接着上一个名次的，如果 2 个并列的第 1 之后，下一个我想是第 3 名，则可以使用RANK函数实现
mysql> select stu_no，course，score， rank()over(partition by course order by score desc ) rn -> fromtb_score;+---------+---------+-------+----+| stu_no | course | score | rn |+---------+---------+-------+----+| 2020005 | C++ | 96.0 | 1 || 2020013 | C++ | 96.0 | 1 || 2020006 | C++ | 90.0 | 3 || 2020001 | C++ | 85.0 | 4 || 2020012 | C++ | 85.0 | 4 || 2020003 | C++ | 81.0 | 6 || 2020010 | C++ | 76.0 | 7 || 2020002 | C++ | 70.0 | 8 || 2020008 | C++ | 69.0 | 9 || 2020007 | C++ | 66.0 | 10 || 2020009 | C++ | 66.0 | 10 || 2020004 | C++ | 60.0 | 12 || 2020003 | English | 100.0 | 1 || 2020004 | English | 100.0 | 1 || 2020002 | English | 99.0 | 3 || 2020013 | English | 88.0 | 4 || 2020008 | English | 86.0 | 5 || 2020009 | English | 86.0 | 5 || 2020011 | English | 84.0 | 7 || 2020010 | English | 81.0 | 8 || 2020003 | English | 80.0 | 9 || 2020007 | English | 76.0 | 10 || 2020012 | English | 75.0 | 11 || 2020005 | English | 70.0 | 12 || 2020006 | English | 70.0 | 12 || 2020005 | mysql | 98.0 | 1 || 2020001 | mysql | 90.0 | 2 || 2020008 | mysql | 90.0 | 2 || 2020011 | mysql | 90.0 | 2 || 2020004 | mysql | 80.0 | 5 || 2020003 | mysql | 78.0 | 6 || 2020010 | mysql | 75.0 | 7 || 2020009 | mysql | 70.0 | 8 || 2020006 | mysql | 60.0 | 9 || 2020002 | mysql | 50.0 | 10 || 2020007 | mysql | 50.0 | 10 |+---------+---------+-------+----+36 rows in set (0.01 sec)
这样就实现了各种排序需求。
NTILE
NTILE函数的作用是对每个分组排名后，再将对应分组分成N个小组，例如
mysql> select stu_no，course，score， rank()over(partition by course order by score desc )rn，NTILE(2)over(partition by course order by score desc ) rn_group fromtb_score;+---------+---------+-------+----+----------+| stu_no | course | score | rn | rn_group |+---------+---------+-------+----+----------+| 2020005 | C++ | 96.0 | 1 | 1 || 2020013 | C++ | 96.0 | 1 | 1 || 2020006 | C++ | 90.0 | 3 | 1 || 2020001 | C++ | 85.0 | 4 | 1 || 2020012 | C++ | 85.0 | 4 | 1 || 2020003 | C++ | 81.0 | 6 | 1 || 2020010 | C++ | 76.0 | 7 | 2 || 2020002 | C++ | 70.0 | 8 | 2 || 2020008 | C++ | 69.0 | 9 | 2 || 2020007 | C++ | 66.0 | 10 | 2 || 2020009 | C++ | 66.0 | 10 | 2 || 2020004 | C++ | 60.0 | 12 | 2 || 2020003 | English | 100.0 | 1 | 1 || 2020004 | English | 100.0 | 1 | 1 || 2020002 | English | 99.0 | 3 | 1 || 2020013 | English | 88.0 | 4 | 1 || 2020008 | English | 86.0 | 5 | 1 || 2020009 | English | 86.0 | 5 | 1 || 2020011 | English | 84.0 | 7 | 1 || 2020010 | English | 81.0 | 8 | 2 || 2020003 | English | 80.0 | 9 | 2 || 2020007 | English | 76.0 | 10 | 2 || 2020012 | English | 75.0 | 11 | 2 || 2020005 | English | 70.0 | 12 | 2 || 2020006 | English | 70.0 | 12 | 2 || 2020005 | mysql | 98.0 | 1 | 1 || 2020001 | mysql | 90.0 | 2 | 1 || 2020008 | mysql | 90.0 | 2 | 1 || 2020011 | mysql | 90.0 | 2 | 1 || 2020004 | mysql | 80.0 | 5 | 1 || 2020003 | mysql | 78.0 | 6 | 1 || 2020010 | mysql | 75.0 | 7 | 2 || 2020009 | mysql | 70.0 | 8 | 2 || 2020006 | mysql | 60.0 | 9 | 2 || 2020002 | mysql | 50.0 | 10 | 2 || 2020007 | mysql | 50.0 | 10 | 2 |+---------+---------+-------+----+----------+36 rows in set (0.01 sec)
3、窗口函数小结
| 类别 | 函数 | 说明 |
| 排序 | ROW_NUMBER | 为表中的每一行分配一个序号，可以指定分组（也可以不指定）及排序字段 |
| DENSE_RANK | 根据排序字段为每个分组中的每一行分配一个序号。 排名值相同时，序号相同，序号中没有间隙（1，1，2， 3 这种） | |
| RANK | 根据排序字段为每个分组中的每一行分配一个序号。 排名值相同时，序号相同，但序号中存在间隙（1，1，3， 4 这种） | |
| NTILE | 根据排序字段为每个分组中根据指定字段的排序再分成对应的组 | |
| 分布 | PERCENT_RANK | 计算各分组或结果集中行的百分数等级 |
| CUME_DIST | 计算某个值在一组有序的数据中累计的分布 | |
| 前后 | LEAD | 返回分组中当前行之后的第N行的值。如果不存在对应行，则返回NULL。比如N= 1 时，第一名对应的值是第二名的，最后一名结果是NULL |
| LAG | 返回分组中当前行之前的第N行的值。如果不存在对应行，则返回NULL。比如N= 1 时，第一名对应的值是是NUL，最后一名结果是倒数第 2 的值 | |
| 首尾中 | FIRST_VALUE | 返回每个分组中第一名对应的字段（或表达式）的值，例如本文中可以是第一名的分数、学号等任意字段的值 |
| LAST_VALUE | 返回每个分组中最后一名对应的字段（或表达式）的值，例如本文中可以是最后一名的分数、学号等任意字段的值 | |
| NTH_VALUE | 返回每个分组中排名第N的对应字段（或表达式）的值，但小于N的行对应的值是NULL |
MySQL中主要的窗口函数先总结这么多，建议还是得动手实践一番。另外，MySQL5. 7 及之前版本的排序方式的实现很多人已总结，也建议实操一番。
本文转载自微信公众号【数据库干货铺】