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

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

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

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

MySQL從8.0開(kāi)始支持窗口函數(shù)倚评，有的也叫分析函數(shù)（處理相對(duì)復(fù)雜的報(bào)表統(tǒng)計(jì)分析場(chǎng)

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

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

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

其中焕襟，over是關(guān)鍵字陨收，用來(lái)指定函數(shù)執(zhí)行的窗口范圍，包含三個(gè)分析子句：分組(partition by)子句，排序(order by)子句务漩，窗口(rows)子句拄衰，如果后面括號(hào)中什么都不寫，則意味著窗口包含滿足where條件的所有行饵骨，窗口函數(shù)基于所有行進(jìn)行計(jì)算翘悉；如果不為空，則支持以下語(yǔ)法來(lái)設(shè)置窗口：

over這個(gè)窗口函數(shù)還是很常見(jiàn)的居触，后面的括號(hào)里可以填寫三類分析子句妖混，注意這里的分組不是group by哦。

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

B: 分組的字段名稱

C: 排序的字段名稱

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

這上面講解了一下rows這個(gè)語(yǔ)句的計(jì)算方式轮洋，其實(shí)看一遍就明白了制市。

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

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

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

這三個(gè)之前在刷題時(shí)遇到過(guò)弊予，三個(gè)都是排名用的息堂，不同之處在于相同數(shù)值的排序，rank是相同的一樣排名块促，后面一個(gè)會(huì)跳著排荣堰；dense——rank也是相同的一樣排名，但后面一個(gè)會(huì)連著排竭翠，row_number就是相同的也不同排名振坚。

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

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

sum()屎鳍，count()，avg()问裕，max()逮壁，min()

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

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

這點(diǎn)還是非常明顯的巍杈，后面也出現(xiàn)過(guò)

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

3?排序(order by)：按照哪些字段進(jìn)行排序筷畦，窗口函數(shù)將按照排序后的記錄順序進(jìn)行編號(hào)词裤，可以和partition子句配合使用，也可以單獨(dú)使用。如果沒(méi)有partition子句吼砂，數(shù)據(jù)范圍則是整個(gè)表的數(shù)據(jù)行作媚。

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

下面開(kāi)始來(lái)實(shí)例

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

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

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

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

1.2.1累計(jì)計(jì)算函數(shù)

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

這里計(jì)算每個(gè)月的總額吧兔，首先上述數(shù)據(jù)里面是沒(méi)有月度數(shù)據(jù)的，其次如何按照月來(lái)分別計(jì)算每個(gè)月的總額也是一個(gè)問(wèn)題袍嬉。select中的兩個(gè)值應(yīng)該怎么填境蔼？

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

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

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

==首先問(wèn)月的銷售總額箍土，不用想太多，直接用sum求和計(jì)算就可以罐监；在此同時(shí)問(wèn)年的累計(jì)總和就需要用窗口函數(shù)吴藻，滑動(dòng)月份窗口來(lái)計(jì)算（4.23

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

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

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

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

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

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

移動(dòng)平均：avg()over()

需求3?查詢出2019年每個(gè)月的近三月移動(dòng)平均支付金額

這里說(shuō)要每個(gè)月前3個(gè)月的平均支付讥耗，其實(shí)剛看到是有些蒙的有勾，但是不礙事，我們先寫子查詢古程，子查詢我們不考慮移動(dòng)平均三個(gè)月什么亂七八糟的蔼卡，我們只要兩列，月挣磨，每月支付累計(jì)雇逞；這樣是不是就簡(jiǎn)單多了。

然后我們?cè)趯憇elect茁裙，移動(dòng)平均使用avg（）over（）塘砸，這里還是需要order by來(lái)對(duì)月份進(jìn)行排名，然后再利用over當(dāng)中的第三個(gè)參數(shù)rows來(lái)進(jìn)行前3個(gè)月的判定晤锥。

前面對(duì)rows函數(shù)也講過(guò)使用方式掉蔬，這里需要寫2 preceding and current row，即前兩行加其自身矾瘾，另外女轿，在order by和rows參數(shù)之間竟然是沒(méi)有逗號(hào)的。

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

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

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

這里的其他部分我都寫對(duì)了戈泼，但是這個(gè)substr是給我整懵了婿禽，沒(méi)學(xué)過(guò)這個(gè)函數(shù)啊

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

1.2.2 排序函數(shù)

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

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

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

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

==遇到排名問(wèn)題大猛，首先把排名需要的數(shù)據(jù)列出來(lái)扭倾，而排名本身就是一個(gè)窗口函數(shù)，并且有三類挽绩，三類效果不同（4.23

首先第一點(diǎn)膛壹，count進(jìn)行計(jì)數(shù)時(shí)，千萬(wàn)不能忘記使用distinct唉堪；第二模聋，排名這類專有的窗口函數(shù)的排名參數(shù)是在over（）中的order by里面給出的，不是在rank里面唠亚；第三這里推測(cè)這個(gè)substring是可以直接調(diào)出具體日期的链方，不過(guò)誰(shuí)能告訴我，上一題里沒(méi)有ing這里又有灶搜。

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

==這里沒(méi)考慮支付數(shù)為0的情況工窍，然后分組問(wèn)題是ntile函數(shù)，并且也需要開(kāi)一個(gè)窗口來(lái)按支付金額排序前酿。（4.23

這一題的問(wèn)題在于如何分成固定的組數(shù)患雏，這里給出了一個(gè)新函數(shù)，ntile罢维，它可以分為固定組數(shù)久信；另外上一題的substr果然寫錯(cuò)了卿操，應(yīng)該是不帶ing的掌敬。

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

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

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

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

1.2.3偏移分析函數(shù)

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

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

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

其實(shí)這個(gè)耐下心去看還是挺好理解的磅叛，雖然這里的代碼寫錯(cuò)了兩個(gè)屑咳，但是不影響理解。

首先明確lag（）over（）是用來(lái)向上求偏置的弊琴，這是什么意思呢兆龙，其實(shí)就是根據(jù)一個(gè)用戶干了個(gè)蠢事，找他上次干這個(gè)蠢事的時(shí)間敲董。

lag（）這里填寫的參數(shù)就是時(shí)間紫皇，over（）中的參數(shù)和前面一樣，需要有partition來(lái)對(duì)用戶進(jìn)行分組腋寨，order by來(lái)對(duì)時(shí)間進(jìn)行排序聪铺。

lag其實(shí)是有第二個(gè)參數(shù)的，就是次數(shù)萄窜，如果寫2铃剔，就是問(wèn)這個(gè)用戶上上次干這個(gè)蠢事的時(shí)間，當(dāng)然不填的話查刻，默認(rèn)是1

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

這個(gè)與前面的計(jì)算完全一樣键兜，lag是往前，lead是往后穗泵，只管及這個(gè)就可以了普气。

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

知識(shí)點(diǎn)總結(jié)：

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

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

exp_str是字段名稱。 offset是偏移量榆浓，即是上1個(gè)或上N個(gè)的值于未，假設(shè)當(dāng)前行在表中排在第5行，則offset 為3陡鹃，則表示我 們所要找的數(shù)據(jù)行就是表中的第2行(即5-3=2)烘浦。offset默認(rèn)值為1。defval默認(rèn)值萍鲸，當(dāng)兩個(gè)函數(shù)取上N/下N個(gè)值闷叉，當(dāng)在表中從當(dāng)前行位置向前數(shù)N行已經(jīng)超出了表的范圍時(shí)，lag()函數(shù)將defval這個(gè)參數(shù)值作為函數(shù)的返回值脊阴，若沒(méi)有指定默認(rèn)值握侧，則返回NULL，那么 在數(shù)學(xué)運(yùn)算中嘿期，總要給一個(gè)默認(rèn)值才不會(huì)出錯(cuò)品擎。

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

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

*需求11：查詢出每年支付時(shí)間間隔最長(zhǎng)的用戶

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

另外此題另一個(gè)難點(diǎn)在按年排序上蓖救。

第2節(jié)MySQL索引

2.1什么是索引

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

索引是針對(duì)字段的循捺，需要添加到字段上

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

2.2 常見(jiàn)索引分類

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

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

從索引的鍵值（字段）類型劃分：主鍵索引叉谜、輔助索引（二級(jí)索引）

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

索引的缺點(diǎn)也很明顯，當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加停局，修改很钓，刪除的時(shí)候，索引要同時(shí)進(jìn)行維護(hù)董栽，數(shù)據(jù)量越大維護(hù)時(shí)間越長(zhǎng)码倦，所以還是不看了。

第3節(jié)MySQL視圖

3.1 什么是視圖

1. 視圖是一種虛擬表锭碳。

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

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

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

3.2 視圖的作用

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

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

簡(jiǎn)化復(fù)雜的多表查詢

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

????視圖主要就是為了簡(jiǎn)化多表的查詢

3.3 視圖的使用

3.3.1 創(chuàng)建視圖

1）語(yǔ)法格式

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

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