mysql視窗函式中的滑動視窗

weixin_46338676發表於2020-11-21

在網上搜尋很多關於視窗函式中的滑動視窗,但是找不到自己需要的,所以自己親自試驗,在此分享一些經驗

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…

相關文章