数据库|mysql窗口函数中的滑动窗口数据库|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 '' with RANGE N PRECEDING/FOLLOWING frame requires exactly one ORDER BY expression, of numeric or temporal type

【数据库|mysql窗口函数中的滑动窗口】正确如下，表示对范围[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 '' 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…