背景
窗口函数也叫分析函数，主要用于处理相对复杂的报表统计分析场景，这个功能在大多商业数据库和部分开源数据库中已经支持，mysql从8.0开始支持窗口函数。经典使用场景是数据错位相减的场景，比如求查询每年支付时间间隔最长的用户，此时如果不使用窗口函数的话，计算过程比较复杂，使用窗口函数会大大简化开发。本文以mysql8.0为演示环境，运行测试相关案例数据。
1. 窗口函数概念
窗口函数可以从名字理解为应用在窗口的函数。即限定一个空间范围（窗口），执行指定的函数。通俗理解，有点像老式的电影播放机，相片（窗口）一帧一帧的滑过，投在上面的光（函数）是固定的。
窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，窗口大小都是固定的，这种属于静态窗口；不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。
1.2 基本用法
窗口函数的基本用法如下：
函数名（[expr]） over子句
函数() over()
其中，over是关键字，用来指定函数执行的窗口范围，包含三个分析子句：分组(partition by)子句，排序(order by)子句，窗口(rows)子句，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下语法来设置窗口：
函数名（[expr]） over(partition by <要分列的组> order by <要排序的列> rows between <数据范围>)
1.3 使用举例
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 current row;
# 当order by和窗口从句都缺失，窗口规范默认是 rows between unbounded precedingand unbounded following
2. 窗口函数应用
一般窗口函数可以分为两类：专有窗口函数和聚合类窗口函数。
专业窗口函数：
rank()
dense_rank()
row_number()
聚合类窗口函数：
普通场景下，聚合函数往往和group by一起使用，但是窗口环境下，聚合函数也可以应用进来，那么此时它们就被称之为聚合类窗口函数，属于窗口函数的一种
sum()
count()
avg()
max()
min()
窗口函数（专有窗口函数+聚合类窗口函数）和普通场景下的聚合函数也很容易混淆，二者区别如下：
- 普通场景下的聚合函数是将多条记录聚合为一条（多到一）；窗口函数是每条记录都会执行，有几条记录执行完还是几条（多到多）。
- 分组(partition by)：记录按照字段进行分组，窗口函数在不同的分组上分别执行
- 排序(order by)：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号，可以和partition子句配合使用，也可以单独使用。如果没有partition子句，数据范围则是整个表的数据行。
- 窗口(rows)：就是进行函数分析时要处理的数据范围，属于当前分区的一个子集，通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的移动平均支付金额，则可以设置rows子句来创建滑动窗口(rows)。
2.1 数据准备
create table user_trade
(
user_name varchar(20) comment '用户名',
piece int comment '购买数量',
price double comment '价格',
pay_amount double comment '支付金额',
goods_category varchar(20) comment '商品品类',
pay_time date comment '支付时间'
);
# 插入语句详见脚本
# https://gitee.com/wlyang666/csdn-resource-summary/blob/master/csdn/SQL/144698094/%E7%AA%97%E5%8F%A3%E5%87%BD%E6%95%B0%E6%BC%94%E7%A4%BA%E5%88%9D%E5%A7%8B%E5%8C%96%E6%95%B0%E6%8D%AE.sql
2.2 累计计算函数
2.2.1 累计求和：sum() over()
2.2.1.1 查询出2019年每月的支付总额和当年累积支付总额
select * from user_trade where year(pay_time)='2019';
-- 2019年 每月支付总额
select month(pay_time), sum(pay_amount)
from user_trade
where year(pay_time) = '2019'
group by month(pay_time);
-- 当年累计支付总额
select sum(pay_amount)
from user_trade
where year(pay_time) = '2019';
-- 高阶函数实现
select a.month, a.sump, sum(a.sump) over (order by a.month)
from (select month(pay_time) as month, sum(pay_amount) sump
from user_trade
where year(pay_time) = '2019'
group by month(pay_time)) a;
2.2.1.2 查询出2018-2019年每月的支付总额和当年累积支付总额
-- 计算 每年的累计支付金额
select sum(pay_amount)
from user_trade
where year(pay_time) in ('2018', '2019')
group by year(pay_time);
-- 计算每月支付总额
select year(pay_time), month(pay_time), sum(pay_amount)
from user_trade
where year(pay_time) in ('2018', '2019')
group by year(pay_time), month(pay_time);
-- 使用高阶函数统计按年汇总的支付总额
select t.year, t.month,sump, sum(t.sump) over (partition by year order by month)
from (select year(pay_time) as year, month(pay_time) as month, sum(pay_amount) as sump
from user_trade
where year(pay_time) in ('2018', '2019')
group by year(pay_time), month(pay_time)) as t;
2.2.2 移动平均：avg() over()
2.2.2.1 查询出2019年每个月的近三月移动平均支付金额
select * from user_trade where year(pay_time)='2019';
select month,
pay_amount,
avg(pay_amount)
over (order by month rows between 2 preceding and current row )
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.2.3 最大/小值：max()/min() over()
2.2.3.1 查询出每四个月的最大月总支付金额
select substr(pay_time, 1, 7) month, sum(pay_amount) sump from user_trade group by substr(pay_time, 1, 7);
select t.month,
sump,
max(sump) over (order by t.month rows between 3 preceding and current row )
from (select substr(pay_time, 1, 7) month,
sum(pay_amount) sump
from user_trade
group by substr(pay_time, 1, 7)) t;
结果样例：
2.3 排序函数
排序函数常见有三种方式
- row_number() over(partition by …A… order by …B… )
- rank() over(partition by …A… order by …B… )
- dense_rank() over(partition by …A… order by …B… )
这三个函数的作用都是返回相应规则的排序序号
- row_number:它会为查询出来的每一行记录生成一个序号，依次排序且不会重复。
- 如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一。
- dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。
- dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。
- 总结来说，在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第三名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。
2.3.1 rank dense_rank and rownumber
2.3.1.1 2020年1月，购买商品品类数的用户排名
select user_name,count(distinct goods_category) from user_trade group by user_name;
select user_name,
count(distinct goods_category),
row_number() over (order by count(distinct goods_category) ),
rank() over (order by count(distinct goods_category) ),
dense_rank() over (order by count(distinct goods_category) )
from user_trade
group by user_name;
结果类似这种
2.3.2 ntile 函数
ntile(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
ntile(n) over(partition by …A… order by …B… )
n:切分的片数
A:分组的字段名称
B:排序的字段名称
注意： ntile不支持ROWS BETWEEN
2.3.2.1 查询出将2020年2月的支付用户，按照支付金额分成5组后的结果
select * from user_trade where substr(pay_time,1,7)='2020-02';
select user_name,sum(pay_amount) from user_trade where substr(pay_time,1,7)='2020-02' group by user_name;
select user_name,
suma,
ntile(5) over ( order by suma desc )
from (select user_name,
sum(pay_amount) suma
from user_trade
where substr(pay_time, 1, 7) = '2020-02'
group by user_name) t;
结果样例：
2.3.2.2 查询出2020年支付金额排名前30%的所有用户
select * from user_trade where year(pay_time)='2020';
select user_name,sum(pay_amount) from user_trade where year(pay_time)='2020' group by user_name;
select user_name,
sum(pay_amount),
ntile(10) over ( order by sum(pay_amount) desc) as level
from user_trade
where year(pay_time) = '2020'
group by user_name
;
select *
from (select user_name,
sum(pay_amount),
ntile(10) over ( order by sum(pay_amount) desc) as level
from user_trade
where year(pay_time) = '2020'
group by user_name) t
where level in ('1', '2', '3');
结果样例：
2.4 偏移分析函数
Lag和Lead函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为 独立的列。
在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。
用法如下:
lag(exp_str,offset,defval) over(partion by ......order by ......)
lead(exp_str,offset,defval) over(partion by ......order by ......)
其中：
- exp_str是字段名称。
- offset是偏移量，即是上1个或上N个的值，假设当前行在表中排在第5 行，则offset 为3，则表示我 们所要找的数据行就是表中的第2行(即5-3=2)。offset默认值为1。
- defval默认值，当两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范 围时，lag()函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL， 在数学运算中，总要给一个默认值才不会出错。
2.4.1 lag
2.4.1.1 查询出King和West的时间偏移(前N行)
select * from user_trade where user_name in ('King','West');
select user_name,
pay_time,
lag(pay_time, 1, pay_time) over (partition by user_name order by pay_time) lag1,
lag(pay_time) over (partition by user_name order by pay_time) lag2,
lag(pay_time, 2, pay_time) over (partition by user_name order by pay_time) lag3,
lag(pay_time, 2) over (partition by user_name order by pay_time) lag4
from user_trade where user_name in ('King','West');
2.4.2 lead
2.4.2.1 King和West的时间偏移(后N行)
select user_name,
pay_time,
lead(pay_time, 1, pay_time) over (partition by user_name order by pay_time) lead1,
lead(pay_time) over (partition by user_name order by pay_time) lead2,
lead(pay_time, 2, pay_time) over (partition by user_name order by pay_time) lead3,
lead(pay_time, 2) over (partition by user_name order by pay_time) lead4
from user_trade where user_name in ('King','West');
结果样例：
2.4.2.3 查询出支付时间间隔超过100天的用户数
select user_name,
pay_time,
lag(pay_time) over (partition by user_name order by pay_time ) ldate
from user_trade;
select count(distinct user_name)
from (select user_name,
pay_time,
lead(pay_time) over (partition by user_name order by pay_time ) ldate
from user_trade) t
where t.ldate is not null
and datediff(t.ldate, t.pay_time) > 100;
结果样例：
2.4.2.4 查询出每年支付时间间隔最长的用户
select year(pay_time),
user_name,
pay_time,
lag(pay_time) over (partition by year(pay_time),user_name order by pay_time) as ldate from user_trade;
select year(pay_time),
user_name,
datediff(pay_time, ldate) as diffday,
rank() over (partition by year(pay_time) order by datediff(pay_time, ldate) desc) as rank1
from (select year(pay_time),
user_name,
pay_time,
lag(pay_time) over (partition by year(pay_time),user_name order by pay_time) as ldate
from user_trade) t;
select pay_year, user_name, diffday
from (select year(pay_time) as pay_year,
user_name,
datediff(pay_time, ldate) as diffday,
rank() over (partition by year(pay_time) order by datediff(pay_time, ldate) desc) as rank1
from (select year(pay_time),
user_name,
pay_time,
lag(pay_time) over (partition by year(pay_time),user_name order by pay_time) as ldate
from user_trade) t
where t.ldate is not null) a
where rank1 = 1;
结果样例：
3. 扩展
3.1 with 使用
3.1.1 简介
WITH 子句（也称为公用表表达式，Common Table Expressions，简称 CTEs）用于定义一个或多个临时的结果集，这些结果集在查询的范围内可用。CTE 使得查询更加模块化和可读，特别是对于那些包含多个步骤或复杂计算的查询。
WITH cte_name (column1, column2, ...) AS (
-- 这里是定义 CTE 的查询
SELECT ...
FROM ...
WHERE ...
-- 可以包含其他 SQL 子句，如 GROUP BY, HAVING, ORDER BY（但注意，ORDER BY 在 CTE 中通常不起作用，除非与 TOP 或 FETCH 一起使用）
)
-- 这里是主查询，可以使用上面定义的 CTE
SELECT ...
FROM cte_name
-- 可以与其他表进行 JOIN, WHERE, GROUP BY 等操作
3.1.2 案例介绍
with cte_test (pay_year, user_name, diffday, rank1, ldate)
as (select year(pay_time) as pay_year,
user_name,
datediff(pay_time, ldate) as diffday,
rank() over (partition by year(pay_time) order by datediff(pay_time, ldate) desc) as rank1,
t.ldate as ldate
from (select year(pay_time),
user_name,
pay_time,
lag(pay_time)
over (partition by year(pay_time),user_name order by pay_time) as ldate
from user_trade) t
where t.ldate is not null )
select pay_year, user_name, diffday,rank1
from cte_test
order by rank1;
3.1.3 优点
- 可读性：通过将查询分解为更小的部分，CTE 使得复杂的查询更加易于理解和维护。
- 重用性：在同一个查询中，你可以多次引用同一个 CTE，而无需重复编写相同的代码。
- 递归查询：CTE 还可以用于递归查询，这在处理层次结构数据（如组织结构图、目录树等）时非常有用。
需要注意的是:
- 虽然 ORDER BY 子句可以在 CTE 中使用，但它通常不会改变 CTE 结果集的实际顺序，除非与 TOP、FETCH 或在主查询中的 ORDER BY 一起使用。此外，CTE 在查询执行时才会被计算，并且只在该查询的上下文中可用。
- CTE中的字段顺序必须和SQL查询结果中的字段顺序保持一致，否则主查询查到的结果会异常
4. 总结
通过上面案例可以看到，窗口函数是一种强大的工具，窗口函数在某些数据分析场景会极大简化脚本开发工作量，它允许你在不改变查询结果集行数的情况下，对每一行执行复杂的聚合计算和其他操作。通过合理使用窗口函数，你可以解决许多复杂的数据分析问题，并得到更精确的分析结果。
以上,如有错误，请不吝指正！
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
