===本節(jié)主要講了以下內(nèi)容 一、窗口函數(shù)，簡要的說就是在滿足某條件的記錄集合上運行的特殊函數(shù)。 后面的幾個例子多看看，是窗口函數(shù)的應(yīng)用。

第三部分MySQL高級應(yīng)用

窗口函數(shù)，索引，視圖

第一節(jié) 窗口函數(shù)

MySQL從8.0開始支持窗口函數(shù)，有的也叫分析函數(shù)（處理相對復雜的報表統(tǒng)計分析場

景），這個功能在大多商業(yè)數(shù)據(jù)庫和部分開源數(shù)據(jù)庫中早已支持。

窗口函數(shù)：窗口、函數(shù)（應(yīng)用在窗口內(nèi)的函數(shù)）-----窗口類似于窗戶，限定一個空間范圍

窗口的概念非常重要，它可以理解為記錄集合，窗口函數(shù)也就是在滿足某種條件的記錄集合上執(zhí)行的特殊函數(shù)。對于每條記錄都要在此窗口內(nèi)執(zhí)行函數(shù)，窗口大小都是固定的，這種屬于靜態(tài)窗口；不同的記錄對應(yīng)著不同的窗口，這種動態(tài)變化的窗口叫滑動窗口。

其中，over是關(guān)鍵字，用來指定函數(shù)執(zhí)行的窗口范圍，包含三個分析子句：分組(partition by)子句，排序(order by)子句，窗口(rows)子句，如果后面括號中什么都不寫，則意味著窗口包含滿足where條件的所有行，窗口函數(shù)基于所有行進行計算；如果不為空，則支持以下語法來設(shè)置窗口：

over這個窗口函數(shù)還是很常見的，后面的括號里可以填寫三類分析子句，注意這里的分組不是group by哦。

A: 需要被加工的字段名稱

B: 分組的字段名稱

C: 排序的字段名稱

D: 計算的行數(shù)范圍

這上面講解了一下rows這個語句的計算方式，其實看一遍就明白了。

1.2 窗口函數(shù)的應(yīng)用

一般，我們可以把窗口函數(shù)分為兩種：

專有窗口函數(shù)：rank(), dense_rank(), row_number()

這三個之前在刷題時遇到過，三個都是排名用的，不同之處在于相同數(shù)值的排序，rank是相同的一樣排名，后面一個會跳著排；dense——rank也是相同的一樣排名，但后面一個會連著排，row_number就是相同的也不同排名。

聚合類窗口函數(shù)：

普通場景下，聚合函數(shù)往往和group by一起使用，但是窗口環(huán)境下，聚合函數(shù)也可以應(yīng)用進來，那么此時它們就被稱之為聚合類窗口函數(shù)，屬于窗口函數(shù)的一種

sum()，count()，avg()，max()，min()

窗口函數(shù)（專有窗口函數(shù)+聚合類窗口函數(shù)）和普通場景下的聚合函數(shù)也很容易混淆，二者區(qū)別如下：

1 普通場景下的聚合函數(shù)是將多條記錄聚合為一條（多到一）；窗口函數(shù)是每條記錄都會執(zhí)行，有幾條記錄執(zhí)行完還是幾條（多到多）。

這點還是非常明顯的，后面也出現(xiàn)過

2?分組(partition by)：記錄按照字段進行分組，窗口函數(shù)在不同的分組上分別執(zhí)行。

3?排序(order by)：按照哪些字段進行排序，窗口函數(shù)將按照排序后的記錄順序進行編號，可以和partition子句配合使用，也可以單獨使用。如果沒有partition子句，數(shù)據(jù)范圍則是整個表的數(shù)據(jù)行。

4?窗口(rows)：就是進行函數(shù)分析時要處理的數(shù)據(jù)范圍，屬于當前分區(qū)的一個子集，通常用來作為滑動窗口使用。比如要根據(jù)每個訂單動態(tài)計算包括本訂單和按時間順序前后兩個訂單的移動平均支付金額，則可以設(shè)置rows子句來創(chuàng)建滑動窗口(rows)。

下面開始來實例

現(xiàn)有2018～2020某電商平臺訂單信息表user_trade

依據(jù)上面的信息，在數(shù)據(jù)庫中建立表

navicat(MySQL的一個圖形化工具)導入數(shù)據(jù)：D:/course/mysql/資料/窗口函數(shù)數(shù)據(jù)/user_trade.xlsx

------累計計算函數(shù)應(yīng)用、排序函數(shù)應(yīng)用、偏移分析函數(shù)應(yīng)用

1.2.1累計計算函數(shù)

需求1?查詢出2019年每月的支付總額和當年累積支付總額

這里計算每個月的總額，首先上述數(shù)據(jù)里面是沒有月度數(shù)據(jù)的，其次如何按照月來分別計算每個月的總額也是一個問題。select中的兩個值應(yīng)該怎么填？

第一步：過濾出2019年的數(shù)據(jù)

第二步：將上面數(shù)據(jù)按月進行g(shù)roup by分組，統(tǒng)計每個月份的支付總額

第三步：在上述基礎(chǔ)上，使用窗口函數(shù)實現(xiàn)需求

==首先問月的銷售總額，不用想太多，直接用sum求和計算就可以；在此同時問年的累計總和就需要用窗口函數(shù)，滑動月份窗口來計算（4.23

以下是計算結(jié)果，代碼最好自己手寫一遍

這里需要先計算第二步，在第二步基礎(chǔ)上往下做，另外pay_time是data類型，可以使用year，month分別提取出年和月。

需求2? 查詢出2018-2019年每月的支付總額和當年累積支付總額

這題和上題大體上是相同的，但需要注意的是，1：這里除了按月分組之外，還需要按年分組，這里使用了over中的第一個參數(shù)partition by。1：還有就是這里不再是一個年份，而是一個年份區(qū)間，where不再使用等于，而是in。

在子查詢里面order by也是可以有兩個參數(shù)的哦

==這里和上題只有一個區(qū)別，就是在年份這里有兩個值，需要進行分組計算，所以就引出了partition這個參數(shù)。（4.23

移動平均：avg()over()

需求3?查詢出2019年每個月的近三月移動平均支付金額

這里說要每個月前3個月的平均支付，其實剛看到是有些蒙的，但是不礙事，我們先寫子查詢，子查詢我們不考慮移動平均三個月什么亂七八糟的，我們只要兩列，月，每月支付累計；這樣是不是就簡單多了。

然后我們在寫select，移動平均使用avg（）over（），這里還是需要order by來對月份進行排名，然后再利用over當中的第三個參數(shù)rows來進行前3個月的判定。

前面對rows函數(shù)也講過使用方式，這里需要寫2 preceding and current row，即前兩行加其自身，另外，在order by和rows參數(shù)之間竟然是沒有逗號的。

==這里的移動平均avg其實是次要的，它主要想讓我們掌握窗口函數(shù)的第三個參數(shù)rows，不管出現(xiàn)什么“近多久”，都得想到這個窗口函數(shù)和rows參數(shù)（4.23

最大/小值：max()/min() over()

需求4：查詢出每四個月的最大月總支付金額

這里的其他部分我都寫對了，但是這個substr是給我整懵了，沒學過這個函數(shù)啊

==用date_format一樣可以解決（4.23

1.2.2 排序函數(shù)

row_number() over(......)

rank() over(......)

dense_rank() over(......)

需求5?2020年1月，購買商品品類數(shù)的用戶排名

==遇到排名問題，首先把排名需要的數(shù)據(jù)列出來，而排名本身就是一個窗口函數(shù)，并且有三類，三類效果不同（4.23

首先第一點，count進行計數(shù)時，千萬不能忘記使用distinct；第二，排名這類專有的窗口函數(shù)的排名參數(shù)是在over（）中的order by里面給出的，不是在rank里面；第三這里推測這個substring是可以直接調(diào)出具體日期的，不過誰能告訴我，上一題里沒有ing這里又有。

需求6 ：查詢出將2020年2月的支付用戶，按照支付金額分成5組后的結(jié)果

==這里沒考慮支付數(shù)為0的情況，然后分組問題是ntile函數(shù)，并且也需要開一個窗口來按支付金額排序。（4.23

這一題的問題在于如何分成固定的組數(shù)，這里給出了一個新函數(shù)，ntile，它可以分為固定組數(shù)；另外上一題的substr果然寫錯了，應(yīng)該是不帶ing的。

需求7?查詢出2020年支付金額排名前30%的所有用戶

這一題其實設(shè)計的很巧妙，寫完看答案后嘆為觀止，這題的主要難度在于這個30%需要怎么表示，他這里用了上一題使用的分組函數(shù)，講排名分成10組，取前3組，就實現(xiàn)了30%這個說法。

ntile（）over（? ?排序? desc）level是這個函數(shù)的使用方法

==這是分組函數(shù)的一個巧妙應(yīng)用。（4.23

1.2.3偏移分析函數(shù)

lag(...) over(......)往前查找

lead(...) over(......)往后查找

需求8?查詢出King和West的時間偏移(前N行)

其實這個耐下心去看還是挺好理解的，雖然這里的代碼寫錯了兩個，但是不影響理解。

首先明確lag（）over（）是用來向上求偏置的，這是什么意思呢，其實就是根據(jù)一個用戶干了個蠢事，找他上次干這個蠢事的時間。

lag（）這里填寫的參數(shù)就是時間，over（）中的參數(shù)和前面一樣，需要有partition來對用戶進行分組，order by來對時間進行排序。

lag其實是有第二個參數(shù)的，就是次數(shù)，如果寫2，就是問這個用戶上上次干這個蠢事的時間，當然不填的話，默認是1

需求9?King和West的時間偏移(后N行)

這個與前面的計算完全一樣，lag是往前，lead是往后，只管及這個就可以了。

這個函數(shù)其實還是挺常用的，看下面幾個例子就知道了。

知識點總結(jié)：

Lag和Lead函數(shù)可以在同一次查詢中取出同一字段的前N行的數(shù)據(jù)(Lag)和后N行的數(shù)據(jù)(Lead)作為 獨立的列。

在實際應(yīng)用當中，若要用到取今天和昨天的某字段差值時，Lag和Lead函數(shù)的應(yīng)用就顯得尤為重要。

exp_str是字段名稱。 offset是偏移量，即是上1個或上N個的值，假設(shè)當前行在表中排在第5行，則offset 為3，則表示我 們所要找的數(shù)據(jù)行就是表中的第2行(即5-3=2)。offset默認值為1。defval默認值，當兩個函數(shù)取上N/下N個值，當在表中從當前行位置向前數(shù)N行已經(jīng)超出了表的范圍時，lag()函數(shù)將defval這個參數(shù)值作為函數(shù)的返回值，若沒有指定默認值，則返回NULL，那么 在數(shù)學運算中，總要給一個默認值才不會出錯。

需求10：查詢出支付時間間隔超過100天的用戶數(shù)

首先還是那句話，count千萬別忘記用distinct，然后就是這個間隔100天應(yīng)該怎么表示，這里使用了一個函數(shù)datediff（），里面的參數(shù)是偏置列和支付時間列，可能用這個函數(shù)可以計算兩個時刻之間的距離

*需求11：查詢出每年支付時間間隔最長的用戶

這題還真是一層套一層，這里記住需要判斷l(xiāng)ag is not null，與上題不同，這里需要判斷最大的間隔時間，別忘了where是不能包含聚合函數(shù)的，那么就不能同上題去判斷，而是用rank（）函數(shù)來逆排序，找出最大值。

另外此題另一個難點在按年排序上。

第2節(jié)MySQL索引

2.1什么是索引

索引可以提升查詢速度，會影響where條件查詢（where xxx=''），order by（order byyyy）排序

索引是針對字段的，需要添加到字段上

索引在大量數(shù)據(jù)場景下效果明顯

2.2 常見索引分類

從索引的存儲結(jié)構(gòu)劃分：B Tree索引、Hash索引、fulltext全文索引、R Tree索引（了解）

從應(yīng)用層次劃分：主鍵索引、唯一索引、普通索引、復合索引

從索引的鍵值（字段）類型劃分：主鍵索引、輔助索引（二級索引）

從索引數(shù)據(jù)和內(nèi)容數(shù)據(jù)邏輯關(guān)系劃分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

索引的缺點也很明顯，當對表中的數(shù)據(jù)進行增加，修改，刪除的時候，索引要同時進行維護，數(shù)據(jù)量越大維護時間越長，所以還是不看了。

第3節(jié)MySQL視圖

3.1 什么是視圖

1. 視圖是一種虛擬表。

2. 視圖建立在已有表的基礎(chǔ)上, 視圖賴以建立的這些表稱為基表。

3. 向視圖提供數(shù)據(jù)內(nèi)容的語句為 SELECT 語句, 可以將視圖理解為存儲起來的 SELECT 語句.

4. 視圖向用戶提供基表數(shù)據(jù)的另一種表現(xiàn)形式

3.2 視圖的作用

權(quán)限控制時可以使用

????比如,某幾個列可以運行用戶查詢,其他列不允許,可以開通視圖 查詢特定的列, 起到權(quán)限控制的 作用

簡化復雜的多表查詢

????視圖本身就是一條查詢SQL,我們可以將一次復雜的查詢 構(gòu)建成一張視圖, 用戶只要查詢視圖就可以獲取想要得到的信息(不需要再編寫復雜的SQL)

????視圖主要就是為了簡化多表的查詢

3.3 視圖的使用

3.3.1 創(chuàng)建視圖

1）語法格式

2）創(chuàng)建一張視圖

3）查詢視圖 ,當做一張只讀的表操作就可以