MySQL数据库基础篇SQL窗口函数示例解析教程

本文简介

前段时间，黄同学写了一篇《MySQL窗口实战》文章（文章如下），但是里面大多数是以实战练习为主，没有做详细的解释。

传送门：MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

于是，私信了月牙美女，看看她能否写一篇《窗口函数基础篇》，正好和之前那篇文章配套。这不，很快她就写好了，今天就给大家做一个分享，旨在和大家交流学习哦！

下面是月牙的玩笑简介，附带大图一张哦。

正文介绍

窗口函数，也被称为 “开窗函数”，MySQL8.0以后，就可以使用这些函数了。

我们在力扣刷题的时候，不难发现，比较困难的题目经常会涉及到窗口函数的应用，可以说窗口函数，是检验我们的SQL水平是否到达熟练水平的一个标尺。

窗口函数的格式为： 聚合函数+over()

窗口是描述over()括号内划定的内容，这个内容就是窗口函数的作用域，即操作的数据都在over()的范围内。

对于窗口函数，我个人的理解是给源数据开一扇可以滑动的窗口，在窗口移动的时候可以对其中的数据进行附加计算，如移动平均、分组排序等，窗口可以是一行多行甚至是所有行。

窗口函数，还可以对多组数据进行同步排序、聚合等运算，针对group by子句或where处理后的结果进行操作，只能写入select子句里。

灵魂画手上线，用Excel简单做了个简易版的窗口函数的演示图：

聚合函数 + over()

基本语法：

sum/avg(被加工的字段名) over(partition by 分组的字段名 order by 排序的字段名 rows between … and …)

含义： 表示用partition by分组后针对每个组别进行求和或者求均值。

--包括本行以内和前3行：rows between 6 preceding and current row
--包括本行以内和后3行：rows between current row and 3 following
--包括本行和之前所有的行：rows between unbounded preceding and current row
--包括本行和之后所有的行：rows between current row and unbounded following
--从前3行到下1行（总共包含5行数据）：rows between 3 preceding and 1 following

还有一些其它的聚合函数，例如max、min、count，它们的语法结构都类似。

排序函数 + over()

row_number()、rank()、dense_rank()这三个函数，都是对select查询到的结果进行排序，我们来看看这三者的区别。

row_number()： 为不重复的连续排序，从1开始，为查询到的数据依次生成不重复的序号进行排序

基本语法――row_number() over(order by 需要排序的字段asc/desc)；

rank()： 为跳跃排序，结果相同的两个数据并列，为下一个数据空出所占的名次，即相同排名会占位

基本语法――rank() over(order by 需要排序的字段 asc/desc)；

dense_rank()： 为有重复的连续排序，结果相同的两个数据并列，不为下一个数据空出所占的名次，即相同排名不占位

基本语法――dense_rank() over(order by 需要排序的字段 asc/desc)；

我们用一张图来表示这三者间的关系：

ntile()函数 + over()

基本语法： ntile(n) over(partition by…order by…)其中n表示被切分的段数。

ntile(n)用于将分组数据平均切分成n块，如果切分的每组数量不均等，则第一组分得的数据更多。

ntile()函数通常用于比如求年级前10%成绩的学生，则n取值为10，用where筛选出第一组的数据。

偏移函数 + over()

基本语法1：前N行：lead(str, n, default) over(partition by …order by …)

基本语法2：后N行：lag(str, n, default) over(partition by …order by …)

str表示字段名，n表示前/后n行数据，默认值为1，default表示如果取值范围已经超过整个表的返回值，可以不填，不填默认返回N/A。

偏移函数，用于取出同一字段的前N行数据或后N行数据，作为单独的列，这里需要特别注意一下的是，lead代表前N行，lag代表后N行。