安科网

MySQL8.0窗口函数实践及小结

hungzz

hungzz

2020-06-26

关注 关注

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

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

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

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)

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窗口函数实践及小结

mysql mysql数据库 mysql函数

hungzz

hungzz

0 关注 0 粉丝 0 动态

关注 关注

相关推荐

MySQL主从复制原理以及需要注意的地方

最近在写Mycat专题，由于不少小伙伴最近要出去面试，问我能不能简单写下MySQL的主从复制原理和注意事项，因为在之前的面试中被问到了这些问题。 Master 将数据改变记录到二进制日志中，也就是配置文件 log-bin 指定的文件， 这些记录叫做二进制日

emmm00 2020-11-17

Mysql联表update数据的示例详解

在MySQL中，可以在 UPDATE语句 中使用JOIN子句执行跨表更新。employees表将存储在员工编号，姓名，工作表现和工资的数据。employees 和 merits 表之间以是 performance 字段相关联的。对于 employees 表

王艺强 2020-11-17

mysql 8.0.22 安装配置图文教程

解压后的目录并没有的my.ini文件，没关系可以自行创建在安装根目录下添加的my.ini ，写入基本配置：。# 允许连接失败的次数。初始化MySQL，在安装时，避免权限问题出错我们尽量使用管理员身份运行CMD，否则在安装时会报错，会导致安装失败的情况，如下

aydh 2020-11-12

MySQL用truncate命令快速清空一个数据库中的所有表

用文本编辑器把每条truncate语句前后的“|”替换为空字符，方便后面一次复制多条执行。truncate与drop是DDL语句，执行后无法回滚；delete是DML语句，可回滚。truncate会清空表中的所有行，但表结构及其约束、索引等保持不变；dro

世樹 2020-11-11

修改MySQL8.0 默认的数据目录(快捷操作无配置)

使用场景：我们使用的是阿里云，单独购买了数据盘，MySQL 8.0 数据库默认装在系统盘上，为了考虑安全性和空间问题，我们需要将默认的数据库目录更改到其它位置。操作系统：CentOS 7.6 数据库：MySQL 8.0 查看我本人更多原创文章，请点击

zry 2020-11-11

阿里云服务器安装Mysql数据库的详细教程

由于在学习过程中需要安装zookeeper，我的虚拟机一直有问题，就够买了阿里云服务器。安装完zookeeper后想着把数据库也安装在服务器上，释放一下电脑的压力，在安装数据库的时候遇到了很多问题，通过查看有些大佬的作品终于安装好了数据库。现在就我遇到的问

URML 2020-11-11

MySQL中的undo日志

我们知道，MySQL中的redo日志记录了事务的行为，在服务器宕机的时候，可以通过重做事务来达到恢复数据的目的，然而，有的时候，事务还有回滚的需求，也就是说，我们需要知道某条在变成当前情况之前的样子，这种情况下，undo日志就派上用场了。也就是说，undo

spurity 2020-11-10

php7连接MySQL实现简易查询程序的方法

假设我们制作的是分班情况查询程序，将使用PHP7的环境以PDO的方式连接MySQL。通过学号和姓名查询自己所在班级。准备就绪，开始吧，现在！echo "<tr><th>学号</th><th>姓名&l

yifangs 2020-10-13

JDBC连接MySQL

String sql = "select * from users where name=?String id = rs.getString;//1代表数据库中表的列数，id在第一列也可以！！！

Andrea0 2020-09-18

Uber为什么放弃Postgres选择迁移到MySQL？

Uber 的早期架构包含了一个用 Python 开发的单体后端应用程序，这个应用程序使用 Postgres 作为数据存储。从那个时候开始，Uber 的架构已经发生了巨大变化，变成了微服务，并采用新的数据平台模型。具体地说，之前使用 Postgres 的地方

Ida 2020-09-16

聊聊MySQL中的参数

在前面一些文章中，经常能看到介绍某某参数的作用，可能有些小伙伴仍搞不清楚 MySQL 参数是啥。我们所说的参数在官方文档中称为 系统变量，不同的变量有着不同的作用。MySQL 服务端维护了许多表示其配置的系统变量，所有变量均有默认值。若会话变量未单独设置，

ltd00 2020-09-12

shell脚本定时备份MySQL数据库数据并保留指定时间

公司用到的MySQL数据库，经常有同事通过一顿骚操作把一些关键的配置数据误删，每次恢复都要花上不少时间，于是写了个shell脚本，结合corntab每天凌晨备份数据库，并保留7天。同样的备份脚本也可用于生产环境MySQL数据库定时备份。上述脚本中指定了要备

tufeiax 2020-09-03

Mysql误删数据解决方案及kill语句原理

使用flashback工具闪回，把数据恢复回来。原理是修改binlog的内容，拿回原库重放，需要确保binlog_format=row和binlog_row_imsge=Full. 如果是insert，将binlog event类型是write_rows

xjd0 2020-09-10

MySQL 如何修改root用户的密码

mysql> set password for 用户名@localhost = password;关闭正在运行的MySQL服务。打开DOS窗口，转到mysql\bin目录。--skip-grant-tables的意思是启动MySQL服务的时候跳过权限

greatboylc 2020-09-10

通过实例解析MySql CURRENT_TIMESTAMP函数

`text` varchar DEFAULT '' COMMENT '内容',那么如何设置一个具体的默认时间呢？

adsadadaddadasda 2020-09-08

mysql主从复制配置过程

配置完毕后，重启数据库： service mysqld restart. 通过phpmyadmin，添加新用户，并授予REPLICATION SLAVE权限。或者通过root用户登录到mysql，赋予权限：。其中repl是用户名，password是该用户的

疯狂老司机 2020-09-08

Golang操作MySql数据库的完整步骤记录

MySQL是业界常用的关系型数据库，在平时开发中会经常与MySql数据库打交道，所以在接下来将介绍怎么使用Go语言操作MySql数据库。Go语言中的database/sql包提供了保证SQL或类SQL数据库的泛用接口，并不提供具体的数据库驱动。我们常用的数

CoderToy 2020-11-16

Mysql 查询JSON结果的相关函数汇总

计算 JSON 深度，计算方式 {} [] 有一个符号即为一层，符号下有数据增加一层，复杂 JSON 算到最深的一次为止，官方文档说 null 值深度为 0，但是实际效果并非如此，列举几个例子。计算 JSON 最外层或者指定 path 的长度，标量的长度为

wangshuangbao 2020-11-13

Mysql 实现字段拼接的三个函数

给运营导出数据时，难免需要对字段进行拼接，如果 Mysql 可以完成的话，就可以少些很多代码。不过如果有字段值为 NULL，则结果为 NULL。上面这种方式如果想要使用分隔符分割，就需要每个字段中间插一个字符串，非常麻烦。concat_ws() 可以一次性

苏康申 2020-11-13

MySQL中使用binlog时格式该如何选择

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。另外就是,由于mysql现在发展比较快，很多的新功能加入，使mysql的复制遇到了不小的挑战,自然

要啥自行车一把梭 2020-11-12
hungzz

hungzz

W3CSchool教程
HTML 教程
CSS 教程
Bootstrap 教程
Javascript 教程
jQuery 教程
后端教程
C 教程
Java 教程
PHP 教程
Python 教程
Go 教程
移动开发
Android 教程
Swift 教程
Kotlin 教程
jQuery Mobile 教程
ionic 教程
关于我们
新闻动态
联系方式
招聘英才
安科实验室
帮助与反馈

安科网(Ancii)，中国第一极客网

Copyright © 2013 - 2019 Ancii.com

京ICP备18063983号 京公网安备11010802014868号