有了这些函数，统计分析事半功倍

2020-06-26 00:00:00 函数字段排名窗口组中

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

准备工作

创建表及测试数据

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: 0

统计排名

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

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    -> from  tb_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    -> from  tb_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  from  tb_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)

窗口函数小结

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

>>>>

近期活动

2020年，Gdevops全球敏捷运维峰会开启了传播前沿技术、助力技术人成长进阶、促进跨界交流合作的第五个年头。依托迄今成功举办的17场大会在分享议题上的精心打磨、在技术圈子里的口碑传播，Gdevops在展开新一年技术巡演中邀请到更的嘉宾阵容，将带来更重磅的科技成果与实践。点击链接了解更多情况

2020 Gdevops全球敏捷运维峰会

往期精彩回顾

1. MySQL高可用之MHA集群部署

2. mysql8.0新增用户及加密规则修改的那些事

3. 比hive快10倍的大数据查询利器-- presto

4. 监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库

5. PostgreSQL主从复制--物理复制

6. MySQL传统点位复制在线转为GTID模式复制

7. MySQL敏感数据加密及解密

8. MySQL数据备份及还原（一）

9. MySQL数据备份及还原（二）

扫

码

关

注

相关文章