【FastReport教程】每个T-SQL程序员应该知道的窗口函数——第2部分(上)

下载FastReport.Net最新版本

在本文的第二部分，我们将讨论用于形成值的函数本身。窗口函数根据与当前行关联的一组数据计算值，即，如果使用分区依据，则来自同一组的数据。用于 分组的常规聚合函数需要对行进行分组，从而从样本中丢失必要的唯一信息。因此，有必要使用两个而不是单个请求，以便拥有所有必要的数据和按组的总和。窗口聚合函数允许在一个查询 中实现相同的结果。窗口是一组用于计算函数的线。OVER指令将整个行集拆分为单独的组 - 根据指定条件的窗口。 我们来谈谈窗口函数的类型。有三组目的地：

• 聚合函数：SUM（），MAX（），MIN（），AVG（）。COUNT（）。这些函数返回通过算术计算获得的值;
• 排名函数：RANK（），DENSE_RANK（），ROW_NUMBER（），NTILE（）。允许获取窗口中的条目序列号;
• 偏移功能：LAG（），LEAD（），FIRST_VALUE（），LAST_VALUE（）。从另一个窗口行返回一个值。

为了演示函数的工作原理，我将使用一个简单的表：

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

聚合函数

SUM()

SUM()函数就像常规聚合函数一样 - 它汇总了数据集中给定列的所有值。但是，由于OVER（）指令，我们将数据集分解为窗口。根据ORDER BY子句中指定的顺序在窗口内执行求和。让我们看一个简 单的例子 - 三组的总和。

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字段，现在每个窗口都被分成组。现在每个小组都有自己的金额。 现在，让我们在每个窗口中创建一个累积结果：

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

我们不再需要GroupId字段，因此我们将其从选择中删除。现在，对于窗口中的每一行，计算总计，即金额的当前值与之前所有金额的总和。

AVG()

此函数计算平均值。它可以与句子Partition by和Order by一起使用。

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

窗口中的每一行都有一个平均值Amount，它由公式计算：所有Amount /为行数的总和。 此函数的行为类似于SUM（）。

MIN()

从函数的名称可以清楚地看到它返回窗口中的最小值。

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

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

MAX()

MAX函数的工作方式与MIN相同，它只给出窗口中字段的最大值：

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

一切都很清楚。在第一组中，最大金额为200，第二组为300，第三组为200。

count()

此函数返回窗口中的行数。

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

使查询更复杂一点，并添加GroupId字段。

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

在这种情况下，它更有趣。我们来看看第一个窗口。对于第一行和第二行，记录数为2.但对于第三行，该值已经为3.我们设法累积了累计金额等组中的金额。 如果我们仍然需要每个组中的数字， 则需要将GroupId添加到Partition by子句中。

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