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

---

下載FastReport.Net最新版本

排名功能

RANK()/ DENSE_RANK()

RANK()函數(shù)返回窗口中當(dāng)前行的序列號。但是，有一個(gè)功能。如果Order By子句包含規(guī)則的幾個(gè)等效字符串，則所有這些字符串都將被視為當(dāng)前字符串。因此，RANK()函數(shù)應(yīng)該用于排名，而不是劃船。但是，如果您正確設(shè)置了Order by，那么您也可以對物理字符串進(jìn)行編號，例如：

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

以下是Order by上下文中相同行的情況：

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

第一個(gè)窗口中的第三行的等級為3，盡管前兩行被分配到第一個(gè)等級。不是最容易理解的邏輯。 在這種情況下，最好使用DENSE_RANK（）。

SELECT ID,
GroupId,
Amount,
DENSE_RANK() OVER (Partition   

現(xiàn)在一切都應(yīng)該如此。 如果前一個(gè)排名包含多行，則DENSE_RANK（）不會跳過排名。

函數(shù)RANK（）和DENSE_RANK（）不需要在括號中指示字段。

ROW_NUMBER()

ROW_NUMBER（）函數(shù)在窗口中顯示當(dāng)前行號。 與前兩個(gè)函數(shù)一樣，ROW_NUMBER（）不需要在括號中指定字段。

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

在查詢中，我們使用Partition by將數(shù)據(jù)集劃分為組。這里一切都很清楚，不應(yīng)該引起問題。 如果您不使用分區(qū)依據(jù)，則在整個(gè)數(shù)據(jù)集中進(jìn)行編號：

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

實(shí)際上，沒有Partition by子句表示整個(gè)數(shù)據(jù)集是一個(gè)窗口。

NTILE()

NTILE（）函數(shù)允許您確定當(dāng)前行屬于哪個(gè)組。組的數(shù)量在括號中指定，ORDER BY子句確定用于定義組的列。 例如，這意味著如果您有100行并且您希望根據(jù)指定的值字段創(chuàng)建4個(gè)四分位數(shù)，則可以輕松執(zhí)行此操作并查看每個(gè)四分位數(shù)中有多少行。 我們來看看這個(gè)例子。在下面的查詢中，我們表示我們要根據(jù)訂單金額創(chuàng)建四個(gè)四分位數(shù)。然后我們想看看每個(gè)四分位數(shù)中有多少訂單。 NTILE根據(jù)以下公式創(chuàng)建切片： 每組中的行數(shù)=集合中的行數(shù)/指定組的數(shù)量 以下是我們的示例：請求僅包含10行和4個(gè)圖塊，因此每個(gè)圖塊中的行數(shù)將為2.5（10/4）。因?yàn)樾袛?shù)必須是整數(shù)，而不是小數(shù)。SQL引擎將為前兩組分配3行，為剩余的兩組分配2行。

SELECT Amount,
NTILE(4) OVER(ORDER BY amount) AS Ntile
FROM ForWindowFunc

這是一個(gè)非常簡單的例子，但它很好地證明了這個(gè)功能。所有金額值按升序排序，并分為4組。

偏移功能

LAG()和LEAD()

這兩個(gè)函數(shù)分別允許您獲取上一個(gè)和下一個(gè)值。通常需要將當(dāng)前值與計(jì)算列中的前一個(gè)或下一個(gè)進(jìn)行比較。 作為參數(shù)，您可以將函數(shù)的名稱和您需要偏離當(dāng)前行的行數(shù)傳遞給函數(shù)并獲取值。與在SUBSTRING（）中一樣，我們指定從中獲取字符的位置，這里我們指示從中獲取值的位置。如果未指定值的數(shù)量，則默認(rèn)值為1。 因此，LAG功能允許您在一個(gè)窗口中訪問上一行的數(shù)據(jù)。

SELECT id, Amount,
LAG(Amount) OVER(ORDER BY id, amount) AS Lag
FROM ForWindowFunc

在第一行中，Lag字段的值肯定是Null，因?yàn)榇诵袥]有先前的Amount值。對于所有后續(xù)行，將獲取上一行的金額值。 LEAD功能以相同的方式工作，但在另一個(gè)方向 - 它從下一行獲取值。

SELECT id, Amount,
LEAD(Amount,2) OVER(ORDER BY id, amount) AS Lag
FROM ForWindowFunc

如您所見，在查詢中我們將參數(shù)2傳遞給LEAD函數(shù)。這意味著我們從當(dāng)前金額中獲得第二個(gè)值。對于最后兩行，值為Null，因?yàn)閷τ谒鼈儧]有以下值。

FIRST_VALUE()和LAST_VALUE()

使用這些函數(shù)，我們可以在窗口中獲取第一個(gè)和最后一個(gè)值。如果未指定Partition by子句，則函數(shù)將返回整個(gè)數(shù)據(jù)集的第一個(gè)和最后一個(gè)值。

SELECT id, Amount,
FIRST_VALUE(Amount) OVER(Partition BY Id ORDER BY Id, amount) AS FIRST
FROM ForWindowFunc

這里我們得到了每個(gè)窗口的第一個(gè)值。 現(xiàn)在我們獲得整個(gè)數(shù)據(jù)集的第一個(gè)值：

SELECT id, Amount,
FIRST_VALUE(Amount) OVER(ORDER BY Id, amount) AS FIRST
FROM ForWindowFunc

我們從查詢中刪除了Partition子句，因此我們將整個(gè)數(shù)據(jù)集定義為一個(gè)窗口。 現(xiàn)在讓我們看一下LAST_VALUE函數(shù)的工作：

SELECT id, Amount,
LAST_VALUE(Amount) OVER(ORDER BY id) AS LAST
FROM ForWindowFunc

查詢與前一個(gè)查詢幾乎相同，但結(jié)果完全不同。由于表中沒有唯一標(biāo)識符，因此我們無法對其進(jìn)行排序。按字段ID排序?qū)嶋H上將數(shù)據(jù)分成三組。函數(shù)返回每個(gè)函數(shù)的最后一個(gè)值 - 這是函數(shù)的一個(gè)特性。 有了這個(gè)，我們將完成窗口函數(shù)的考慮。給出的示例大大簡化，以便更好地理解函數(shù)的工作方式。實(shí)際任務(wù)通常更加困難，因此根據(jù)OVER指令中的句子很好地理解函數(shù)的行為。