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

---

下載FastReport.Net最新版本

排名功能

RANK()/ DENSE_RANK()

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

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

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

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

現在一切都應該如此。 如果前一個排名包含多行，則DENSE_RANK（）不會跳過排名。

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

ROW_NUMBER()

ROW_NUMBER（）函數在窗口中顯示當前行號。 與前兩個函數一樣，ROW_NUMBER（）不需要在括號中指定字段。

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

在查詢中，我們使用Partition by將數據集劃分為組。這里一切都很清楚，不應該引起問題。 如果您不使用分區依據，則在整個數據集中進行編號：

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

實際上，沒有Partition by子句表示整個數據集是一個窗口。

NTILE()

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

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

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

偏移功能

LAG()和LEAD()

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

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

在第一行中，Lag字段的值肯定是Null，因為此行沒有先前的Amount值。對于所有后續行，將獲取上一行的金額值。 LEAD功能以相同的方式工作，但在另一個方向 - 它從下一行獲取值。

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

如您所見，在查詢中我們將參數2傳遞給LEAD函數。這意味著我們從當前金額中獲得第二個值。對于最后兩行，值為Null，因為對于它們沒有以下值。

FIRST_VALUE()和LAST_VALUE()

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

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

這里我們得到了每個窗口的第一個值。 現在我們獲得整個數據集的第一個值：

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

我們從查詢中刪除了Partition子句，因此我們將整個數據集定義為一個窗口。 現在讓我們看一下LAST_VALUE函數的工作：

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

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