MySQL基础(六)——MySQL之开窗函数
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL基础(六)——MySQL之开窗函数，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5594字，纯文字阅读大概需要8分钟。
内容图文
前言
??上篇文章给大家介绍MySQL中常用的一些函数，其中主要包括数学函数、字符串函数、日期时间函数、分组合并函数、逻辑函数，其实还有开窗函数，只不过开窗函数是MySQL新提出来的，因此，我们单独用一篇文章将其详细介绍。首先，我们介绍开窗函数的理论知识。
一、开窗函数的相关理论知识
??其实，开窗函数是在MySQL8.0以后才新加的功能，因此，要想直接使用开窗函数，则mysql版本要8.0以上。其实开窗函数是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算，只不过它更具灵活性，它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。接下来，我们介绍开窗函数的相关语法；具体语法如下：
开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [< 细分窗口>])
??开窗函数的一个概念是当前行，当前行属于某个窗口，窗口由over关键字来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，开窗函数基于所有行进行计算；如果不为空，则有三个参数来设置窗口，具体参数的含义如下：
- partition by子句：按照指定字段进行分区，两个分区由边界分隔，开窗函数在不同的分区内分别 执行，在跨越分区边界时重新初始化。
- order by子句：按照指定字段进行排序，开窗函数将按照排序后的记录顺序进行编号。可以和 partition by子句配合使用，也可以单独使用。
- frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。
??对于滑动窗口的范围指定，通常使用 between frame_start and frame_end 语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：
- current row 边界是当前行，一般和其他范围关键字一起使用
- unbounded preceding 边界是分区中的第一行
- unbounded following 边界是分区中的最后一行
- expr preceding 边界是当前行减去expr的值
- expr following 边界是当前行加上expr的值
??比如，下面都是合法的范围：
- rows between 1 preceding and 1 following 窗口范围是当前行、前一行、后一行一共三行记录。
- rows unbounded preceding 窗口范围是当前行到分区中的最后一行。
- rows between unbounded preceding and unbounded following 窗口范围是当前分区中所有行， 等同于不写
??其实，这些滑动窗口的范围用图表示如下：
??其实，我们不难发现，开窗函数本质上也是一种普通函数，但是它和我们一般的聚合函数还是有区别的：
- 聚合函数是将多条记录聚合为一条；而开窗函数是每条记录都会执行，有几条记录执行完还是几条。
- 聚合函数也可以用于开窗函数中。
??接下来，我们继续介绍序号函数。也是窗口函数。
1、row_number()显示分区中不重复不间断的序号
2、dense_rank()显示分区中重复不间断的序号
3、rank()显示分区中重复间断的序号
二、开窗函数案例
??我们前面开篇就说过，要使用开窗函数，我们本地的MySQL版本确保要是8.0以上。本来看到这里的读者应该都会查看自己版本的，但是为了确保让读者可以练习接下来的案例，还是先给大家介绍MySQL版本查询以及下载网址。我们在查看自己MySQL版本的时候首先打开黑屏终端，具体用菜单键+R快捷键打开黑屏终端。然后输入以下命令：
mysql -uroot -p
??然后输入密码即可看到自己的版本，具体如下：
??如果你的mysql是其他版本建议卸载后安装，关于卸载的文章有很多，大家可以去搜一下，然后再直接安装即可。由于这方面的文章很多，这里就不做介绍了。安装好之后，就可以直接练习开窗函数了。这里需要说明的是：我们的用到的数据库和数据均是前面文章提到的，大家要是练习实战的可以去前面的文章去创建数据库和数据表。
- 1、计算所有员工的平均工资。
-- 我们先用普通函数进行实现：
select avg(sal)
from emp;
??执行的结果如下：
-- 当然，我们也可以用开窗函数实现
select *,avg(sal) over() avg_sal
from emp;
??执行的结果如下：
??从结果可以看出：我们的窗口函数显示的是每一行，而普通的聚合函数则是只有一个结果。
- 2、查询各部门按入职日期计算累计工资总和
select deptno, avg(sal) avg_sal
from emp
group by deptno;
??执行的结果如下：
select *,avg(sal) over(partition by deptno) avg_sal
from emp;
??执行的结果如下：
??从结果可以看出：我们的窗口函数显示的是每一行，并且按照组进行输出，组内的每一行平均薪资是一样的；而我们一般的聚合函数按部门输出，每一个部门输出的是一行。那么看到这里的读者不由得有一个问题：既然开窗函数都可以通过我们的一般聚合函数实现，那么为什么还要有开窗函数呢？其实，我们在前面介绍理论的时候也介绍过聚合函数和开窗函数的区别。下面的一个案例，就只能用开窗函数实现了，其它的函数不可以实现的；
- 3、各部门按入职日期计算累计工资总和
select *, sum(sal) over(partition by deptno order by hiredate) sum_sal
from emp;
??执行的结果如下：
??我们从这个题目就可以看出我们的开窗函数的独到之处。接下来，我们在看个案例；
- 4、各部门按入职日期计算当前行的前一行和后一行的平均工资
select *,avg(sal) over(partition by deptno order by hiredate rows between 1 preceding and 1 following) avg_sal
from emp;
??执行的结果如下：
??一般来说，序号函数一般和开窗函数一起使用，效果很好，就是为了解决TOPN的问题；
- 5、查询所有员工按入职日期显示排名
select *, row_number() over(order by hiredate) 排名
from emp;
??执行的结果如下：
- 6、各部门员工按照基本工资显示排名
??这里我们先按正常思维显示：
select *, row_number() over(partititon by deptno order by sal desc) 排名
from emp;
??执行的结果如下：
??但是，这里出现一个问题：就是基本工资一样的按理说要排名应该是一样的。具体实现如下：
*select *,
row_number() over(partition by deptno order by sal desc) 排名1,
dense_rank() over(partition by deptno order by sal desc) 排名2,
rank() over(partition by deptno order by sal desc) 排名3
from emp;
??执行的结果如下：
总结
??上篇文章给大家介绍MySQL中常用的一些函数，其中主要包括数学函数、字符串函数、日期时间函数、分组合并函数、逻辑函数，其实还有开窗函数，只不过开窗函数是MySQL新提出来的，因此，我们单独用一篇文章将其做了详细介绍。主要包括开窗函数和序号函数。另外，还通过一些案例进行进一步说明其用法及其作用。因此，mysql是很重要的一个技能，几乎计算机中的每个岗位都需要一个mysq技能，因此，需要我们特别的掌握。生命不息，奋斗不止，我们每天努力，好好学习，不断提高自己的能力，相信自己一定会学有所获。加油！！！
内容总结
以上是互联网集市为您收集整理的MySQL基础(六)——MySQL之开窗函数全部内容，希望文章能够帮你解决MySQL基础(六)——MySQL之开窗函数所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。