MySQL8.0窗口函数入门实践及总结

2023-07-22 11:44:05 299

前言

MySQL8.0之前，做数据排名统计等相当痛苦，因为没有像Oracle、SQLSERVER、PostgreSQL等其他数据库那样的窗口函数。但随着MySQL8.0中新增了窗口函数之后，针对这类统计就再也不是事了，本文就以常用的排序实例介绍MySQL的窗口函数。

1、准备工作

创建表及测试数据

mysql>usetestdb;
Databasechanged
/*创建表*/
mysql>createtabletb_score(idintprimarykeyauto_increment,stu_novarchar(10),coursevarchar(50),scoredecimal(4,1)，keyidx_stuNo_course(stu_no,course));
QueryOK,0rowsaffected(0.03sec)

mysql>showtables;
+------------------+
|Tables_in_testdb|
+------------------+
|tb_score|
+------------------+

/*新增一批测试数据*/
mysql>insertintotb_score(stu_no,course,score)values('2020001','mysql',90),('2020001','C++',85),('2020003','English',100),('2020002','mysql',50),('2020002','C++',70),('2020002','English',99);
QueryOK,6rowsaffected(0.00sec)
Records:6Duplicates:0Warnings:0
mysql>insertintotb_score(stu_no,course,score)values('2020003','mysql',78),('2020003','C++',81),('2020003','English',80),('2020004','mysql',80),('2020004','C++',60),('2020004','English',100);
QueryOK,6rowsaffected(0.01sec)
Records:6Duplicates:0Warnings:0
mysql>insertintotb_score(stu_no,course,score)values('2020005','mysql',98),('2020005','C++',96),('2020005','English',70),('2020006','mysql',60),('2020006','C++',90),('2020006','English',70);
QueryOK,6rowsaffected(0.01sec)
Records:6Duplicates:0Warnings:0
mysql>insertintotb_score(stu_no,course,score)values('2020007','mysql',50),('2020007','C++',66),('2020007','English',76),('2020008','mysql',90),('2020008','C++',69),('2020008','English',86);
QueryOK,6rowsaffected(0.01sec)
Records:6Duplicates:0Warnings:0
mysql>insertintotb_score(stu_no,course,score)values('2020009','mysql',70),('2020009','C++',66),('2020009','English',86),('2020010','mysql',75),('2020010','C++',76),('2020010','English',81);
QueryOK,6rowsaffected(0.01sec)
Records:6Duplicates:0Warnings:0
mysql>insertintotb_score(stu_no,course,score)values('2020011','mysql',90),('2020012','C++',85),('2020011','English',84),('2020012','English',75),('2020013','C++',96),('2020013','English',88);
QueryOK,6rowsaffected(0.01sec)
Records:6Duplicates:0Warnings:0

2、统计每门课程分数的排名

根据每门课程的分数从高到低进行排名，此时，会出现分数相同时怎么处理的问题，下面就根据不同的窗口函数来处理不同场景的需求

ROW_NUMBER

由结果可以看出，分数相同时按照学号顺序进行排名

mysql>selectstu_no,course,score,row_number()over(partitionbycourseorderbyscoredesc)rn
->fromtb_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|
+---------+---------+-------+----+
36rowsinset(0.00sec)mysql>selectstu_no,course,score,row_number()over(partitionbycourseorderbyscoredesc)rn
->fromtb_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|
+---------+---------+-------+----+
36rowsinset(0.00sec)

DENSE_RANK

为了让分数相同时排名也相同，则可以使用DENSE_RANK函数，结果如下：

mysql>selectstu_no,course,score,DENSE_RANK()over(partitionbycourseorderbyscoredesc)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|
+---------+---------+-------+----+
36rowsinset(0.00sec)

RANK

DENSE_RANK的结果是分数相同时排名相同了，但是下一个名次是紧接着上一个名次的，如果2个并列的第1之后，下一个我想是第3名，则可以使用RANK函数实现

mysql>selectstu_no,course,score,rank()over(partitionbycourseorderbyscoredesc)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|
+---------+---------+-------+----+
36rowsinset(0.01sec)

这样就实现了各种排序需求。

NTILE

NTILE函数的作用是对每个分组排名后，再将对应分组分成N个小组，例如

mysql>selectstu_no,course,score,rank()over(partitionbycourseorderbyscoredesc)rn,NTILE(2)over(partitionbycourseorderbyscoredesc)rn_groupfromtb_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|
+---------+---------+-------+----+----------+
36rowsinset(0.01sec)

3、窗口函数小结

MySQL中还有许多其他的窗口函数，本文列举一些，大家可以自行测试

排序分布前后首尾中

类别	函数	说明
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及之前版本的排序方式的实现很多人已总结，也建议实操一番。

总结

到此这篇关于MySQL8.0窗口函数入门实践及总结的文章就介绍到这了,更多相关MySQL8.0窗口函数实践内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

MySQL8.0窗口函数入门实践及总结

热门推荐

随机推荐