MySQL 窗口函數之頭尾函數

2020 年 1 月 1 日
筆記

本文標識 : MQ0018

本文編輯 : 長安月下賞美人兒

編程工具 : MySQL、DBeaver

閱讀時長 : 6分鐘

1、頭尾函數

（1）應用場景：快速查看某個窗口範圍中的第一個或者最後一個指定的字段的數值

（2）頭尾函數分類及基礎語法

FIRST_value(指定字段)OVER(PARTITION BY 分區的字段 ORDER BY 排序的字段 DESC/ASC)  LAST_value（字段）OVER (PARTITION BY 分區字段 ORDER BY 排序的字段 DESC/ASC)

（3）兩種頭尾函數的區別

first_value() 函數

指定排序字段，不同分區中，指定字段在窗口範圍第一個值

last_value() 函數

指定排序字段，不同分區中，指定字段在窗口範圍最後一個值

（4）實例比較兩種頭尾函數

SELECT t2.*        ,FIRST_value(t2.amt)OVER()AS f1        ,LAST_value(t2.amt)OVER()AS l2  FROM(SELECT t1.dimShopID        ,t1.dimDateID        ,SUM(AMT) AS amt  FROM dw.fct_sales AS t1  WHERE dimDateID BETWEEN 20170801 AND 20170810  GROUP BY t1.dimShopID          ,t1.dimDateID)AS t2  ORDER BY dimShopID,amt DESC;

數據結果：

注意：當函數後無指定分區及排序字段，即 over() 括號內容為空，則會出現上面的結果。

如果函數後有指定的分區及排序的字段又會如何呢？！

此刻，將分享的知識點為窗口的滑動函數！！！

2、滑動函數

（1）基礎語法與頭尾函數相似

（2）作用：在 over() 中以參數限制窗口分析範圍

（3）表示行範圍語法

BETWEEN frame_start AND frame_end

（4）frame_start 和 frame_end 如下關鍵字，精準確定窗口函數分析範圍

CURRENT ROW ：邊界是當前行，常與其他關鍵字組合使用
UNBOUNDED PRECEDING ：邊界是分區中的第一行
UNBOUNDED FOLLOWING ：邊界分區中的最後一行
expr PRECEDING：邊界為以當前行減去 expr 數值
expr FOLLOWING：邊界為以當前行加上 expr 數值

（5）舉例

#窗口範圍是當前行，前 2 行，後 1 行，共計 4 行記錄  RANGE BETWEEN  2 PRECEDING AND 1 FOLLOWING

#窗口範圍是當前行到分區中的最後一行  RANGE UNBOUNDED FOLLOWING

#窗口範圍是當前分區中所有行  RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

（6）實例展示窗口滑動函數

SELECT t2.*        ,FIRST_value(t2.amt)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC)AS f1        ,LAST_value(t2.amt)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC)AS l2  FROM(SELECT t1.dimShopID        ,t1.dimDateID        ,SUM(AMT) AS amt  FROM dw.fct_sales AS t1  WHERE dimDateID BETWEEN 20170801 AND 20170810  GROUP BY t1.dimShopID          ,t1.dimDateID)AS t2  ORDER BY dimShopID,amt DESC;

數據結果：

注意：經過對比發現，在沒有加入限定範圍的情況下，得到的結果，並不是理想中的結果。l2 數據列，數據值是混亂的，並不是每個分組中最小的值。

為什麼會出現這種情況？！

因為，窗口函數，默認限制範圍是第一行到當前行！！！

分析：

目前以 dimShopID 分成兩組，且以 amt 數列倒序排列，理想中的結果 l2 數列，當 dimShopID =33 時，數值應當為 37,233.64；當 dimShopID =34時，數值應當為 44,691.52；
因窗口函數，默認限制範圍是第一行到當前行，所以 l2 數列呈現與 amt 數列相同。

添加限制範圍，則

SELECT t2.*        ,FIRST_value(t2.amt)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC)AS f1        ,LAST_value(t2.amt)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS l2  FROM(SELECT t1.dimShopID        ,t1.dimDateID        ,SUM(AMT) AS amt  FROM dw.fct_sales AS t1  WHERE dimDateID BETWEEN 20170801 AND 20170810  GROUP BY t1.dimShopID          ,t1.dimDateID)AS t2  ORDER BY dimShopID,amt DESC;

數據結果：