mysql窗口函数中的滑动窗口

在网上搜索很多关于窗口函数中的滑动窗口，但是找不到自己需要的，所以自己亲自试验，在此分享一些经验

1 窗口函数基本介绍

window_function_name (expression)
over(
	[partition_definition]
	[order_definition]
	[frame_definition])

其中window_function_name为函数名，over是关键字，窗口由 [partition_definition],[order_definition],[frame_definition]共同确定，其中

• partition by子句： 窗口按照指定字段进行分组，窗口功能在分组内执行，并且在跨越分区边界时重新初始化；
• order by 子句：按照指定字段进行排序，窗口函数将按照排序后的记录顺序进行编号，可以和partition 语句结合使用，也可以单独使用；
• frame子句：frame是当前分区的一个子集，在分区中再进一步的细分窗口，通常用来作为滑动窗口使用，>>> 某些窗口函数属于静态窗口，frame子句没有作用

以上为窗口函数的简单介绍，本文主要介绍frame子句即滑动窗口的使用
对于滑动窗口的范围指定，有两种方式，基于行和基于范围，具体区别如下：

基于行 ：

通常使用 ROWS BETWEEN frame_start AND frame_end语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：
CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用

UNBOUNDED PRECEDING 边界是分区中的第一行

UNBOUNDED FOLLOWING 边界是分区中的最后一行

expr PRECEDING 边界是当前行减去expr的值

expr FOLLOWING 边界是当前行加上expr的值

比如，下面都是合法的范围：
rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 窗口范围是当前行、前一行、后一行一共三行记录。

rows UNBOUNDED Preceding 窗口范围是f分区第一行到当前行。

rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 窗口范围是当前分区中所有行，等同于不写。

基于范围

和基于行类似，但有些范围不是直接可以用行数来表示的，比如希望窗口范围是一周前的订单开始，截止到当前行，则无法使用rows来直接表示，此时就可以使用范围RANGE来表示窗口：range INTERVAL 7 DAY PRECEDING。
如果是基于值的范围，可以使用range between 10 preceding and 5 following表示值在[n-10,n+5]范围内的所有值

二 案例 (mysql中的坑）

本人使用的为Mysql8.0
使用表格如下：

① rows unbounded following或者rows 1 following 在当前版本中是无法使用的，rows unbounded preceding或者rows 1 preceding可以使用，建议如果表示前n行到当前行或者当前行至后n行使用rows between…and…
正确表示当前行至后1行如下

select *,sum(money) over (partition by city order by insert_date rows between current row and 1 following) sum 
from a1;

表示按照city进行分组，insert_date进行排序，统计当前行与后一行的和
②当range和PRECEDING/FOLLOWING一起使用时，order by的表达式必须为数字或者时间差

select *,sum(money) over (partition by city order by insert_date range between 4 preceding and current row) sum 
from a1;
Error Code: 3587. Window '<unnamed window>' with RANGE N PRECEDING/FOLLOWING frame requires exactly one ORDER BY expression, of numeric or temporal type

正确如下，表示对范围[n-3,n]的值进行求和，n表示当前行的值

③当order by表达式的类型为datetime时，必须使用Interval

select *,sum(money) over (partition by city order by str_to_date(insert_date,'%Y/%m/%d') range between 4 preceding and current row) sum 
from a1;
Error Code: 3588. Window '<unnamed window>' with RANGE frame has ORDER BY expression of datetime type. Only INTERVAL bound value allowed.

正确如下，表示前两天的累计金额

④range同rows一样，支持range 3 preceding、range interval 3 day preceding但是同样不支持range 3 following、range interval 3 day following，以防出错的话建议均使用range between…and…