MySQL數據庫窗口函數應用

黃明輝

湖北三峽職業技術學院電子信息學院 湖北 宜昌 443000

引言

MySQL版本從8.0開始支持窗口函數，窗口函數有的也叫分析函數。

窗口的概念非常重要，它可以理解為記錄集合，窗口函數也就是在滿足某種條件的記錄集合上執行的特殊函數對于每條記錄都要在此窗口內執行函數，有的函數隨著記錄不同，窗口大小都是固定的，這種屬于靜態窗口；有的函數則相反，不同的記錄對應著不同的窗口，這種動態變化的窗口叫滑動窗口[1]。簡單地說，窗口函數就是對于查詢的每一行，都使用與該行相關的行進行計算。

1 窗口函數和普通聚合函數的區別

聚合函數是將多條記錄聚合為一條；窗口函數是每條記錄都會執行，有幾條記錄執行完還是幾條。

聚合函數也可以用于窗口函數。在導入數據之前，Sqoop使用JDBC檢查導入的數據表，檢索出表的所有列以及列的SQL數據類型，并將這些SQL類型映射為Jave數據類型，在轉換后的MapReduce應用中使用這些對應的Jave類型來保存字段的值，Sqoop的代碼生成器使用這些信息來創建對應表的類，用于保存從表中抽取的記錄[2]。

2 窗口函數應用特點

數據表，它代表各省中主要城市的國內生產總值（Gross Domestic Product，簡稱GDP）[3]。創建表gdp，表的數據如下表：

表1 gdp數據表

要求計算每個省的GDP總額、在全國的GDP總額，每個城市的GDP占所在省GDP中的比率，以及占總GDP中的比率。

方法一：使用傳統函數來解決

步驟1：創建臨時表t1，顯示全國的GDP值。

步驟2：創建臨時表t2，顯示每個城市的GDP值。

步驟3：連接表t1與t2，顯示出要的結果。

JOIN t2 ON g.province=t2.province－連接省統計結果臨時表

JOIN t1－連接總計生產總值臨時表

結果如下表。

表2 多表查詢結果

方法二：應用窗口函數

結果如下表。

表3 窗口函數查詢結果

應用窗口函數得到同樣結果。

窗口函數應用，一步就能實現最終查詢結果。因為沒有用到臨時表，不占用服務器的內存，提高了執行效率。對于這種需要用到分組統計的結果對每一條記錄進行計算的情況下，使用窗口函數更能發揮出效果。

3 簡單實用窗口函數應用

示例表如下：

表4 student數據表

表5 scoret數據表

表6 course數據表

3.1 求每門課程的成績排名

結果如下表。

表7 成績排名圖

3.2 查詢每位學生的成績總分并排名

SELECT sid, SUM(grade) 總成績, RANK() over(ORDER BY SUM(grade) DESC) 排名

FROM score

GROUP BY sid;

結果如下表。

表8 成績總分排名圖

3.3 查詢每門功課成績最好的前兩名學生姓名及功課

結果如下表。

續表

表9 成績最好的前兩名結果圖

4 結論

窗口函數有聚合、取值、排名、序列4種功能，其中聚合、取值、排名3種使用比較多，也容易理解，而使用序列的不太常用。