MySQL之窗口函数

开发工具：

• mysql-8.0
• DataGrip

数据源：chapter11.csv

shopname,sales,sale_date
A,1,2020/1/1
B,3,2020/1/1
C,5,2020/1/1
A,7,2020/1/2
B,9,2020/1/2
C,2,2020/1/2
A,4,2020/1/3
B,6,2020/1/3
C,8,2020/1/3

（1）什么是窗口函数

窗口函数与数据分组类似，但是比数据分组的功能丰富。数据分组是将组内多个数据聚合成一个值，而窗口函数除了可以将组内数据聚合成一个值，还可以保留原始的每条数据。

（2）聚合函数+over()函数

现在我们想看一下每个店铺每天的销量与这张表中全部销量的平均值之间的情况，使用子查询实现：在查询结果中插入一列固定值，这里全部销量的平均值其实就是一个固定值，只不过这个固定值是一个查询出来的固定值，而不是输入的一个常数。具体实现代码如下：

-- 子查询
select shopname,
       sales,
       sale_date,
       (select
               avg(sales)
       from
            chapter11) as avg_sales
from
     demo.chapter11;

运行结果：

上面的代码虽然实现需求，但是相对繁琐，我们可以使用窗口函数中的over()函数轻松实现上面的需求，只需要在聚合函数后面加一个over()函数即可，具体实现代码如下：

-- 窗口函数
select shopname,
       sales,
       sale_date,
       avg(sales) over() avg_sales 
from demo.chapter11;

over()函数的作用是将聚合结果显示在每条单独的记录中。

（3）partition by子句

现在我们想求出每个店铺每天的销量和表中自己店铺的所有销量的平均值进行比较，其实就是按照店铺进行分组，然后在组内进行平均值聚合运算。使用子查询实现：一部分是每个店铺每天的销量，另一部分是每个店铺所有销量的平均值。将两个表连接即可得到我们的需求

-- 每个店铺每天的销量和表中自己店铺的所有销量的平均值进行比较
select chapter11.shopname,
       sales,
       sale_date,
       avg_table.avg_sales
from demo.chapter11
left join
(select shopname,
        avg(sales) as avg_sales
from demo.chapter11
group by
         shopname) avg_table
on chapter11.shopname = avg_table.shopname;

运行结果：

上面的代码虽然运行出结果了，但是依旧很烦琐，接下来使用窗口函数实现。在over()函数中使用partition by来指明要按照哪列进行分组，然后聚合函数就会在分好的组内进行聚合运算，此处按照shopname列进行分组，具体实现代码如下：

select shopname,
       sales,
       sale_date,
       avg(sales) over(partition by shopname) as avg_sales
from demo.chapter11;

（4）order by子句

们要获取店铺A在2020年1月2日的销量与平均值之间的对比情况，这里的平均值就是通过对店铺A在2020年1月2日之前所有的销量求平均值得到的，不包括2020年1月2日以后的。我们把这种聚合方式称为顺序聚合，使用的是order by，利用order by对时间进行排序，具体实现代码如下：

-- order by子句
select shopname,
       sales,
       sale_date,
       avg(sales) over(partition by shopname order by sale_date) as avg_sales
from demo.chapter11;

运行结果：

店铺A在2020年1月1日的平均值就是它本身，在1月2日的平均值是1月1日与1月2日两天的平均值，在1月3日的平均值是1月1日、1月2日、1月3日三天的平均值。

（5）序列函数

序列函数是将数据整理成一个有序的序列，我们可以在这个序列中挑选我们想要的序列对应的数据。

（5.1）ntile()函数

ntile()函数主要用于对整张表的数据进行切片分组，默认是在对表不进行任何操作之前进行切片分组，比如，现在整张表有9行数据，要分成3组，那么就是第1～3行为一组、第4～6行为一组、第7～9行为一组。我们将chapter11表切分成3组，具体实现代码如下：

-- 按行数分组
select shopname,
       sales,
       sale_date,
       ntile(3) over() as cut_group
from demo.chapter11;

运行结果：

前面讲的聚合函数可以针对全表进行聚合，也可以针对组内进行聚合，这里的切片分组也是一样的，也可以针对组内进行切片分组。比如，按照shopname列进行切片分组，具体实现代码如下：

-- 按shopname分组
select shopname,
       sales,
       sale_date,
       ntile(3) over(partition by shopname) as cut_group
from demo.chapter11;

运行结果：

上面是按照表中的默认顺序依次进行切片分组的，我们也可以按照指定顺序进行切片分组，比如，在各个组内按照销量进行升序排列以后再进行切片分组，具体实现代码如下：

select shopname,
       sales,
       sale_date,
       ntile(3) over(partition by shopname order by sales) as cut_group
from demo.chapter11;

运行结果：

（5.2）row_number()和rank()函数

row_number()函数就是用来生成每条记录对应的行数的，即第几行。行数是按照数据存储的顺序进行生成的，且从1开始。因为行数是按照数据存储顺序生成的，所以一般row_number()函数与order by结合使用，此时的行数就表示排序，需要注意的是，row_number()函数的结果中不会出现重复值，即不会出现重复的行数，如果有两个相同的值，会按照表中存储的顺序来生成行数。

如果我们要获取全表中销量的升序排列结果，则可以使用row_number()函数，具体实现代码如下：

select shopname,
       sales,
       sale_date,
       row_number() over(order by sales) as rank_num
from demo.chapter11;

我们就可以得到全表中每个店铺每天的销量在表中的一个升序排列结果，具体运行结果如下表所示。

有时候，我们的需求可能是获取各自组内的一个排名结果，这个时候就需要用到partition by，具体实现代码如下：

select shopname,
       sales,
       sale_date,
       row_number() over(partition by shopname order by sales) as rank_num
from demo.chapter11;

就会得到每个店铺在不同时间的销量对应的排名，具体运行结果如下表所示。

我们可以根据需要对上面的rank_num列进行筛选，比如，让rank_num = 1，即获取每个店铺销量最差的一天。

注意row_number()要和rank()区别：row_number()显示的是行号，排序相同时不会重复，会根据顺序排序。rank()排序相同时会重复，总数不变，即会出现1、1、3这样的排序结果

RANK() 排序相同时会重复，总数不会变，例如1224

DENSE_RANK() 排序相同时会重复，总数会减少，例如 1223

ROW_NUMBER() 会根据顺序去计算，例如 1234

row_number()举例

select 
       Name,
       Salary,
       row_number() over (partition by DepartmentId order by Salary desc ) as rank_num 
from 
     test.Employee;

rank()举例

select 
       Name,
       Salary,
       rank() over (partition by DepartmentId order by Salary desc) as rank_num 
from 
     test.Employee;

如果我们要求出salary最大值的Name（包含重复值）就需要用rank()，row_number()就无法实现

（5.3）lag()和lead()函数

lag()函数是让数据向后移动，而lead()函数是让数据向前移动。

如果我们现在想获取每个店铺本次销量与它前一次销量之差，只需要把该店铺的销量数据全部向后移动1行，这样本次销量数据就与前一次销量数据处于同一行，然后就可以直接做差进行比较了，具体实现代码如下：

select shopname,
       sales,
       sale_date,
       lag(sales,1) over(partition by shopname order by sale_date) as lag_value
from demo.chapter11;

在上面的代码中，我们先对全表数据按照shopname列进行分组，然后在组内按照销售日期进行排序，因为我们是将每个店铺的本次销量与它的前一次销量进行比较，所以需要再将分组排序后的数据整体向后移动1行。运行上面的代码，具体运行结果如下表所示。

lag(sales,1)表示将sales列向后移动1行，当然我们也可以选择将其他列向后移动n行。

如果我们想获取每个店铺本次销量与它后一次销量之差，只需要把该店铺的销量数据全部向前移动1行即可，这样本次销量数据就与后一次销量数据处于同一行，然后就可以直接做差进行比较了，在代码实现上，只需要把上面代码中的lag换成lead即可，具体如下：

select shopname,
       sales,
       sale_date,
       lead(sales,1) over(partition by shopname order by sale_date) as lead_value
from demo.chapter11;

运行上面的代码，具体运行结果如下表所示。

（5.4）first_value()和last_value()函数

first_value和last_value顾名思义，就是第一个值和最后一个值，但又不是完全意义上的第一个或最后一个，而是截至当前行的第一个或最后一个。类似于前面讲过的顺序聚合。

比如，我们现在想获取每个店铺的最早销售日期和截至当前最后一次销售日期，通过这两个指标来反映店铺的营业时间，可以直接借助first_value()和last_value()函数，具体实现代码如下：

-- first_value()和last_value()函数
select shopname,
       sales,
       sale_date,
       first_value(sale_date) over(partition by shopname order by sale_date) as first_date,
       last_value(sale_date) over(partition by shopname order by sale_date) as last_date
from demo.chapter11;

在上面的代码中，我们先对店铺进行分组，然后在组内根据销售日期进行排序，最后通过first_value()和last_value()函数来获取每个店铺的最早销售日期和截至当前最后一次销售日期。运行上面的代码，具体运行结果如下表所示。