MySQL8.0：窗口函数

一、MySQL8.0窗口函数概述

1、什么是窗口函数

窗口函数是类似于可以返回聚合函数值的函数，例如SUM()，COUNT()，MAX()。但是窗口函数又与普通的聚合函数不一样，它不会对结果进行分组，使输出中的行数和输入中的行数相同。

窗口函数示例：

select sum() over(partition by __order by__) from table

这里有3点需要注意：

聚合功能：在上述例子中，我们使用了sum()，这里也可以使用count()、avg()等之类的计算功能
partition by：功能与group by子句类似，但是在窗口函数中要使用partition by
order by：order by与普通查询语句中的order by语句没什么不同，要注意使用的顺序

基本语法：

select 聚合函数() over(partition by __order by__) from table

over()：开窗函数，用于框选函数，如果没有任何参数，代表针对from后面的数据表中所有记录
partition by：分组操作，主要是针对over开窗函数中的数据进行分组
order by：如果有分组，只想针对组内的数据进行排序

2、窗口函数与分组函数的对比

若是想求解以性别分组，求解各个分组中的平均函数：

# 在分组的情况下，返回的是分组以及分组后聚合函数值
select gender, avg(score) from tb_student group by gender;

不能返回原数据表中的字段值

使用窗口函数可以在返回各个分组的聚合函数的同时返回原数据表中的各个数据值

# 窗口函数可以在获取聚合函数的同时保持原数据表的数据列
select * ,avg(score) over(partition by gender) as avg_score from tb_student;

使用聚合函数 + join可以达到相同的效果：

select 
	id,
	name,
	age,
	tb_student.gender,
	score_avg
from 
# 合并数据表，并需要给临时创建表起别名
(select gender,avg(score) score_avg from  tb_student group by gender ) as avg_socre
join tb_student 
    on avg_socre.gender = tb_student.gender;

3、窗口函数的优点

简单

窗口函数更易于使用。在上面示例中，与使用聚合函数然后合并效果对比，使用窗口函数使用更少sql语句就可以获取想要的结果

快速

这一点与上一点相关，使用窗口函数比使用替代方法要快的多。当你处理成千上万个千兆字节的数据时，这非常有用。

多功能性

最重要的是，窗口函数具有多种功能，比如，添加移动平均线，添加行号，组内排序等。

4、窗口函数执行顺序

优先级递减：

from 、 where 、 group by 、聚合函数、having、窗口函数、select、distinct、union、order by、offset、limit

二、窗口函数的使用

1、数据集的准备

各表的关系：

三张表通过部门id关联起来

部门表：

员工表：

采购表：

2、窗口函数的引入

窗口函数是对表中一组数据进行计算的函数，一组数据与当前行有关。

例如：计算每三天的销售总金额，就可以使用窗口函数，以当前行为基准，选前一行，后一行，三行一组如下图所示：

之所以称之为窗口函数，是因为好像有个固定大小的窗框划过数据集，滑动一次取一次，对窗口内的函数进行处理。

基本语法：

<window_function> OVER(...)

<window_function>：这里可以是我们之前已经学过的聚合函数，比如（COUNT(), SUM(), AVG() 等）。也可以是其他函数，比如ranking 排序函数，分析函数等
OVER(...)：窗口函数的窗框OVER(...)子句定义，窗口函数中很重要的一部分就是通过OVER(...)定义窗框的开窗方式和大小）

如果over子句中为空则代表对全部数据进行计算，然后再加上使用的聚合函数或其他函数

3、案例展示

查询员工表中，员工姓名、员工工资以及所有员工的平均薪资：

# 不使用窗口函数
select last_name,first_name,salary,e2.avg_salary 
from employee e1 
inner join (select avg(salary) avg_salary from employee) e2  ;

# 使用窗口函数
select 
	last_name,
	first_name,
	salary,
    avg(salary) over() as avg_salary 
from employee;

显示每个人的姓名，薪资以及公司的每个月工资支出情况:

# 显示每个人的姓名，薪资以及公司的每个月工资支出情况
select 
	first_name,
	last_name,
	salary ,
	sum(salary) over() as company_salary 
from employee;

统计采购表中的平均采购价格，并与明细一起显示（每件物品名称，价格）:

# 统计采购表中的平均采购价格，并与明细一起显示（每件物品名称，价格）
select 
	item,
	price,
	avg(price) over() as avg_price
from purchase;

对于over()的计算结果进一操作

例如：计算每一位员工与平均薪资的差距

# 计算每一位员工与平均薪资的差距
select last_name,
       first_name,
       salary,
       avg(salary) over() as avg_salary,
       salary - avg(salary) over() as diff
from employee;

采购价格占比总采购的百分比：

# 求采购价格占比总采购的百分比
select * from purchase;
select item,
       price,
       sum(price) over() as sum_price,
       price / sum(price) over() * 100
from purchase;

在一条SQL语句中可以使用多个窗口函数

在purchase表基础上，添加平均价格和采购总金额两列

select * ,sum(price) over(), avg(price) over() from purchase;

4、小结

可以使用<window_function> OVER() ,对全部查询结果进行聚合计算
窗口函数在聚合计算的同时还可以保留每行的其他数据的原始信息
在where条件执行之后，才会执行窗口函数，所以不能在where子句中使用窗口函数
可以在一条sql语句中同时创建多个窗口函数

三、排序函数

通过窗口函数实现排序函数，基本语法如下：

<ranking function> over(order by <order by columns>)

1、数据集的准备

游戏表（game）：

游戏采购表(game_purchase):

2、rank()函数

使用方法：

rank() over( order by ...)

rank() 会返回每一行的等级（序号）
order by 对行进行排序将数据进行升序或降序排列
rank() over (order by ...) 是一个函数，与order by配合返回序号

注意：rank() 有并列不连续

案例1：根据游戏的评分进行排名，要求有并列且排名序号不连续

# 根据查询游戏的评分进行排序
select 
	name,
	platform,
	genre, 
	rank() over(order by editor_rating) from game;

案例2：统计每个游戏的名字，分类，更新日期，更新日期序号

# 统计每个游戏的名字，分类，更新日期，更新日期序号
select
    name,
    genre,
    updated,
    rank() over(order by updated)
from game;

3、dense_rank()函数

rank()函数返回的序列，可能会出现不连续的情况。

如果想在有并列情况发生下仍然返回连续序号，可以使用dense_rank()函数

注意：返回的序号有并列且连续

案例3：根据游戏的评分进行排名，要求有并列且排名序号连续

# 根据查询游戏的评分进行排序,序号有并列并且连续
select
	name,
	platform,
	genre,
	dense_rank() over(order by editor_rating) from game;

4、row_number()函数

想要获取排序之后的序号，也可以使用row_number()来实现，通过名字就可以知道，意思是返回行号。

连续的且没有并列

# 通过row_number或取排序后的行号
select
    name,
    platform,
    editor_rating,
    row_number() over(order by editor_rating) as row_number_
from game;

5、rank()、dense_rank()、row_number()对比

通过对游戏评分的对比：

select
    name,
    editor_rating,
    rank() over(order by editor_rating) as `rank`,
    dense_rank() over (order by editor_rating) as `dense_rank`,
    row_number() over(order by editor_rating) as `row_number`
from game;