SQL輕松入門（5）：窗口函數(shù)

作者介紹

@知乎：鯨歌

電商行業(yè)數(shù)據(jù)分析師；

為頭部品牌提供數(shù)據(jù)咨詢服務(wù)；

熱衷學(xué)習(xí)與分享；

“數(shù)據(jù)人創(chuàng)作者聯(lián)盟”成員。

01

前言

標(biāo)題中有2個字讓我在初次接觸窗口函數(shù)時(shí)，真真切切明白了何謂”高級”？說來也是一番辛酸史！話說，我見識了窗口函數(shù)的強(qiáng)大后，便磨拳檫掌的要試驗(yàn)一番，結(jié)果在查詢中輸入語句，返回的結(jié)果卻是報(bào)錯，What？聰明的你，猜猜為啥？

害，原因可不就是MySQL的版本不匹配唄，我的原裝是5.5，而窗口函數(shù)可運(yùn)用的版本是MySQL8.0。經(jīng)歷了卸載重裝，我對窗口函數(shù)的印象可是老深的啦！閑話到此，正題開始~

窗口函數(shù)的高級不僅體現(xiàn)在版本要求高，還體現(xiàn)在功能上的簡潔易懂。以Excel中vlookup作類比，窗口函數(shù)≈vlookup，group by+order by +關(guān)聯(lián)子查詢≈index+match，雖不完全貼切，但能助你理解即可。

本文所涉及知識點(diǎn)，框架如下：

02

什么是窗口函數(shù)？

窗口函數(shù)，也叫OLAP函數(shù)（Online Anallytical Processing，聯(lián)機(jī)分析處理），可以對數(shù)據(jù)庫數(shù)據(jù)進(jìn)行實(shí)時(shí)分析處理。

基本語法: 窗口函數(shù) over (partition by 用于分組的列名 order by 用于排序的列名 )。語法中的 窗口函數(shù) 主要由專用窗口函數(shù)（rank、dense_rank和row_number等組成）與聚合函數(shù)（sum、avg、count、max和min等）作為窗口函數(shù)組成。

從窗口函數(shù)組成上看，它是group by 和 order by的功能組合，既然我們已經(jīng)學(xué)了group by和order by，為什么還要學(xué)窗口函數(shù)呢？group by分組匯總后改變了表的行數(shù)，一行只有一個類別，而partiition by則不會減少原表中的行數(shù)。

恰如窗口函數(shù)的組成，它同時(shí)具有分組和排序的功能，且不減少原表的行數(shù)。

03

窗口函數(shù)如何使用？

以截圖問題為例：查看每個班級的排名情況

截圖示例中使用了專有窗口函數(shù)rank，從整體執(zhí)行效果來看，語句十分簡潔。我們也可以理解：為什么要叫“窗口”函數(shù)？因?yàn)閜artition by分組后的結(jié)果被稱為“窗口”，這里的窗口不是指我們家里的門窗，而是表示“范圍”的意思。

04

窗口函數(shù)分類和應(yīng)用

從上例，我們可以清晰看到窗口函數(shù)的厲害之處，所以會在了解窗口函數(shù)組成部分的同時(shí)通過應(yīng)用的講解來加深理解。

排序函數(shù)rank、dense_rank和row_number的區(qū)別在于并列的排名表現(xiàn)。

rank函數(shù)：這個例子中是5位，5位，5位，8位，也就是如果有并列名次的行，會占用下一名次的位置。

dense_rank函數(shù)：這個例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。

row_number函數(shù)：這個例子中是5位，6位，7位，8位，也就是不考慮并列名次的情況，和Excel中的row（）函數(shù)一樣，依據(jù)順序排列下去。

topN問題：在日常工作中十分常用，以查找每個學(xué)生成績最高的2個科目為例：

截圖中紅色標(biāo)注是為了說明掌握sql語句運(yùn)行順序的重要性，能看懂報(bào)錯十分重要，有時(shí)候很容易忽略語句的運(yùn)行順序，謹(jǐn)記！

topN的萬能模板：

select * from (select *, row_number() over (partition by 要分組的列名 order by 要排序的列名 desc) as ranking from 表名) as a where ranking = N;

如果是最小的話，只需變動order by的排序方式即可。

匯總分析版塊中的常用聚合函數(shù)相信我們已掌握，那么在窗口函數(shù)中，它們區(qū)別于專用窗口函數(shù)的一個點(diǎn)是括號里需帶有對應(yīng)的列名。

上圖以2列突出顯示，可以觀察到sum（）數(shù)值不斷累加，avg（）也是，由此可以說明，窗口函數(shù)中可以看到1列的數(shù)值動態(tài)變化過程。

根據(jù)上面的觀察，我們可以解決業(yè)務(wù)中的累計(jì)求和問題。

“累計(jì)求和”問題的萬能模板是：

select 列1,列2, sum(列名) over (order by 用于排序的列名 ) as 累計(jì)值的別名 from 表名;在開篇對比中，我有說到關(guān)聯(lián)子查詢，毫無疑問，接下來的問題和”每一組對比”相關(guān)，那么如何在每個組里比較？

通過上述2種方法，再一次驗(yàn)證了窗口函數(shù)的簡潔高效，以后若是遇到每組比較的問題，要知道有2種解法，同時(shí)大腦中能反映出這2種語句，但執(zhí)行可以選擇窗口函數(shù)。

窗口函數(shù)除了能解決以上的應(yīng)用外，還可以實(shí)現(xiàn)移動平均，或者移動求和等，以移動平均為例：

紅色標(biāo)注的就是移動平均的關(guān)鍵字，截圖是包括本行再往上2行，也可以變成數(shù)字4，那么就是5行對比，重點(diǎn)可以觀測到相鄰數(shù)值之間的差異。

綜上，窗口函數(shù)所包含的內(nèi)容絕不僅是上面這些，隨著我們在業(yè)務(wù)中的不斷打磨，我們需要不斷擴(kuò)充自己的知識庫，平時(shí)要多總結(jié)學(xué)習(xí)。

想了解更多數(shù)據(jù)知識也歡迎看，7位大廠數(shù)據(jù)產(chǎn)品寫的《大數(shù)據(jù)實(shí)踐之路：數(shù)據(jù)中臺+數(shù)據(jù)分析+產(chǎn)品應(yīng)用》這本書。

關(guān)鍵詞： SQL輕松入門（5）窗口函數(shù) mysql groupby excel