【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指令中的句子很好地理解函数的行为。