MySQL數(shù)據(jù)庫(kù)窗口函數(shù)應(yīng)用

黃明輝

湖北三峽職業(yè)技術(shù)學(xué)院電子信息學(xué)院 湖北 宜昌 443000

引言

MySQL版本從8.0開(kāi)始支持窗口函數(shù)，窗口函數(shù)有的也叫分析函數(shù)。

窗口的概念非常重要，它可以理解為記錄集合，窗口函數(shù)也就是在滿(mǎn)足某種條件的記錄集合上執(zhí)行的特殊函數(shù)對(duì)于每條記錄都要在此窗口內(nèi)執(zhí)行函數(shù)，有的函數(shù)隨著記錄不同，窗口大小都是固定的，這種屬于靜態(tài)窗口；有的函數(shù)則相反，不同的記錄對(duì)應(yīng)著不同的窗口，這種動(dòng)態(tài)變化的窗口叫滑動(dòng)窗口[1]。簡(jiǎn)單地說(shuō)，窗口函數(shù)就是對(duì)于查詢(xún)的每一行，都使用與該行相關(guān)的行進(jìn)行計(jì)算。

1 窗口函數(shù)和普通聚合函數(shù)的區(qū)別

聚合函數(shù)是將多條記錄聚合為一條；窗口函數(shù)是每條記錄都會(huì)執(zhí)行，有幾條記錄執(zhí)行完還是幾條。

聚合函數(shù)也可以用于窗口函數(shù)。在導(dǎo)入數(shù)據(jù)之前，Sqoop使用JDBC檢查導(dǎo)入的數(shù)據(jù)表，檢索出表的所有列以及列的SQL數(shù)據(jù)類(lèi)型，并將這些SQL類(lèi)型映射為Jave數(shù)據(jù)類(lèi)型，在轉(zhuǎn)換后的MapReduce應(yīng)用中使用這些對(duì)應(yīng)的Jave類(lèi)型來(lái)保存字段的值，Sqoop的代碼生成器使用這些信息來(lái)創(chuàng)建對(duì)應(yīng)表的類(lèi)，用于保存從表中抽取的記錄[2]。

2 窗口函數(shù)應(yīng)用特點(diǎn)

數(shù)據(jù)表，它代表各省中主要城市的國(guó)內(nèi)生產(chǎn)總值（Gross Domestic Product，簡(jiǎn)稱(chēng)GDP）[3]。創(chuàng)建表gdp，表的數(shù)據(jù)如下表：

表1 gdp數(shù)據(jù)表

要求計(jì)算每個(gè)省的GDP總額、在全國(guó)的GDP總額，每個(gè)城市的GDP占所在省GDP中的比率，以及占總GDP中的比率。

方法一：使用傳統(tǒng)函數(shù)來(lái)解決

步驟1：創(chuàng)建臨時(shí)表t1，顯示全國(guó)的GDP值。

步驟2：創(chuàng)建臨時(shí)表t2，顯示每個(gè)城市的GDP值。

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

JOIN t2 ON g.province=t2.province－連接省統(tǒng)計(jì)結(jié)果臨時(shí)表

JOIN t1－連接總計(jì)生產(chǎn)總值臨時(shí)表

結(jié)果如下表。

表2 多表查詢(xún)結(jié)果

方法二：應(yīng)用窗口函數(shù)

結(jié)果如下表。

表3 窗口函數(shù)查詢(xún)結(jié)果

應(yīng)用窗口函數(shù)得到同樣結(jié)果。

窗口函數(shù)應(yīng)用，一步就能實(shí)現(xiàn)最終查詢(xún)結(jié)果。因?yàn)闆](méi)有用到臨時(shí)表，不占用服務(wù)器的內(nèi)存，提高了執(zhí)行效率。對(duì)于這種需要用到分組統(tǒng)計(jì)的結(jié)果對(duì)每一條記錄進(jìn)行計(jì)算的情況下，使用窗口函數(shù)更能發(fā)揮出效果。

3 簡(jiǎn)單實(shí)用窗口函數(shù)應(yīng)用

示例表如下：

表4 student數(shù)據(jù)表

表5 scoret數(shù)據(jù)表

表6 course數(shù)據(jù)表

3.1 求每門(mén)課程的成績(jī)排名

結(jié)果如下表。

表7 成績(jī)排名圖

3.2 查詢(xún)每位學(xué)生的成績(jī)總分并排名

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

FROM score

GROUP BY sid;

結(jié)果如下表。

表8 成績(jī)總分排名圖

3.3 查詢(xún)每門(mén)功課成績(jī)最好的前兩名學(xué)生姓名及功課

結(jié)果如下表。

續(xù)表

表9 成績(jī)最好的前兩名結(jié)果圖

4 結(jié)論

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