本節書摘來自異步社群出版社《t-sql性能調優秘笈——基于sql server 2012 視窗函數》一書中的第1章，第1.4節，作者： 【美】itzik ben-gan，更多章節内容可以通路雲栖社群“異步社群”公衆号檢視。
t-sql性能調優秘笈——基于sql server 2012 視窗函數
并不是所有的查詢子句都支援視窗函數，相反，僅僅select和order by子句支援視窗函數。為了幫助大家了解這個限制的原因，我首先要解釋查詢邏輯處理的概念。然後，我會介紹支援視窗函數的子句，最後，解釋如何在其他子句中避開限制。
查詢邏輯處理從概念性的角度描述select查詢是如何根據邏輯語言的設計進行判斷的。它描述了怎樣由查詢的輸入表，經由一系列步驟和階段，直到查詢的最終結果的過程。注意我的用詞“查詢邏輯處理”，我指的是查詢判斷的概念性方式——不一定是sql server處理查詢的實體方式。作為優化的一部分，sql server會走捷徑，會重新安排某些步驟的順序，及使用它認為有助性能提高的其他手段。但前提是，它确定會按照查詢邏輯處理在查詢請求上的規則，提供正确的輸出。
查詢邏輯處理對作為輸入的一張或多張表（行集）進行處理，輸出傳回一張表，上一個步驟的輸出即成為下一個步驟的輸入。
圖1-6是一個流程圖，顯示sql server 2012的查詢邏輯處理流程。
請注意，當編寫查詢語句時，在輸入順序上，select子句是最早輸入的，但觀察查詢的邏輯處理順序，它幾乎是最後處理的——僅僅排在order by子句的前面。
關于查詢邏輯處理，還有很多可以提及的内容，但本書就不涉及這些内容了。為了便于本書後續内容的讨論，記住不同的子句的判斷順序很重要，順序如下（粗體表示允許視窗函數出現的階段）：
1． from
2． where
3． group by
4． having
5． select
5-1．evalute expressions(判斷表達式)
5-2．删除重複資料
6． order by
7． offset-fetch/top
了解查詢的邏輯處理以及邏輯處理順序，就使我們明白了視窗函數隻能出現在特定子句中這個限制的背後動機。
如圖1-6所示，隻有select和order by子句直接支援視窗函數。做這個限制的原因是為了避免二義性，是以把（幾乎是）查詢的最終結果當作視窗的起點。如果視窗函數可以早于select階段出現，那麼通過一些查詢表單會無法得到正确的結果。我通過一個示例來展示這種二義性問題。首先運作下面的代碼建立表t1，并在其中填入樣本資料：
假定視窗函數可以出現在select階段之前——例如，在where階段。那麼看看下面的查詢，試着找出結果中col1的值：
在我們認為答案顯而易見是c、d、e前，請考慮一下sql中的同時發生概念。同時發生概念指的是，從概念上來說，同一個邏輯階段裡的所有表達式是同時判斷的，這就意味着，順序對表達式的判斷并不重要。按照這個思路，從語義上來說，下面的查詢與上面的查詢是一樣的：
現在，我們找到的正确答案是什麼？是c、d、e，還是僅僅是c？
這就是我說的二義性示例。通過限制視窗函數，使其隻出現在select和order by查詢子句中，就排除了這種二義性。
觀察圖1-6，我們會注意到，在select階段，步驟5-1（判斷表達式）支援視窗函數，這一步驟在5-2（去重）之前進行。了解其中的微妙之處很重要，我會闡述為什麼。
下面的查詢從員工表中傳回所有員工的empid和country特性：
下一步，檢查下面的查詢，試着在執行查詢前，猜一猜輸出結果：
有些人以為會得到這樣的輸出：
實際上，輸出如下所示：
現在考慮一下：查詢中的row_number函數和select清單表達式都是在步驟5-1中判斷——早于步驟5-2中的删除重複資料。row_number函數配置設定了9個行号給9個員工行，是以distinct子句發現沒有重複記錄可以删除。
當我們認識到這一點并了解了查詢邏輯處理對不同的元素的處理順序時，我們就可以想出解決方案。例如，可以基于查詢定義一個表表達式，僅僅傳回不同的國家，在删除重複資料完成後，用外部查詢配置設定行号，如下所示：
現在，大家可以想想用其他方法來解答問題，是否還有比上面更簡單的方法？
視窗函數在select或order by階段判斷，實際上意味着為計算定義的視窗，在應用進一步的限制之前，是前面階段完成後查詢的行的中間形式，即應用了所有的表操作符（如，聯接）的from之後，在where篩選之後，在分組和分組篩選之後。以下面的查詢作為示例：
首先判斷from子句，進行表的聯接。随後篩選了2007年的訂單，這些選中的訂單按照員工号進行分組。直到這時，才判斷select清單中的表達式，包括rank函數，它基于合計銷量按降序進行計算。如果select清單中有其他視窗函數，它們會使用同樣的結果集作為起始點。現在回想對于視窗函數替代方案的讨論（例如，子查詢），其資料開始點是從頭開始的——意味着對于外部查詢中的設定邏輯，我們必須在每個内部查詢中重複設定一遍，導緻代碼冗長很多。
前面解釋了查詢邏輯處理的各個階段中，不允許視窗函數的判斷早于select子句的原因。但如果我們需要根據視窗函數的計算結果進行篩選或分組該怎麼辦？解決方法是使用像cte那樣的表表達式或派生表。在一個查詢的select清單中調用視窗函數，給計算配置設定一個别名，根據這個查詢定義一個表表達式，當需要時，就可讓外部查詢指向這個别名。
下面的示例展示如何用一個cte來篩選視窗函數的結果：
在修改語句中，視窗函數不能出現，因為修改語句不支援select和order by子句。但在某些情況下，需要在修改的語句中使用視窗函數。用表表達式也可以滿足這個需求，因為t-sql支援通過表表達式修改資料。我用一個update示例來示範其能力。首先，運作下面的代碼建立一個叫t1的表，其中有col1和col2兩列，然後填充一些樣品資料：
這裡給col2中提供了明确的值，col1的值就是預設的null。
假設這個表代表一種資料品質有問題的情形。表中沒有強制實作鍵，是以無法對各行進行唯一性識别，我們需要對所有行的col1指定唯一值，考慮在update語句中使用row_number函數，如下所示：
但回想一下，這是不允許的。變通方案是在t1上寫一個查詢，傳回col1和一個基于row_number函數的表達式（把它稱為rownum），基于這個查詢定義一個表達式；最後，在cte上用一個外部update語句，把rownum值賦給col1，如下所示：
查詢t1，我們可以看到所有行的col1都有唯一值。
本文僅用于學習和交流目的，不代表異步社群觀點。非商業轉載請注明作譯者、出處，并保留本文的原始連結。