之是以要提出窗體這個概念，由于這種基于窗體或分區的又一次計算在實際工作應用範圍比較廣泛。比如。假設我們要對每一個班級中的學生按成績進行排序，在對第1個班級排序完畢後，對第2個班級進行排序時編号須要又一次從1開始。在SQL Server 2005之前。像這種排序方式實作起來是比較煩瑣的。能夠說，對新窗體又一次啟動計算是窗體計算的重要特點。
為支援窗體計算，SQLServer提供了OVER子句和窗體函數。
窗體函數在MSDN Library中被翻譯為開窗函數。
盡管“開窗函數”了解起來并不如“窗體函數”easy，可是它描寫叙述了資料窗體變化後又一次啟動計算這樣一個動作，是以我們尊重MSDN Library中的翻譯。在興許的介紹中将使用“開窗函數”這一名詞。
窗體計算的兩個主要應用就是對每組内的資料進行排序和聚合計算。是以，開窗函數也被分為排名開窗函數和聚合開窗函數。排名開窗函數如ROW_NUMBER( )、RANK( )。聚合開窗函數如AVG( )、SUM等。
進行排名計算時，OVER子句的文法格式例如以下：
OVER ( [ PARTITION BY value_expression , ... [ n ]]
<ORDER BY_Clause> )
PARTITION BY value_expression
指定對對應FROM子句生成的行集進行分區所根據的列。
開窗函數分别應用于每一個分區，并為每一個分區又一次啟動計算。value_expression僅僅能引用通過FROM子句可用的列。不能引用選擇清單中的表達式或别名。value_expression能夠是清單達式、标量子查詢、标量函數或使用者定義的變量。
<ORDER BY 子句>
指定應用排名開窗函數的排序順序。僅僅能引用通過FROM子句可用的列。可是不同通過指定整數來表示選擇清單中列名稱或列别名的位置。
以下我們将以表9-1所看到的的Students表為例，進行介紹。像Students表這種資料結構設計。相對于資料庫存儲而言是比較合理的，由于我們不可能為每一個班級建立一個表，但确實又存在像為每一個班級中的學生成績進行排序或為學生編号這種實際需求。SQL Server的窗體計算技術就有效攻克了二者之間的沖突。
從SQL Server2005開始，提供了4個排名函數。各自是：ROW_NUMBER( )、RANK( )、DENSE_RANK( )和NTILE( )。它們能夠為分區中的每一行傳回一個排名值。ROW_NUMBER( )用于按行進行編号，RANK( )和DENSE_RANK( )用于按指定順序排名，NTILE( )用于對資料進行分區。
ROW_NUMBER( )傳回分區内行的序列号，每一個分區的第一行從1開始。比如，以下的語句指定按ClassID進行分區。并按StudentName進行排序編号。查詢結果如表9-2所看到的。
SELECT ClassID, StudentName, Achievement,
ROW_NUMBER() OVER(PARTITION BY ClassID ORDER BY StudentName) ASRowNumber
FROM Students;
表9-2 按班級分區、按學生姓名進行編号
ClassID
StudentName
Achievement
RowNumber
1
Andrew
99.00
Grace
2
Janet
75.00
3
Margaret
89.00
4
Michael
72.00
Robert
91.00
Steven
86.00
Ann
94.00
Ina
80.00
Ken
92.00
Laura
為了了解SQL Server中排名函數的工作原理，我們來看一下查詢優化器為查詢生成的運作計劃。如圖9-1所看到的。
圖9-1 為ROW_NUMBER( )生成的運作計劃
由上圖能夠看出，為了計算排名。優化器首先按分區列排序。然後再對分區内行按ORDER BY子句指定的列排序。
假設事先為表建立了符合該排序條件的索引。則會直接掃描該索引檔案。不再進行排序。
“序列射影”運算符的工作是負責計算排名。“段”運算符用于确定分組邊界。
二者互相協調工作，來确定每一行的排名值。
“段”運算符在記憶體中會保留一行，用來與下一行的PARTITION BY列值進行比較。
對于表中的第一行。“段”運算符自然會發送true信号。對于後面的行，直到PARTITIONBY列值有變化之前，會一直發送false信号。
假設PARTITION BY列值發生了變化，說明已經到了下一個分區。“段”運算符會再次發送true信号。“序列射影”運算符在接收到true信号後，會重置排名值。
假設“序列射影”運算符接收到的是false信号，它會确認目前輸入行的排序值是否不同于上一行。假設不同。則按排名函數所訓示的遞增排名值。自然，在該示範樣例中，由于ROW_NUMBER( )函數須要為每一行遞增值。是以。這個排序值比較步驟在該示範樣例中是不存在的。可是。對于像RANK( )和DENSE_RANK( )函數。在運作計劃中還會有另外一個“段”運算符，用于比較排序值是否有變化，以确定是否遞增排名值。此問題我們在以下還會有介紹。
ROW_NUMBER( )函數用于編号，它與排名具有不同的概念。比如，由表9-1能夠看出，班級1中的Grace和Andrew的成績同樣。都是99分。假設使用ROW_NUMBER( )函數編号，有兩種編号方案可供選擇：一種是Grace第1、Andrew第2。還有一種是Andrew第1、Grace第2。這盡管都是正确的。它具有不确定性。
而排名則不同了。它具有确定性，同樣的排序值總是被配置設定同樣的排名值。Grace和Andrew在排名的情況下都應當是第1。也就是我們常說的并列第1。那他們兩人之後的名次是什麼呢？是第2還是第3呢？從兩人并列第1的角度講，他們兩人之後的名次應當是第2。這也是DENSE_RANK()函數的排名方式。前面已經有2個人99分了，他們後面的人應當是第3個高分者，從這個角度了解。後面的名次應當是第3，這也是RANK( )的排名方式。
DENSE_RANK( )函數的排名方式我們稱之為密集排名。由于它的名次之間沒有間隔。
以下的語句示範了RANK()和DENSE_RANK( )的排名方式，查詢結果如表9-3所看到的。
RANK() OVER(PARTITION BY ClassID ORDER BY Achievement DESC) AS SortRank,
DENSE_RANK() OVER(PARTITION BY ClassID ORDER BY Achievement DESC) AS SortDense
FROM Students;
表9-3 按班級和考試成績分别使用RANK( )和DENSE_RANK( )排名
SortRank
SortDense
以下是為語句生成的運作計劃。與ROW_NUMBER( )相比，運作計劃中多出了一個“段”運算符。右邊段的分組根據是ClassID，左邊段的分組根據是ClassID和Achievement，這是多出的“段”。右邊的“段”用于分區操作，在到達下一個分區時發送true信号，“序列射影”運算符會重置排名值。
而左邊的“段”用于比較排序值是否有變化，假設有變化，則通知“序列射影”運算符遞增排名值，遞增方式則按RANK( )和DENSE_RANK( )函數的規則進行。
圖9-2 為RANK( )和DENSE_RANK( )生成的運作計劃
在SQL Server2005之前，也能夠使用子查詢的方式實作排名計算。
語句的原理就是查詢出比目前成績高的個數，再加上1，就是該成績的排名。比如，在第1個班級中，比99分高的成績為0。加上1後。該成績就是第1名。以下語句的運作結果表9-3所看到的同樣，可是由于對于每一個成績都要運作兩次子查詢，在性能方面與RANK()和DENSE_RANK( )函數相差非常遠。
(SELECT COUNT(*) FROM Students AS S2
WHERE S2.ClassID = S1.ClassID AND S2.Achievement > S1.Achievement)+1AS SortRank,
(SELECT COUNT(DISTINCT achievement) FROM Students AS S2
WHERE S2.ClassID = S1.ClassID AND S2.Achievement > S1.Achievement)+1AS SortDense
FROM Students AS S1
ORDER BY ClassID, Achievement DESC;
NTILE( )函數用于把行分發到指定數目的組中。
各個組有編号。編号從1開始。對于每一個行，NTILE将傳回此行所屬的組的編号。
NTILE( )函數能夠接受一個代表組數量的參數，分組的方式“均分”原則。比如，假設一個表有10行，須要分成2組。則每一個組都會有5行。假設表有11行，須要分成3個組。這時候是無法均分的。它配置設定方法是先得到一個能夠整除的基組大小（11/3=3），每組應當配置設定3行。剩餘的2行（11-9）會被再次均分到前面的2組中。
比如，以下的語句指定将Students表按學生成績劃分為3個組，而且Students表恰好也是11行。分組結果如表9-4所看到的。
NTILE(3) OVER(ORDER BY Achievement DESC) AS Tile
表9-4 分組結果
Tile
也能夠先分區，再分組。比如，以下的語句将每一個班級的成績劃分為高、低兩組。查詢結果如表9-5所看到的。
能夠看出，包括4名學生的班級，每組是2人；包括3名學生的班級，第1組是2人，第2組是1人。
CASENTILE(2) OVER(PARTITION BY ClassID ORDER BY Achievement DESC)
WHEN 1 THEN '高'
WHEN 2 THEN '低'
ENDAS Tile
FROM Students;
表9-5 按班級分區再按成績分組結果
高
低
本文轉自mfrbuaa部落格園部落格，原文連結：http://www.cnblogs.com/mfrbuaa/p/5356223.html，如需轉載請自行聯系原作者