SQL輕鬆入門（5）：視窗函式

01、前言

標題中有2個字讓我在初次接觸視窗函式時，真真切切明白了何謂”高階”?說來也是一番辛酸史!話說，我見識了視窗函式的強大後，便磨拳檫掌的要試驗一番，結果在查詢中輸入語句，返回的結果卻是報錯，What?聰明的你，猜猜為啥?

害，原因可不就是MySQL的版本不匹配唄，我的原裝是5.5，而視窗函式可運用的版本是MySQL8.0。經歷了解除安裝重灌，我對視窗函式的印象可是老深的啦!閒話到此，正題開始~

視窗函式的高階不僅體現在版本要求高，還體現在功能上的簡潔易懂。以Excel中vlookup作類比，視窗函式≈vlookup，group by+order by +關聯子查詢≈index+match，雖不完全貼切，但能助你理解即可。

本文所涉及知識點，框架如下：

02、什麼是視窗函式?

視窗函式，也叫OLAP函式(Online Anallytical Processing，聯機分析處理)，可以對資料庫資料進行實時分析處理。

基本語法: ‹視窗函式› over (partition by ‹用於分組的列名› order by ‹用於排序的列名›)。語法中的‹視窗函式›主要由專用視窗函式(rank、dense_rank和row_number等組成)與聚合函式(sum、avg、count、max和min等)作為視窗函式組成。

從視窗函式組成上看，它是group by 和 order by的功能組合，既然我們已經學了group by和order by，為什麼還要學視窗函式呢?group by分組彙總後改變了表的行數，一行只有一個類別，而partiition by則不會減少原表中的行數。

恰如視窗函式的組成，它同時具有分組和排序的功能，且不減少原表的行數。

03、視窗函式如何使用?

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

截圖示例中使用了專有視窗函式rank，從整體執行效果來看，語句十分簡潔。我們也可以理解：為什麼要叫“視窗”函式?因為partition by分組後的結果被稱為“視窗”，這裡的視窗不是指我們家裡的門窗，而是表示“範圍”的意思。

04、視窗函式分類和應用

從上例，我們可以清晰看到視窗函式的厲害之處，所以會在瞭解視窗函式組成部分的同時通過應用的講解來加深理解。

排序函式rank、dense_rank和row_number的區別在於並列的排名表現。

rank函式：這個例子中是5位，5位，5位，8位，也就是如果有並列名次的行，會佔用下一名次的位置。

dense_rank函式：這個例子中是5位，5位，5位，6位，也就是如果有並列名次的行，不佔用下一名次的位置。

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

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

截圖中紅色標註是為了說明掌握sql語句執行順序的重要性，能看懂報錯十分重要，有時候很容易忽略語句的執行順序，謹記!

topN的萬能模板：

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

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

彙總分析版塊中的常用聚合函式相信我們已掌握，那麼在視窗函式中，它們區別於專用視窗函式的一個點是括號裡需帶有對應的列名。

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

根據上面的觀察，我們可以解決業務中的累計求和問題。

“累計求和”問題的萬能模板是：

select 列1,列2, sum(列名) over (order by ‹用於排序的列名›) as 累計值的別名 from 表名;在開篇對比中，我有說到關聯子查詢，毫無疑問，接下來的問題和”每一組對比”相關，那麼如何在每個組裡比較?

通過上述2種方法，再一次驗證了視窗函式的簡潔高效，以後若是遇到每組比較的問題，要知道有2種解法，同時大腦中能反映出這2種語句，但執行可以選擇視窗函式。

視窗函式除了能解決以上的應用外，還可以實現移動平均，或者移動求和等，以移動平均為例：

紅色標註的就是移動平均的關鍵字，截圖是包括本行再往上2行，也可以變成數字4，那麼就是5行對比，重點可以觀測到相鄰數值之間的差異。

綜上，視窗函式所包含的內容絕不僅是上面這些，隨著我們在業務中的不斷打磨，我們需要不斷擴充自己的知識庫，平時要多總結學習。