tft每日頭條

 > 生活

 > mysql内核教程

mysql内核教程

生活 更新时间:2025-01-23 15:23:22

mysql内核教程(MySQL8.0窗口函數框架用法-愛可生)1

之前在 MySQL 8.0 新特性欄目裡介紹過 8.0 的窗口函數的用法,沒有細化到全部的語法,恰巧今天有客戶追問其中的框架子句用法,寫出來大家一起探讨。

窗口函數其實就是一個分組窗口内部處理每條記錄的函數,這個窗口也就是之前聚合操作的窗口。不同的是,聚合函數是把窗口關閉,給一個彙總的結果;而窗口函數是把窗口打開,給分組内每行記錄求取對應的聚合函數值或者其他表達式的結果。

今天重點看窗口函數内的 frame 子句:frame 子句用來把窗口内的記錄按照指定的條件打印出來,跟在 partition 和 order by 子句後面。frame 子句的語法為:

frame_clause: frame_units frame_extent frame_units: {ROWS | RANGE} frame_extent: {frame_start | frame_between} frame_between: BETWEEN frame_start AND frame_end frame_start, frame_end: { CURRENT ROW | UNBOUNDED PRECEDING | UNBOUNDED FOLLOWING | expr PRECEDING | expr FOLLOWING }

這裡分為兩塊,一塊是 frame_units (框架單元),另外一塊是 frame_extent (框架内容)

  • frame_units 框架單元有兩個,一個是 rows,一個是 range。
  • rows 後面跟的内容為指定的行号,而 range 不同,range 是指的行内容。
  • 框架内容看起來挺多分類,其實就一句話來表達:為了定義分組内對應行記錄的邊界值來求取對應的計算結果。

    基于 t1 舉例說明下:

    mysql: ytt_80 > desc t1; ------- ------ ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- ------ ------ ----- --------- ------- | id | int | YES | | NULL | | | r1 | int | YES | | NULL | | | r2 | int | YES | | NULL | | ------- ------ ------ ----- --------- ------- 3 rows in set (0.00 sec) mysql: ytt_80 > select * from t1; ------ ------ ------ | id | r1 | r2 | ------ ------ ------ | 2 | 1 | 1 | | 2 | 2 | 20 | | 2 | 3 | 30 | | 2 | 4 | 40 | | 3 | 3 | 3 | | 3 | 2 | 2 | | 3 | 10 | 20 | | 3 | 30 | 20 | | 1 | 1 | 1 | | 1 | 2 | 3 | | 1 | 3 | 4 | | 1 | 10 | 10 | | 1 | 15 | 20 | | 2 | 15 | 2 | | 3 | 15 | 5 | | 1 | 9 | 100 | ------ ------ ------ 16 rows in set (0.00 sec)

    1、CURRENT ROW

    表示獲取當前行記錄,也就是邊界是當前行,等值關系

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc range current row ) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 1 | | 1 | 2 | 2 | | 1 | 3 | 3 | | 1 | 9 | 9 | | 1 | 10 | 10 | | 1 | 15 | 15 | ------ ------ ----------- 6 rows in set (0.00 sec)

    這裡我們求 ID 為 1 的分組記錄,基于聚合函數 SUM 來對分組内的行記錄按照一定的條件求和。其中 OVER 子句用來定義分區以及相關條件,這裡表示隻獲取分組内排序字段的當前行記錄,也就是字段 r1 對應的記錄,這是最簡單的場景。

    2、UNBOUNDED PRECEDING

    表示邊界永遠為第一行

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc rows unbounded preceding ) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 1 | | 1 | 2 | 3 | | 1 | 3 | 6 | | 1 | 9 | 15 | | 1 | 10 | 25 | | 1 | 15 | 40 | ------ ------ ----------- 6 rows in set (0.00 sec)

    以上 unbounded preceding 用來獲取表 t1 按照字段 ID 來分組,并且對字段 r1 求和。由于都是以第一行,也就是 r1 = 1 為基礎求和,也就是求取上一行和當前行相加的結果,基于第一行記錄。這個例子中 r1 字段的第一行記錄為 1,後面的所有求和都是基于第一行來累加的結果。

    3、unbounded following

    表示邊界永遠為最後一行

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc rows between unbounded preceding and unbounded following ) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 40 | | 1 | 2 | 40 | | 1 | 3 | 40 | | 1 | 9 | 40 | | 1 | 10 | 40 | | 1 | 15 | 40 | ------ ------ ----------- 6 rows in set (0.00 sec)

    以上用了 rows between 把邊界局限在第一行和最後一行,這樣每行的求和結果和不帶邊界一樣,也就是下面查詢:

    mysql: ytt_80 > select id,r1,sum(r1) over() as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 40 | | 1 | 2 | 40 | | 1 | 3 | 40 | | 1 | 10 | 40 | | 1 | 15 | 40 | | 1 | 9 | 40 | ------ ------ ----------- 6 rows in set (0.00 sec)

    4、expr preceding / following

    帶表達式的邊界,

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc rows 1 preceding) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 1 | | 1 | 2 | 3 | | 1 | 3 | 5 | | 1 | 9 | 12 | | 1 | 10 | 19 | | 1 | 15 | 25 | ------ ------ ----------- 6 rows in set (0.00 sec)

    帶表達式的邊界隻是把無邊界換成 具體的行号。上面的查詢表達的意思是基于分組内每行記錄和它上一條記錄求和,不累加。可以看到 wf_result 的具體值,25 對應的是 10 和 15 求和,19 對應的是 9 和 10 求和。

    那以此類推,求每行和它上面兩行的和:

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc rows 2 preceding) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 1 | | 1 | 2 | 3 | | 1 | 3 | 6 | | 1 | 9 | 14 | | 1 | 10 | 22 | | 1 | 15 | 34 | ------ ------ ----------- 6 rows in set (0.00 sec)

    再來求每行的前兩行和後面四行相加的結果:

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc rows between 2 preceding and 4 following) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 25 | | 1 | 2 | 40 | | 1 | 3 | 40 | | 1 | 9 | 39 | | 1 | 10 | 37 | | 1 | 15 | 34 | ------ ------ ----------- 6 rows in set (0.00 sec)

    其實 rows 單元很簡單,接下來看下 range ,range 稍微難理解些。

    5、range preceding / following

    求當前行值範圍内的分組記錄。這個沒有 rows 好理解,rows 對應的是行号,range 對應的行值。看下面例子:

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc range 1 preceding) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 1 | | 1 | 2 | 3 | | 1 | 3 | 5 | | 1 | 9 | 9 | | 1 | 10 | 19 | | 1 | 15 | 15 | ------ ------ ----------- 6 rows in set (0.00 sec)

    這個例子包含的關鍵詞 range 1 preceding ,是個表達式條件,表示對于分組内每一行來講:以字段 r1 當前行值減去1的結果為邊界來求和。具體點就是:第一行,r1 的值為 1,那 1-1=0, 由于表 t1 裡沒有找到 r1 = 0 的結果,所以此時 wf_result = 1,也就是等于當前行值;對于第五行,由于 r1 對應的值為 10 , 10 - 1 = 9 ,表 t1 裡 r1 = 9 是存在的,此時求和結果為 9 10 = 19。

    再次帶上範圍來看下另外一個例子:

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc range between 1 preceding and 1 following) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 3 | | 1 | 2 | 6 | | 1 | 3 | 5 | | 1 | 9 | 19 | | 1 | 10 | 19 | | 1 | 15 | 15 | ------ ------ ----------- 6 rows in set (0.00 sec)

    這個例子 over 子句裡指定一個邊界範圍,也就是對每行值減1和加1後對應的記錄來求和。比如第一行:r1 = 1,1 -1 =0,1 1 =2,表 t1 沒有 r1 = 0 的記錄,但是有 r1 = 2 的記錄,所以第一行的窗口求和結果為 3;再來看看 r1 = 10 的這行,10-1 = 9,10 1 = 11, 表 t1 裡有 r1 = 9 的記錄,沒有 r1 = 11 的記錄,所以這裡的求和結果為 9 10=19.

    這裡舉例說明了 MySQL 8.0 窗口函數 frame 子句的用法,可能使用場景比較稀少,不過可以收藏起來以備不時之需。

    關鍵字:愛可生、MySQL數據庫、數據庫運維管理、開源數據庫解決方案

    ,

    更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!

    查看全部

    相关生活资讯推荐

    热门生活资讯推荐

    网友关注

    Copyright 2023-2025 - www.tftnews.com All Rights Reserved