【FastReport教程】每個T-SQL程序員應該知道的窗口函數(shù)——第1部分

---

下載FastReport.Net最新版本

很久以前，Microsoft SQL Server 2005窗口函數(shù)中出現(xiàn)了一個有趣的功能。這些函數(shù)允許您在Select子句中的給定行范圍內執(zhí)行計算。對于那些沒有遇到過這些功能的人來說，問題出現(xiàn)了 - “Window是什么意思？”。窗口表示在其中執(zhí)行計算的行集。窗口功能允許您將整個數(shù)據(jù)集分成這樣的窗口。

當然，沒有它們，所有窗口函數(shù)都可以做到。但是，窗口函數(shù)與常規(guī)聚合函數(shù)相比具有很大的優(yōu)勢：無需對數(shù)據(jù)集進行分組以進行計算，這允許您使用其唯一標識符保存集合中的所有行。同時，窗函數(shù)的工作結果只是作為另一個字段添加到結果樣本中。 使用窗口函數(shù)而不是常規(guī)聚合函數(shù)的主要優(yōu)點如下：窗口函數(shù)不會將行分組為一行輸出，行保留其單獨的標識符，并將聚合值添加到每一行。 該窗口使用OVER（）指令定義。我們來看看這條指令的語法：

Window function (column for calculations) OVER ([PARTITION BY column for grouping] [ORDER BY column for sorting] [ROWS or RANGE expression for restricting rows within a group])

在本文的第二部分中考慮的功能范圍。我只想說它們分為：aggregating、ranking和bias。 為了演示窗口函數(shù)的操作，我建議在測試表上：

CREATE TABLE ForWindowFunc (ID INT, GroupId INT, Amount INT)
GO
 
INSERT INTO ForWindowFunc (ID, GroupId, Amount)
 VALUES(1, 1, 100), (1, 1, 200), (1, 2, 150),
 (2, 1, 100), (2, 1, 300), (2, 2, 200), (2, 2, 50),
 (3, 1, 150), (3, 2, 200), (3, 2, 10);

組中有三組具有不同數(shù)量的元素。 最常使用求和函數(shù)，因此我們將對其進行演示。讓我們看看OVER指令是如何工作的：

SELECT ID,
 Amount,
 SUM(Amount) OVER() AS SUM FROM ForWindowFunc

我們使用了沒有句子的OVER（）指令。在該實施例中，窗口將是整個數(shù)據(jù)集并且不應用排序。我們很幸運，數(shù)據(jù)的輸出順序與插入表中的順序相同，但如果沒有明確定義的排序，SQL Server可以更改顯示順序。因此，OVER（）指令幾乎從未在沒有建議的情況下使用。但是讓我們把注意力轉向新的列SUM。對于每一行，輸出相同的值1310。這是“Amount”列中所有值的累積總和。

PARTITION BY子句

PARTITION BY子句定義了分組所需的列，它是將行集拆分為窗口的關鍵。 讓我們改變我們之前寫的查詢：

SELECT ID, 
 Amount, 
 SUM(Amount) OVER(PARTITION BY ID) AS SUM FROM ForWindowFunc

PARTITION BY子句按ID字段對字符串進行分組。現(xiàn)在為每個組計算自己的金額值的總和。您可以通過多個字段創(chuàng)建窗口。然后在PARTITION BY中，您需要編寫用逗號分隔的分組字段（例如，PARTITION BY ID，Amount）。

ORDER BY子句

與PARTITION BY一起，可以使用ORDER BY子句。ORDER BY子句確定窗口中的排序順序。排序順序非常重要，因為窗口函數(shù)將根據(jù)此特定順序處理數(shù)據(jù)。如果不使用PARTITION BY子句，而只使用ORDER BY，則窗口將表示整個數(shù)據(jù)集。

SELECT ID,
 GroupId,
 Amount,
 SUM(Amount) OVER(PARTITION BY id ORDER BY Amount) AS SUM FROM ForWindowFunc

對于PARTITION BY子句，在Amount字段中添加了ORDER BY。因此，我們指出我們希望在窗口中看到并非所有Amount值的總和，但是對于每個Amount值，與所有先前值的總和。這種總和通常被稱為cumulative total或increscent total。

您已注意到GpoupId字段出現(xiàn)在示例中。此字段允許您顯示累積總計將如何更改，具體取決于排序。更改查詢：

SELECT ID, 
 GroupId, 
 Amount, 
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId, Amount) AS SUM FROM ForWindowFunc

ROWS/RANG

而且我們得到了完全不同的行為。雖然最后是窗口中的最后一個值，但值與前一個示例一致，但所有其他值的總和是不同的。因此，清楚地了解您最終想要獲得的內容非常重要。

在OVER指令中使用了另外兩個ROWS和RANGE子句。此功能出現(xiàn)在MS SQL Server 2012中。 ROWS子句限制窗口中的行，指示當前行之前或之后的固定行數(shù)。ROWS和RANGE子句都與ORDER BY一起使用。 可以使用以下方法指定ROWS子句：

• CURRENT ROW - 顯示當前行;
• UNBOUNDED FOLLOWING - 當前記錄之后的所有記錄;
• UNBOUNDED PRECEDING - 以前的所有參賽作品;
• < integer> PRECEDING - 指定的前一行數(shù);
• < integer> FOLLOWING - 指定的后續(xù)條目數(shù)。

您可以組合這些功能以獲得所需的結果，例如：當前行與下行之間的行 - 當前和下一個條目將出現(xiàn)在窗口中;

SELECT ID, 
 GroupId, 
 Amount, 
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId, Amount ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) AS SUM FROM ForWindowFunc

這里，金額由窗口中的當前和下一個單元格計算。窗口中的最后一行與Amount的含義相同。讓我們看一下用藍色突出顯示的第一個窗口。通過加100和200來計算總和300.對于下一個值，情況類似。并且窗口中的最后一個數(shù)量是150，因為當前的數(shù)量沒有更多要添加。

BETWEEN 1 PRECEDING AND CURRENT ROW ROWS -一個以前和目前的紀錄

SELECT ID, 
 GroupId, 
 Amount, 
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId, Amount ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS SUM FROM ForWindowFunc

在此查詢中，我們通過添加金額和前一個金額的當前值來獲得總和。第一行是100，因為沒有先前的金額值。 該RANGE子句也意在限制的行集。與ROWS不同，它不適用于物理字符串，但在ORDER BY子句中具有一系列行。這意味著ORDER BY子句上下文中排名相同的行將被計??為CURRENT ROW函數(shù)的一個當前行。在ROWS子句中，當前行是數(shù)據(jù)集的當前行。

RANGE子句只能與CURRENT ROW，UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING選項一起使用。 RANGE子句可以使用選項：

• CURRENT ROW - 顯示當前行;
• UNBOUNDED FOLLOWING - 當前記錄之后的所有記錄;
• UNBOUNDED PRECEDING - 所有以前的條目。

不能使用選項：

• < integer> PRECEDING - 指定的前一行數(shù);
• < integer> FOLLOWING - 指定的后續(xù)條目數(shù)。

例子：RANGE CURRENT ROW

SELECT ID,
 GroupId,
 Amount,
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId RANGE CURRENT ROW) AS SUM FROM ForWindowFunc

Range子句設置為當前行。但是，正如我們記得的那樣，對于Range，當前行是對應于相同排序值的所有行。在這種情況下，通過GroupId字段進行排序。第一個窗口的前兩行的GroupId值為1 - 因此這兩個值都滿足RANGE CURRENT ROW約束。因此，這些行中的每一行的總和等于它們的總金額 - 300。

無界前置與當前行之間的范圍

SELECT ID,
 GroupId,
 Amount,
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SUM FROM ForWindowFunc

在這種情況下，限制是在所有先前的行和當前行。對于第一行和第二行，此規(guī)則的工作方式與前一行相同（記住CURRENT ROW），第三行則是前一行的數(shù)量與當前行的總和。 當前行和無限下行之間的范圍

SELECT ID, 
 GroupId, 
 Amount, 
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS SUM FROM ForWindowFunc

這個限制允許我們從一個窗口中的當前行和所有先前行中獲取金額。由于第二行和第三行位于同一GroupId中，因此這些值為Current Row。因此，他們立即總結。 本文的第一部分到此結束。最后 - 從實際操作中使用窗口函數(shù)的示例。 了解窗口函數(shù)本質的最佳方法是一個例子。假設您有關于付款訂閱者的數(shù)據(jù)。付款到達合同。但是這份合同的附屬合同存在負余額。我們希望分配收到的資金以償還子公司的債務。 因此，我們需要了解我們將從主合同賬戶中注銷多少錢以及我們將向孩子轉賬多少錢。我們來看看表格：

其中，ContractId是主合同的標識符， ChildContractId - 子契約標識符， PayId - 付款ID， CustAccount - 兒童合同余額， PayAmount - 付款。 從表中可以清楚地看出，對于每個兒童合同，付款金額為800.這是因為付款是在父母合同上。 所以我們的任務是計算從父母到子女合同的轉賬金額。 為此，請總結CustAccount和PayAmount。但是，簡單的余額和付款并不適合我們。畢竟，要償還第二個附屬合同的債務，我們必須考慮第一份合同和付款的剩余余額。 我們怎么能在這種情況下采取行動？我們可以選擇：

SELECT
ContractId,
ChildContractId,
PayId,
CustAccount,
PayAmount,
PayAmount + (SELECT SUM(CustAccount) FROM dbo.Pays p2 WHERE p1.PayId = p2.PayId AND p2.ChildContractId < = p1.ChildContractId) AS [SUM]
FROM dbo.Pays p1

此查詢解決了該任務，但子查詢破壞了整個圖片 - 它增加了查詢執(zhí)行時間。讓我們應用添加的窗口函數(shù)：

SELECT
ContractId,
ChildContractId,
PayId,
CustAccount,
PayAmount,
PayAmount + SUM(CustAccount) OVER (ORDER BY ChildContractId) AS [SUM]
FROM dbo.Pays p1

此選項更快，更簡潔。在我們的例子中，我們得到窗口中CustAccount字段的總和，該字段由ChildContractId字段組成。 這些查詢的結果將是表格：

根據(jù)Sum列中獲得的數(shù)據(jù)，我們確定從父合約轉移到子合同的金額。對于合同1000000002，我們全額償還了債務，因此支付金額為200.對于協(xié)議1000000003，債務部分償還 - 支付金額等于計算后的余額和支付余額之和第一條記錄（-1000 + 600 = -400）。