目录：

1、什么是开窗函数？

（1）开窗函数的定义

>>> 开窗函数和group by有什么区别

>>>开窗函数和普通聚合函数之间又有什么关联

>>> 开窗函数的书写语句

（2）开窗函数的分类

2、常见开窗函数的应用案例

序号函数：row_number() / rank() / dense_rank()

前后函数：lag() / lead()

头尾函数：first_value() / last_value()

正文：

1、什么是开窗函数？

（1）开窗函数的定义

开窗函数是在满足某种条件的记录集合上执行的特殊函数，对于每条记录在此窗口内执行函数

>>> 开窗函数和group by有什么区别呢？

需要注意，开窗函数的本质还是聚合运算，对比起group by，它更具灵活性，是对表中字段的值进行聚合运算，它作用于表中数据的每一行，使窗口内每一行使用与该行相关的行进行计算并返回计算结果，而group by通常是对整个表进行分组聚合运算。

>>>开窗函数和普通聚合函数之间又有什么关联呢？

• 聚合函数是将多条记录聚合为一条，而开窗函数是每条记录都会执行，有几条记录执行完还是几条，返回多条值

• 聚合函数也可以用于开窗函数中
  

>>> 开窗函数的书写语句

开窗函数名([

开窗函数中一个重要的概念是当前行，若当前行属于某个窗口，窗口则由 over关键字用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where 条件的所有行。

开窗函数基于所有行进行计算；over后面如果不为空，则有三个参数共同来设置窗口：

• partition by子句：按指定字段进行分区，开窗函数在不同的分区内分别执行

• order by子句：按指定字段进行排序，开窗函数将按排序后的记录顺序进行编号

  可以和 partition by子句配合使用，也可以单独使用

• range|rows子句：用于给当前分区窗口指定显示范围，通常用来作为滑动窗口使用

# 当over没有指定分区、排序和滑动窗口范围(仅针对滑动窗口函数)时，表中所有记录为一个区，默认计算当前分区内的第一行对应值到当前行排序字段对应值范围内的记录

# 滑动窗口范围仅针对滑动窗口函数，滑动窗口函数（基于行row或者值range/基于日期），静态窗口函数适用滑动窗口范围不会报错，但是也不会有效果

# 区别：基于行和基于值计算

range和rows的区别：

range是逻辑窗口，根据order by子句排序后，当前行对应的值计算。

rows是物理窗口，根据order by子句排序后，当前行对应的序号计算（与当前行的值无关，只与排序后的序号相关）。

补充>>滑动窗口的范围指定：基于行和基于值

基于行（row）： 通常使用 rows between frame_start and frame_end 语法来表示行范围

基于值（range）： 和基于行类似，通常使用 range between frame_start and frame_end 语法来表示值范围

（不管是基于行还是基于值得计算，都是以order by后的字段作为参照依据）

（2）开窗函数的分类

按照函数功能不同，MySQL支持的开窗函数有如下几类：

序号函数：row_number() / rank() / dense_rank()

分布函数：percent_rank() / cume_dist()

前后函数：lag() / lead()

头尾函数：first_value() / last_value()

其他函数：nth_value() / ntile()

2、开窗函数的应用场景

*适用场景：计算占比、同比、环比、累计求和，得到各部门工资排名前N名员工列表，替代子查询等

*实际应用案例：

（1）聚合函数 sum()/avg()/max()/min()/count()

（原表如下图）

-- 查询所有订单总金额

select sum(amount) as 订单总金额 from order_tab;

select *,sum(amount) over() as 订单总金额 from order_tab;

# 当over没有指定分区、排序和滑动窗口范围(仅针对滑动窗口函数)时，表中所有记录为一个区，默认计算当前分区内的第一行对应值到当前行排序字段对应值范围内的记录

# 滑动窗口范围仅针对滑动窗口函数，滑动窗口函数（基于行row或者值range/基于日期），静态窗口函数适用滑动窗口范围不会报错，但是也不会有效果

# 区别：基于行（物理窗口）和基于值(逻辑)计算,不管是基于行还是基于值得计算，都是以order by后的字段作为参照依据

-- 查询所有订单总金额

select*,sum(amount) over(partition by user_no) as 订单总金额 from order_tab;

-- 查询每个用户的订单总金额

select user_no,sum(amount) as 订单总金额
from order_tab
group by user_no;

-- 按下单时间顺序，查询每个用户的累积订单金额

select *,sum(amount) over(partition by user_no order by create_date) as 累积订单金额 from order_tab;

#当over中指定分区和排序，但是没有指定滑动窗口范围时，默认计算当前分区内的第一行排序字段对应值到当前行排序字段对应值范围内的记录

-- 按订单编号顺序，查询每个用户的累积订单金额

select *,sum(amount) over(partition by user_no order by order_id) as 累积订单金额 from order_tab;

#当over中指定了排序，但是没有指定滑动窗口范围时，默认是基于值计算，计算当前分区内的第一行排序字段对应值到当前行排序字段对应值范围内的记录

-- 查询每个用户按单号顺序，前一笔到后一笔订单的平均订单金额

select *,avg(amount) over(partition by user_no order by order_id range between 1 preceding and 1 following) as 平均订单金额 from order_tab;

#当over中指定滑动窗口范围时，计算指定的范围内的记录

-- 查询每个用户按下单时间顺序，前一笔到后一笔订单的平均订单金额

select *,avg(amount) over(partition by user_no order by create_date range between interval 1 day preceding and interval 1 day following) as 平均订单金额 from order_tab;

#当over中指定滑动窗口范围时，计算指定的范围内的记录

（2）序号函数

row number()、rank()、dense_rank()

-- 查询每个用户订单金额最高的前三个订单

【步骤一】给每个用户的订单金额增加序列号

>>>.SQL写法1：

select *, 
row_number() over (partition by user_no order by amount rows between 1 preceding and 1 following) as 订单金额排名1,
rank() over (partition by user_no order by amount rows between 1 preceding and 1 following) as 订单金额排名2 , 
dense_rank() over (partition by user_no order by amount rows between 1 preceding and 1 following) as 订单金额排名3 
from order_tab;

>>>>SQL写法2：

select 
*,
    row_number() over(partition by user_no order by amount desc ) as 订单金额排名1,
    rank() over(partition by user_no order by amount desc) as 订单金额排名2,
    dense_rank() over(partition by user_no order by amount) as 订单金额排名3
from order_tab;

(🤔温馨提示)

# row_number(),rank(),dense_rank()s都属于静态滑动窗口，第三个子句desc后面加上限制范围不会生效，但是不会报错；

# rank(并列间断)和dense_rank(并列且不间断),高考等成绩排名多用dense_rank(并列且不间断)，记录用户登录时间3个函数都适用，活动发放优惠券适合rank(并列间断)控制成本

【步骤二】筛选出订单排名前三名的订单

select *, 
dense_rank() over (partition by user_no order by amount desc rows between 1 preceding and 1 following) as 订单金额排名2 
from order_tab
where dense_rank() over (partition by user_no order by amount desc rows between 1 preceding and 1 following);

# ❌报错！where子句中不能使用聚合函数

# 如何处理？增加一个表

select *
from 
(select 
*,
dense_rank() over(partition by user_no order by amount desc) as 订单金额排名
from order_tab) as t
where 订单金额排名<=3;

（3）前后函数 （静态窗口函数）

# lag()、lead()

# 适用场景：计算同环比

# 为什么SQL单表不能计算同比？因为SQL计算中，行与行之间无法进行计算 ---🤔如果用自连接呢？创建所有字段的虚拟结果集，数据计算量过大

-- 查询每个用户上一个订单距离当前订单的间隔天数

# 步骤：用lag函数计算出上一个订单日期得到新字段last_date，再对这两个字段进行减法运算得到间隔天数

select *, 
datediff(create_date,last_date) as diff
from(
select 
*,
    lag(create_date,1) over(partition by user_no order by create_date) as last_date
from order_tab) as T表;

-- 查询每个用户当前订单距离下一个订单的间隔天数

# 步骤：用lead函数计算出下一个订单日期得到新字段lead_date，再对这两个字段进行减法运算得到间隔天数
select *, 
datediff(create_date,lead_date) as diff
from(
select 
*,
    lead(create_date,1) over(partition by user_no order by create_date) as lead_date
from order_tab) as T表;

（4） 头尾函数

# first_value() 、last_value()

# min()、max()

-- 查询每个用户按照日期排序第一个订单和最后一个订单的订单金额

select 
*,
    first_value(amount) over(partition by user_no order by create_date rows between unbounded preceding and unbounded following) as first_amount,
    last_value(amount) over(partition by user_no order by create_date rows between unbounded preceding and unbounded following) as last_amount
from order_tab;

select 
*,
    min(amount) over(partition by user_no) as first_amount,
    max(amount) over(partition by user_no) as last_amount
from order_tab;

#当over中指定分区，但是没有指定排序和滑动窗口范围时，默认计算当前分区内的所有记录