窗口函數是指什么？為什么要用它？怎么用？有哪些經典的案例？

1）窗口函數是什么？

窗口函數是OLAP(online analytical processing)，顧名思義，可以對數據庫內的數據實時分析處理。通俗來講，把一簇大的數據拆分成多個范圍，把一面墻劃分成多個窗口。

<窗口函數>一般有以下兩種，

1、專用窗口函數，包括rank，dense_rank，row_number等。

2、聚合函數，如sum，avg，count，max，min等。

特點：

1、窗口函數有特定的語法結構，使用起來就像一個獨立的子句。

<

2、窗口函數一般用于對where或者group by子句處理后的數據進行操作，（很明顯按照sql執行順序）所以窗口函數原則上只能寫在select子句中。

窗口函數有什么用？

2）窗口函數怎么使用？

專用窗口函數

select

#以成績表score為例，要求“在每個課程內按成績排名”

rank+partition by和group by子句的區別：前者是分組排名后總行數不變，后者分組匯總后總行數改變為幾大類別。前者分組排序后返回新列且有排好序的數值，后者分組后產生一個虛擬表用于后續處理。總結如下：

簡單來說，窗口函數的功能：

同時實現分組和排序的作用；

不減少原表的行數；

用在select子句后，語法結構；

select

3）幾個專用窗口函數的區別：

rank正常排序，并列的次序會占用位置；

dense_rank緊密排序，并列的次序不會占用位置，依然按自然數順延；

row_number行數排序，完全不考慮并列的情況，按照行數依次排序；

面試經典排名問題（分組功能可選）

在庫中score表中的內容，記錄了每個學生所選的課程，和對應的成績。

#現在需要按成績來排名，如果兩個分數相同，那么排名要是并列的且不占位置，類似1，1，1，2；

面試經典topN問題

比如如何找到每個類別下的number1？如何找到每個類別下XX方面排在前面的5個商品？

實質就是常見的：分組取最大值，最小值，每組最大的N條記錄（topN記錄）。

在庫中score表中的內容，記錄了每個學生所選的課程，和對應的成績。

#1每組最大記錄：按課程號分組取成績最大值所在行的全部數據;

#2每組最小記錄：按課程號分組取成績最小值所在行的全部數據;

#3每組最大的N條記錄：查詢每個學生各科成績前兩名的記錄

解題思路：針對1，2兩題，group by子句和匯總函數不能得到全部的行和列數據，因此考慮使用“匯總函數+子查詢”結合使用得到完整的數據。

解題思路：針對3題，同樣的group by子句不能得到全部的行和列數據，而且order by+limit也不適用于分別取出每一組內的限定行數。

而由上面的對比可知，窗口函數rank() 能夠實現在分組的同時保留全部數據且進行排序，因而topN適用窗口函數一步到位：

正確寫法，窗口函數使用后再作為表子查詢

窗口函數只能放在select子句中，因此直接在where子句中使用窗口函數中的字段作為過濾條件，會報錯。

顯然使用窗口函數解決topN問題更加流暢，上面1，2題同樣可以按照3題的思路進行解決。

select

4）聚合函數作為窗口函數

將聚合函數替代專用窗口函數（rank（））的位置即可。語法如下：：

select

比如0004號，在使用sum窗口函數后的結果，是對0001，0002，0003，0004號的成績求和，若是0005號，則結果是0001號~0005號成績的求和，以此類推。

從上圖執行結果來看，聚合函數作為窗口函數，是對自身所處位置，以及位于自身記錄以上的數據進行sum，avg，max，min，count進行處理的結果。

注意：這里的自身記錄，以及自身記錄以上....一定要是有一個次序比如（0001，0002，0003），否則并列的話會都當作自身記錄，會在一起處理，幾行數據也會一致。下面有示例圖。

這樣使用窗口函數有什么用呢？

聚合函數作為窗口函數，可以在每一行的數據里直觀的看到，截止到本行數據，統計數據是多少（最大值、最小值等）。同時可以看出每一行數據，對整體統計數據的影響。

那么問題是？對使用聚合函數作為窗口函數時，+partition by分組后，此時還是對當前記錄和記錄之上的數據進行聚合處理？事實上相當于是對“組內求平均”，因為此時組內字段值是一樣的，（也即都算作當前記錄），一組作為一個框架處理。示例如下：score表中按課程號分組求平均成績...

如何在每個組內進行比較

問題：以score表為例，查找單科成績高于該科目平均成績的學生名單

解題思路：第一思路是前面用過的“聚合函數+關聯子查詢”，也適合組內比較或者組內最大最小值一類。 第二思路是使用“聚合函數做窗口函數”，同時實現聚合，分組，排序等功能。如下：

這一部分理解上 出了偏差，一直糾結于成績大于avg_score上，認為avg_score是多個值，混淆到“列子查詢”上去了。 此處是表內比較，數據一行一行執行的。——說明對sql執行順序和單步運行結果仍不夠熟悉；

(篩選條件可以是常量，字段，表達式，函數 。。)

5）窗口函數的移動平均（rows...preceeding）

語法如下：

select

這樣使用窗口函數有什么用呢？

由于這里可以通過preceding關鍵字調整作用范圍，在以下場景中非常適用：

在公司業績名單排名中，可以通過移動平均，直觀地查看到與相鄰名次業績的平均、求和等統計數據。

直接使用聚合函數avg的窗口函數 來舉例說明，以score表為例：

#使用聚合函數avg的窗口函數查詢學生的平均成績（未移動平均前且用來排序的字段有并列值）

#使用聚合函數avg的窗口函數查詢學生的平均成績（未移動平均前且用來排序的字段唯一）

#使用聚合函數avg的窗口函數查詢學生的平均成績（移動平均后）

這三個不同的查詢結果體會一下。再看下面的例子，

這張圖中學號按次序排列，按照移動平均rows 2 preceding執行后得到的。每一行得到的結果，都是當前行和前面2行的平均（共3行）。想要計算當前行與前n行（共n+1行）的平均時，只要調整rows…preceding中間的數字即可。

框架：在移動平均中，被選出的數據可以理解為構成一個“框架”。數據的運算發生在這個框架之中。

窗口函數——知識點總結

1、窗口函數的語法

<

1）其中<窗口函數>的位置，可使用以下兩種函數：①專用窗口函數，如rank，dense_rank，row_number等適合“經典排序問題”“排序topN問題”等；②聚合函數，如sum，avg，count，max，min等適合“組內比較、篩選問題”等；

2）partition by子句可以省略，也就是不指定分組。

2、窗口函數有以下功能：

1）可同時實現分組（partition by）和排序（order by）的功能；

2）且不減少原表的行數，所以經常用來在每組內排名；

3、注意事項

1）窗口函數原則上只能寫在select子句中；

2）因為只能放在select子句中，所以如果需要對窗口函數的結果進行條件篩選，而SQL主查詢中select執行順序靠后，因此可以考慮把select 窗口函數改成一個表子查詢。執行順序就變成了第一順位。

4、應用場景

總結

以上是生活随笔為你收集整理的可以对窗口函数之后再加条件_SQL 窗口函数——解决实际问题的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。