本節書摘來自異步社區出版社《T-SQL性能調優秘笈——基于SQL Server 2012 窗口函數》一書中的第1章，第1.2節，作者： 【美】Itzik Ben-Gan，更多章節內容可以訪問云棲社區“異步社區”公眾號查看。

1.2　使用窗口函數的解決方案簡介

T-SQL性能調優秘笈——基于SQL Server 2012 窗口函數
本書前4章描述了窗口函數及其優化，所選素材偏重技術說明，雖然我自己覺得很吸引人，但可以想見，有些人會覺得有點沉悶。通常來說，人們在閱讀用窗口函數解決現實問題的內容時，會覺得比較有趣，本書將在最后一章滿足大家。只有當我們看到能如何用窗口函數解決難題時，才會真正認識到它們的價值。所以，我在思考如何說服你在讀到有趣的章節之前，能堅持讀完這些枯燥的技術說明而不中途放棄。也許我可以展示一個窗口函數解決方案示例。

這里演示的查詢包含：對一個在列中包含序列號的表進行查詢，在現有值中找到連貫的區間。這個難題也就是所謂的數據島問題。序列號可以是數值類型的、時間類型的（比較常見）或支持全排序的任意數據類型。序列號可以是唯一值，也可以是重復值。間隔可以是任何符合列類型（如，整型數1、整型數7、時間間隔為1天、時間間隔為2周等）的固定間隔。第5章會講述間隔的各種變化。這里只舉個簡單的案例，讓大家感受一下窗口函數是如何工作的——所以用數值序列，間隔為1，首先用下面的代碼來生成樣本數據：

在表T1中，可以看到col1的序列號并不連續。我們的任務是找到現有值的連續區間（也稱為數據島，）返回每個數據島的開始值和結束值，我們希望得到的結果如下：

如果我們好奇這類問題在現實中的使用場景，這里有很多實際例子。示例包括生成可用性報告，識別某種活動的持續時間（如，銷售活動），找到滿足一定要求的持續區間（如，股票高于或低于一定閾值的時間區間），識別車牌的使用范圍等。當前示例的目的非常簡單，我們可以專注在實現它的技術上。只須稍作調整，應用在簡單案例上的技術就可以應用在更加復雜的案例上，所以請把它看成是一個基于集合的解決方案帶來的挑戰。首先請找出一個能解決問題的方案，然后對表填充大量的數據行（假設10 000 000行）然后再試試剛才的解決方案，看看它將如何進行。完成這些，我們再來看我的解決方案。

在展示使用窗口函數的方案之前，我先展示其中一個用傳統語言結構實現的解決方案。特別地，我會展示使用子查詢的解決方案。為了解釋第一種解決方案的策略，先查看T1.col1序列的值，我增加了一個目前尚不存在的概念化的列，把它當做組標識符：

grp列尚未存在，從概念上來說，它的值唯一地標識一個數據島。這就意味著，對同一個島內的所有成員，它們的grp值相同，并與其他島內的成員的grp值不同。如果我們能設法對這樣的組標識符進行計算，我們就能把結果根據grp特性值進行分組，返回每組（島）中最大和最小的col1值。傳統語言結構中生成這個組標識符的方法是：針對col1的每個當前值，找到大于或等于當前值的最小的col1值，并且要求這個值后面沒有值。

舉例說明，按照上面的邏輯，試著找到相對于值2，col1的值大于或等于2的最小值，且要求其后面的值沒有連續，答案是3。現在，針對3，做同樣的查找，結果還是3，所以3是組標識符，數據島開始于2，結束于3。對于開始于11，結束于13的數據島，組成員的標識符都是13。從這里我們可以看到，一個數據島內的所有成員的組標識符，實際上就是數據島最后一個成員的值。

下面是實現這個概念的T-SQL代碼。

代碼執行后的輸出結果如下：

下面的部分就相當直觀了——根據上面的查詢定義表表達式，在外部查詢中，根據組標識符進行分組，返回每組的最大和最小的col值，如下。

在這個解決方案里，有兩個主要問題。一、這里遵循的邏輯有點復雜。二、運行非常緩慢。我暫時不想開始討論查詢執行計劃——后面的篇幅有很多這方面的討論——在這兒可以告訴大家，針對表中的每一行，SQL Server都幾乎執行了兩次完整的數據掃描。現在可以想象對于10 000 000條記錄組成的序列，嘗試轉化它對應的工作量會有多少了。需要處理的行的數量只有一個詞形容——巨大。

另一個解決方案也是計算組標識符，不同之處是，使用窗口函數進行。解決方案的第一步是使用ROW_NUMBER函數基于col1排序計算行號。本書后面會提供ROW_NUMBER的細節。目前，只要知道它在分區中，按照給定的順序，從1開始，逐一遞增，產生唯一的整數就足夠了。

牢記上面的知識，下面的查詢按照col1排序，返回的col1的值和行號。

現在，我把注意力集中到兩個序列上。一個（col1）是不連貫的，另一個（rownum）是連貫的。記住這一點，然后嘗試發現在一個數據島內，二者有什么獨特的關系。在數據島內，兩個序列都以固定間隔在增長，因此，二者的差異是一個常數。在另一個島內，col1的增量超過1，而rownum的增量還是1，所以差異在變大。換句話說，二者的差異是常數，不同數據島內的數值不同。運行下面的查詢來計算其差異。

我們可以看到，這個差異滿足我們對組標識符的兩個需求，因此，可以把它當做組標識符使用。其他內容都與之前的解決方案相同，即是說，把行按組標識符進行分組，返回每組中最小和最大的col1值，如下所示。

請觀察一下，這樣的解決方案多么清晰和簡單。同時，在代碼上加上注釋，幫助那些第一次讀代碼的人更好地了解解決方案，也是個不錯的主意。

這個解決方案還很高效。與前一解決方案相比，其所涉及的行的處理數量微不足道。它僅僅包含一個在col1上的排序索引掃描和一個持續遞增計數器的迭代器。我測試過這條查詢的性能，它在10 000 000條記錄組成的序列上僅運行了10秒鐘。前一解決方案的運行時間就要長得多。

希望關于使用窗口函數的解決方案的簡介，能足以吸引你，讓你看到它們包含的強大功能。現在，我們要返回繼續學習窗口函數技術了，在書的后面部分，我們還有機會看到更多的示例。

本文僅用于學習和交流目的，不代表異步社區觀點。非商業轉載請注明作譯者、出處，并保留本文的原始鏈接。

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的《T-SQL性能调优秘笈——基于SQL Server 2012 窗口函数》——1.2　使用窗口函数的解决方案简介...的全部內容，希望文章能夠幫你解決所遇到的問題。

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