【FastReport教程】每個(gè)T-SQL程序員應(yīng)該知道的窗口函數(shù)——第2部分(上)

---

下載FastReport.Net最新版本

在本文的第二部分，我們將討論用于形成值的函數(shù)本身。窗口函數(shù)根據(jù)與當(dāng)前行關(guān)聯(lián)的一組數(shù)據(jù)計(jì)算值，即，如果使用分區(qū)依據(jù)，則來自同一組的數(shù)據(jù)。用于 分組的常規(guī)聚合函數(shù)需要對(duì)行進(jìn)行分組，從而從樣本中丟失必要的唯一信息。因此，有必要使用兩個(gè)而不是單個(gè)請(qǐng)求，以便擁有所有必要的數(shù)據(jù)和按組的總和。窗口聚合函數(shù)允許在一個(gè)查詢 中實(shí)現(xiàn)相同的結(jié)果。窗口是一組用于計(jì)算函數(shù)的線。OVER指令將整個(gè)行集拆分為單獨(dú)的組 - 根據(jù)指定條件的窗口。 我們來談?wù)劥翱诤瘮?shù)的類型。有三組目的地：

• 聚合函數(shù)：SUM（），MAX（），MIN（），AVG（）。COUNT（）。這些函數(shù)返回通過算術(shù)計(jì)算獲得的值;
• 排名函數(shù)：RANK（），DENSE_RANK（），ROW_NUMBER（），NTILE（）。允許獲取窗口中的條目序列號(hào);
• 偏移功能：LAG（），LEAD（），F(xiàn)IRST_VALUE（），LAST_VALUE（）。從另一個(gè)窗口行返回一個(gè)值。

為了演示函數(shù)的工作原理，我將使用一個(gè)簡單的表：

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ù)

SUM()

SUM()函數(shù)就像常規(guī)聚合函數(shù)一樣 - 它匯總了數(shù)據(jù)集中給定列的所有值。但是，由于OVER（）指令，我們將數(shù)據(jù)集分解為窗口。根據(jù)ORDER BY子句中指定的順序在窗口內(nèi)執(zhí)行求和。讓我們看一個(gè)簡 單的例子 - 三組的總和。

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

為方便起見，窗戶以不同顏色突出顯示。窗口中的所有值都具有相同的金額 - 窗口中所有金額的總和。

讓我們在選擇中添加另一列并更改OVER指令：

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

如您所見，由于GroupId字段，現(xiàn)在每個(gè)窗口都被分成組?，F(xiàn)在每個(gè)小組都有自己的金額。 現(xiàn)在，讓我們在每個(gè)窗口中創(chuàng)建一個(gè)累積結(jié)果：

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

我們不再需要GroupId字段，因此我們將其從選擇中刪除。現(xiàn)在，對(duì)于窗口中的每一行，計(jì)算總計(jì)，即金額的當(dāng)前值與之前所有金額的總和。

AVG()

此函數(shù)計(jì)算平均值。它可以與句子Partition by和Order by一起使用。

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

窗口中的每一行都有一個(gè)平均值A(chǔ)mount，它由公式計(jì)算：所有Amount /為行數(shù)的總和。 此函數(shù)的行為類似于SUM（）。

MIN()

從函數(shù)的名稱可以清楚地看到它返回窗口中的最小值。

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

如您所見，在Min列中，窗口中顯示最小Amount值。

MAX()

MAX函數(shù)的工作方式與MIN相同，它只給出窗口中字段的最大值：

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

一切都很清楚。在第一組中，最大金額為200，第二組為300，第三組為200。

count()

此函數(shù)返回窗口中的行數(shù)。

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

使查詢更復(fù)雜一點(diǎn)，并添加GroupId字段。

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

在這種情況下，它更有趣。我們來看看第一個(gè)窗口。對(duì)于第一行和第二行，記錄數(shù)為2.但對(duì)于第三行，該值已經(jīng)為3.我們設(shè)法累積了累計(jì)金額等組中的金額。 如果我們?nèi)匀恍枰總€(gè)組中的數(shù)字， 則需要將GroupId添加到Partition by子句中。

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