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…