===本节主要讲了以下内容 一、窗口函数，简要的说就是在满足某条件的记录集合上运行的特殊函数。 后面的几个例子多看看，是窗口函数的应用。

第三部分MySQL高级应用

窗口函数，索引，视图

第一节 窗口函数

MySQL从8.0开始支持窗口函数，有的也叫分析函数（处理相对复杂的报表统计分析场

景），这个功能在大多商业数据库和部分开源数据库中早已支持。

窗口函数：窗口、函数（应用在窗口内的函数）-----窗口类似于窗户，限定一个空间范围

窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，窗口大小都是固定的，这种属于静态窗口；不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

其中，over是关键字，用来指定函数执行的窗口范围，包含三个分析子句：分组(partition by)子句，排序(order by)子句，窗口(rows)子句，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下语法来设置窗口：

over这个窗口函数还是很常见的，后面的括号里可以填写三类分析子句，注意这里的分组不是group by哦。

A: 需要被加工的字段名称

B: 分组的字段名称

C: 排序的字段名称

D: 计算的行数范围

这上面讲解了一下rows这个语句的计算方式，其实看一遍就明白了。

1.2 窗口函数的应用

一般，我们可以把窗口函数分为两种：

专有窗口函数：rank(), dense_rank(), row_number()

这三个之前在刷题时遇到过，三个都是排名用的，不同之处在于相同数值的排序，rank是相同的一样排名，后面一个会跳着排；dense——rank也是相同的一样排名，但后面一个会连着排，row_number就是相同的也不同排名。

聚合类窗口函数：

普通场景下，聚合函数往往和group by一起使用，但是窗口环境下，聚合函数也可以应用进来，那么此时它们就被称之为聚合类窗口函数，属于窗口函数的一种

sum()，count()，avg()，max()，min()

窗口函数（专有窗口函数+聚合类窗口函数）和普通场景下的聚合函数也很容易混淆，二者区别如下：

1 普通场景下的聚合函数是将多条记录聚合为一条（多到一）；窗口函数是每条记录都会执行，有几条记录执行完还是几条（多到多）。

这点还是非常明显的，后面也出现过

2 分组(partition by)：记录按照字段进行分组，窗口函数在不同的分组上分别执行。

3 排序(order by)：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号，可以和partition子句配合使用，也可以单独使用。如果没有partition子句，数据范围则是整个表的数据行。

4 窗口(rows)：就是进行函数分析时要处理的数据范围，属于当前分区的一个子集，通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的移动平均支付金额，则可以设置rows子句来创建滑动窗口(rows)。

下面开始来实例

现有2018～2020某电商平台订单信息表user_trade

依据上面的信息，在数据库中建立表

navicat(MySQL的一个图形化工具)导入数据：D:/course/mysql/资料/窗口函数数据/user_trade.xlsx

------累计计算函数应用、排序函数应用、偏移分析函数应用

1.2.1累计计算函数

需求1 查询出2019年每月的支付总额和当年累积支付总额

这里计算每个月的总额，首先上述数据里面是没有月度数据的，其次如何按照月来分别计算每个月的总额也是一个问题。select中的两个值应该怎么填？

第一步：过滤出2019年的数据

第二步：将上面数据按月进行group by分组，统计每个月份的支付总额

第三步：在上述基础上，使用窗口函数实现需求

==首先问月的销售总额，不用想太多，直接用sum求和计算就可以；在此同时问年的累计总和就需要用窗口函数，滑动月份窗口来计算（4.23

以下是计算结果，代码最好自己手写一遍

这里需要先计算第二步，在第二步基础上往下做，另外pay_time是data类型，可以使用year，month分别提取出年和月。

需求2  查询出2018-2019年每月的支付总额和当年累积支付总额

这题和上题大体上是相同的，但需要注意的是，1：这里除了按月分组之外，还需要按年分组，这里使用了over中的第一个参数partition by。1：还有就是这里不再是一个年份，而是一个年份区间，where不再使用等于，而是in。

在子查询里面order by也是可以有两个参数的哦

==这里和上题只有一个区别，就是在年份这里有两个值，需要进行分组计算，所以就引出了partition这个参数。（4.23

移动平均：avg()over()

需求3 查询出2019年每个月的近三月移动平均支付金额

这里说要每个月前3个月的平均支付，其实刚看到是有些蒙的，但是不碍事，我们先写子查询，子查询我们不考虑移动平均三个月什么乱七八糟的，我们只要两列，月，每月支付累计；这样是不是就简单多了。

然后我们在写select，移动平均使用avg（）over（），这里还是需要order by来对月份进行排名，然后再利用over当中的第三个参数rows来进行前3个月的判定。

前面对rows函数也讲过使用方式，这里需要写2 preceding and current row，即前两行加其自身，另外，在order by和rows参数之间竟然是没有逗号的。

==这里的移动平均avg其实是次要的，它主要想让我们掌握窗口函数的第三个参数rows，不管出现什么“近多久”，都得想到这个窗口函数和rows参数（4.23

最大/小值：max()/min() over()

需求4：查询出每四个月的最大月总支付金额

这里的其他部分我都写对了，但是这个substr是给我整懵了，没学过这个函数啊

==用date_format一样可以解决（4.23

1.2.2 排序函数

row_number() over(......)

rank() over(......)

dense_rank() over(......)

需求5 2020年1月，购买商品品类数的用户排名

==遇到排名问题，首先把排名需要的数据列出来，而排名本身就是一个窗口函数，并且有三类，三类效果不同（4.23

首先第一点，count进行计数时，千万不能忘记使用distinct；第二，排名这类专有的窗口函数的排名参数是在over（）中的order by里面给出的，不是在rank里面；第三这里推测这个substring是可以直接调出具体日期的，不过谁能告诉我，上一题里没有ing这里又有。

需求6 ：查询出将2020年2月的支付用户，按照支付金额分成5组后的结果

==这里没考虑支付数为0的情况，然后分组问题是ntile函数，并且也需要开一个窗口来按支付金额排序。（4.23

这一题的问题在于如何分成固定的组数，这里给出了一个新函数，ntile，它可以分为固定组数；另外上一题的substr果然写错了，应该是不带ing的。

需求7 查询出2020年支付金额排名前30%的所有用户

这一题其实设计的很巧妙，写完看答案后叹为观止，这题的主要难度在于这个30%需要怎么表示，他这里用了上一题使用的分组函数，讲排名分成10组，取前3组，就实现了30%这个说法。

ntile（）over（   排序  desc）level是这个函数的使用方法

==这是分组函数的一个巧妙应用。（4.23

1.2.3偏移分析函数

lag(...) over(......)往前查找

lead(...) over(......)往后查找

需求8 查询出King和West的时间偏移(前N行)

其实这个耐下心去看还是挺好理解的，虽然这里的代码写错了两个，但是不影响理解。

首先明确lag（）over（）是用来向上求偏置的，这是什么意思呢，其实就是根据一个用户干了个蠢事，找他上次干这个蠢事的时间。

lag（）这里填写的参数就是时间，over（）中的参数和前面一样，需要有partition来对用户进行分组，order by来对时间进行排序。

lag其实是有第二个参数的，就是次数，如果写2，就是问这个用户上上次干这个蠢事的时间，当然不填的话，默认是1

需求9 King和West的时间偏移(后N行)

这个与前面的计算完全一样，lag是往前，lead是往后，只管及这个就可以了。

这个函数其实还是挺常用的，看下面几个例子就知道了。

知识点总结：

Lag和Lead函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为 独立的列。

在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。

exp_str是字段名称。 offset是偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，则offset 为3，则表示我 们所要找的数据行就是表中的第2行(即5-3=2)。offset默认值为1。defval默认值，当两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时，lag()函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL，那么 在数学运算中，总要给一个默认值才不会出错。

需求10：查询出支付时间间隔超过100天的用户数

首先还是那句话，count千万别忘记用distinct，然后就是这个间隔100天应该怎么表示，这里使用了一个函数datediff（），里面的参数是偏置列和支付时间列，可能用这个函数可以计算两个时刻之间的距离

*需求11：查询出每年支付时间间隔最长的用户

这题还真是一层套一层，这里记住需要判断lag is not null，与上题不同，这里需要判断最大的间隔时间，别忘了where是不能包含聚合函数的，那么就不能同上题去判断，而是用rank（）函数来逆排序，找出最大值。

另外此题另一个难点在按年排序上。

第2节MySQL索引

2.1什么是索引

索引可以提升查询速度，会影响where条件查询（where xxx=''），order by（order byyyy）排序

索引是针对字段的，需要添加到字段上

索引在大量数据场景下效果明显

2.2 常见索引分类

从索引的存储结构划分：B Tree索引、Hash索引、fulltext全文索引、R Tree索引（了解）

从应用层次划分：主键索引、唯一索引、普通索引、复合索引

从索引的键值（字段）类型划分：主键索引、辅助索引（二级索引）

从索引数据和内容数据逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

索引的缺点也很明显，当对表中的数据进行增加，修改，删除的时候，索引要同时进行维护，数据量越大维护时间越长，所以还是不看了。

第3节MySQL视图

3.1 什么是视图

1. 视图是一种虚拟表。

2. 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。

3. 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.

4. 视图向用户提供基表数据的另一种表现形式

3.2 视图的作用

权限控制时可以使用

    比如,某几个列可以运行用户查询,其他列不允许,可以开通视图 查询特定的列, 起到权限控制的 作用

简化复杂的多表查询

    视图本身就是一条查询SQL,我们可以将一次复杂的查询 构建成一张视图, 用户只要查询视图就可以获取想要得到的信息(不需要再编写复杂的SQL)

    视图主要就是为了简化多表的查询

3.3 视图的使用

3.3.1 创建视图

1）语法格式

2）创建一张视图

3）查询视图 ,当做一张只读的表操作就可以