Mysql窗口函数学习

2026/7/2 4:37:46

1 窗口函数

1.1 什么是窗口函数

窗口函数，也叫分析函数，用于处理相对复杂的报表统计分析场景

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

窗口函数基本用法

函数名([expr]) over子句

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

函数名（[expr]） over(partition by <要分组的列> order by <要排序的列> rows between <数据范围>)

知识点总结
sum(.. A..) over(partition by ...B... order by.. C... rows between...D1.. and ...D2...)
avg(...A...) over(partition by..B.. order by...C... rows between ...D1... and ..D2...)
A:需要被加工的字段名称B:分组的字段名称C:排序的字段名称D:计算的行数范围

rows between 2 preceding and current row#取当前行和前面两行  rows between unbounded preceding and current row#包括本行和之前所有的行。  rows between current row and unbounded following # 包括本行和之后所有的行  rows between 3 preceding and current row# 包括本行和前面三行  rows between 3 preceding and 1 following# 从前面三行和下面一行，总共五行  # 当order by后面缺少窗口从句条件,窗口规范默认是rows between unbounded preceding and currentrow. # 当order by和窗口从句都缺失，窗口规范默认是 rows between unbounded preceding and unboundedfollowing

1.2 窗口函数应用

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

专有窗口函数:
- rank()
- dense_rank()
- row_number()
聚合类窗口函数:
普通场景下，聚合函数往往和group by一起使用，但是窗口环境下，聚合函数也可以应用进来，那么它们称为聚合类窗口函数，属于窗口函数的一种
- sum()
- count()
- avg()
- max()
- min()

窗口函数和普通场景下的聚合函数也很容易混淆，二者区别如下:

聚合函数是将多条记录聚合为一条（多到一）；窗口函数是每条记录都会执行，有几条记录执行完还是几条（多到多）。
分组(partitionby):窗口按照字段进行分组，窗口函数在不同的分组上分别执行。
排序(order by):按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号，可以和partition子句配合使用，也可以单独使用。如果没有partition子句，数据范围则是整个表的数据行。
窗口(rows):就是进行函数分析时要处理的数据范围，属于当前分区的一个子集，通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的移动平均支付金额，则可以设置rows子句来创建滑动窗口(rows)。

1.3 累积计算函数应用

建立数据表

use lagou; create table user_trade ( user_name varchar(20), piece int, price double, pay_amount double, goods_category varchar(20), pay_time date );

问题1：查询出19年每月的支付总额和当年累积支付总额

-- step1 过滤出2019年数据 select month(pay_time), sum(pay_amount) from user_trade where year(pay_time)=2019 -- step2 在1基础上，按照月份进行分组，统计每月的支付总额 group by month(pay_time); -- step3 在2的基础上应用窗口函数计算当年累计支付总额 select a.month, --月份 a.pay_amount, --当月支付总额 sum(a.pay_amount) over(order by a.month) --2019年的数据，因此不需要分组 -- 此时没有指定rows指定窗口数据范围，默认当前行及其之前的所有行 from (select month(pay_time) month, sum(pay_amount) pay_amount from user_trade where year(pay_time)=2019 group by month(pay_time); ) a

实例2：查询出2018-2019年每月的支付总额和当年累计支付总额

-- step1 过滤出2018-2019年数据 select year(pay_time), month(pay_time), sum(pay_amount) from user_trade where year(pay_time) IN(2018,2019) -- step2 在1基础上，按照年份和月份进行分组，统计每月的支付总额 group by year(pay_time),month(pay_time) ; -- step3 在2的基础上应用窗口函数计算当年累计支付总额 select a.year, --年份 a.month, --月份 a.pay_amount, --当月支付总额 sum(a.pay_amount) over(partition by a.year order by a.month) --基于年分组， -- 此时没有指定rows指定窗口数据范围，默认当前行及其之前的所有行 from (select year(pay_time) year, month(pay_time) month, sum(pay_amount) pay_amount from user_trade where year(pay_time) IN(2018,2019) group by year(pay_time),month(pay_time) ; ) a

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

select a.month, --月份 a.pay_amount, --当月支付总额 avg(a.pay_amount) over(order by a.month rows between 2 preceding and current row) avg_pay_amount --基于年分组， from (select month(pay_time) month, sum(pay_amount) pay_amount from user_trade where year(pay_time)=2019 group by month(pay_time) ; ) a

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

-- step1 把每个月的月度支付总额算出来（分组聚合sum统计） select a.month, a.pay_amount, max(a.pay_amount) over(order by a.month rows between 3 preceding and current row) max_pay_amount from (select substring(pay_time,1,7) month, sum(pay_amount) pay_amount from user_trade group by substring(pay_time,1,7) )a

1.4 排序窗口函数应用

实例1：2020年1月，购买商品品类数的用户排名

-- step1 先把2020年1月每个用户所购买商品涉及的品类数统计出来 select user_name, count(distinct goods_category) category_count, row_number() over(order by count(distinct goods_category)) order1 --row_number生成了行的编号，从1开始 rank() over(order by count(distinct goods_category)) order2 dense_rank() over(order by count(distinct goods_category)) order3 from user_trade where substring(pay_time,1,7)='2020-01' group by user_name;)a

实例2：查询出2020年2月的支付用户，按支付金额分成5组的结果

select user_name, sum(pay_amount) pay_amount, -- 按支付金额分成5组 ntile(5) over(order by sum(pay_amount) desc) level from user_trade where substring(pay_time,1,7)='2020-02' group by user_name;

实例3：查询出2020年支付金额排名前30%的所有用户

select a.user_name, a.pay_amount, a.level from (select user_name, sum(pay_amount) pay_amount, -- 按支付金额分成5组 ntile(10) over(order by sum(pay_amount) desc) level from user_trade where year(pay_time)='2020' group by user_name;)a where a.level in(1,2,3);

1.5 偏移函数应用

实例1：查询出支付时间间隔超过100天的用户数

-- 同一用户，相邻的订单进行下单时间比较，如果相邻订单下单时间间隔超过100天，那么这个用户就是你要统计的 select count(distinct user_name) from (select user_name, pay_time, lead(pay_time,1) over(partition by user_name order by pay_time) lead_time from user_trade)a where datediff(a.lead_time,a.pay_time)>100;

实例2：查询出每年支付时间间隔最长的用户

select b.years, b.user_name, b.inteerval_days from (select a.years years, a.user_name user_name, datediff(a.pay_time,a.lag_time) inteerval_days -- 计算订单时间间隔 -- 接下来在年度内按照间隔进行排名，可以使用排名函数 rank() over(partition by a.years order by datediff(a.pay_time,a.lag_time) desc) rank1 from (select user_name, year(pay_time) years, pay_time, lag(pay_time) over(partition by user_name,year(pay_time) order by pay_time asc) lag_time from user_trade)a)b where b.rank1=1