MySQL8.0新增了窗口函数,大大的方便了做数据排名统计的人,很多朋友还不清楚怎么用MySQL8.0做数据统计排名,下面就来为大家分享一篇心得文章。
MySQL8.0之前,做数据排名统计等相当痛苦,因为没有像Oracle、SQL SERVER 、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:0mysql>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:0mysql>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:0mysql>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:0mysql>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:0mysql>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)
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、窗口函数小结
类别 | 函数 | 说明 |
排序 | 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 及之前版本的排序方式的实现很多人已总结,也建议实操一番。
本文转载自微信公众号【数据库干货铺】
(举报)