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

下载FastReport.Net最新版本

很久以前，Microsoft SQL Server 2005窗口函数中出现了一个有趣的功能。这些函数允许您在Select子句中的给定行范围内执行计算。对于那些没有遇到过这些功能的人来说，问题出现了 - “Window是什么意思？”。窗口表示在其中执行计算的行集。窗口功能允许您将整个数据集分成这样的窗口。

当然，没有它们，所有窗口函数都可以做到。但是，窗口函数与常规聚合函数相比具有很大的优势：无需对数据集进行分组以进行计算，这允许您使用其唯一标识符保存集合中的所有行。同时，窗函数的工作结果只是作为另一个字段添加到结果样本中。 使用窗口函数而不是常规聚合函数的主要优点如下：窗口函数不会将行分组为一行输出，行保留其单独的标识符，并将聚合值添加到每一行。 该窗口使用OVER（）指令定义。我们来看看这条指令的语法：

Window function (column for calculations) OVER ([PARTITION BY column for grouping] [ORDER BY column for sorting] [ROWS or RANGE expression for restricting rows within a group])

在本文的第二部分中考虑的功能范围。我只想说它们分为：aggregating、ranking和bias。 为了演示窗口函数的操作，我建议在测试表上：

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

组中有三组具有不同数量的元素。 最常使用求和函数，因此我们将对其进行演示。让我们看看OVER指令是如何工作的：

SELECT ID,
 Amount,
 SUM(Amount) OVER() AS SUM FROM ForWindowFunc

我们使用了没有句子的OVER（）指令。在该实施例中，窗口将是整个数据集并且不应用排序。我们很幸运，数据的输出顺序与插入表中的顺序相同，但如果没有明确定义的排序，SQL Server可以更改显示顺序。因此，OVER（）指令几乎从未在没有建议的情况下使用。但是让我们把注意力转向新的列SUM。对于每一行，输出相同的值1310。这是“Amount”列中所有值的累积总和。

PARTITION BY子句

PARTITION BY子句定义了分组所需的列，它是将行集拆分为窗口的关键。 让我们改变我们之前写的查询：

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

PARTITION BY子句按ID字段对字符串进行分组。现在为每个组计算自己的金额值的总和。您可以通过多个字段创建窗口。然后在PARTITION BY中，您需要编写用逗号分隔的分组字段（例如，PARTITION BY ID，Amount）。

ORDER BY子句

与PARTITION BY一起，可以使用ORDER BY子句。ORDER BY子句确定窗口中的排序顺序。排序顺序非常重要，因为窗口函数将根据此特定顺序处理数据。如果不使用PARTITION BY子句，而只使用ORDER BY，则窗口将表示整个数据集。

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

对于PARTITION BY子句，在Amount字段中添加了ORDER BY。因此，我们指出我们希望在窗口中看到并非所有Amount值的总和，但是对于每个Amount值，与所有先前值的总和。这种总和通常被称为cumulative total或increscent total。

您已注意到GpoupId字段出现在示例中。此字段允许您显示累积总计将如何更改，具体取决于排序。更改查询：

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

ROWS/RANG

而且我们得到了完全不同的行为。虽然最后是窗口中的最后一个值，但值与前一个示例一致，但所有其他值的总和是不同的。因此，清楚地了解您最终想要获得的内容非常重要。

在OVER指令中使用了另外两个ROWS和RANGE子句。此功能出现在MS SQL Server 2012中。 ROWS子句限制窗口中的行，指示当前行之前或之后的固定行数。ROWS和RANGE子句都与ORDER BY一起使用。 可以使用以下方法指定ROWS子句：

• CURRENT ROW - 显示当前行;
• UNBOUNDED FOLLOWING - 当前记录之后的所有记录;
• UNBOUNDED PRECEDING - 以前的所有参赛作品;
• < integer> PRECEDING - 指定的前一行数;
• < integer> FOLLOWING - 指定的后续条目数。

您可以组合这些功能以获得所需的结果，例如：当前行与下行之间的行 - 当前和下一个条目将出现在窗口中;

SELECT ID, 
 GroupId, 
 Amount, 
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId, Amount ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) AS SUM FROM ForWindowFunc

这里，金额由窗口中的当前和下一个单元格计算。窗口中的最后一行与Amount的含义相同。让我们看一下用蓝色突出显示的第一个窗口。通过加100和200来计算总和300.对于下一个值，情况类似。并且窗口中的最后一个数量是150，因为当前的数量没有更多要添加。

BETWEEN 1 PRECEDING AND CURRENT ROW ROWS -一个以前和目前的纪录

SELECT ID, 
 GroupId, 
 Amount, 
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId, Amount ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS SUM FROM ForWindowFunc

在此查询中，我们通过添加金额和前一个金额的当前值来获得总和。第一行是100，因为没有先前的金额值。 该RANGE子句也意在限制的行集。与ROWS不同，它不适用于物理字符串，但在ORDER BY子句中具有一系列行。这意味着ORDER BY子句上下文中排名相同的行将被计​​为CURRENT ROW函数的一个当前行。在ROWS子句中，当前行是数据集的当前行。

RANGE子句只能与CURRENT ROW，UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING选项一起使用。 RANGE子句可以使用选项：

• CURRENT ROW - 显示当前行;
• UNBOUNDED FOLLOWING - 当前记录之后的所有记录;
• UNBOUNDED PRECEDING - 所有以前的条目。

不能使用选项：

• < integer> PRECEDING - 指定的前一行数;
• < integer> FOLLOWING - 指定的后续条目数。

例子：RANGE CURRENT ROW

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

Range子句设置为当前行。但是，正如我们记得的那样，对于Range，当前行是对应于相同排序值的所有行。在这种情况下，通过GroupId字段进行排序。第一个窗口的前两行的GroupId值为1 - 因此这两个值都满足RANGE CURRENT ROW约束。因此，这些行中的每一行的总和等于它们的总金额 - 300。

无界前置与当前行之间的范围

SELECT ID,
 GroupId,
 Amount,
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SUM FROM ForWindowFunc

在这种情况下，限制是在所有先前的行和当前行。对于第一行和第二行，此规则的工作方式与前一行相同（记住CURRENT ROW），第三行则是前一行的数量与当前行的总和。 当前行和无限下行之间的范围

SELECT ID, 
 GroupId, 
 Amount, 
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS SUM FROM ForWindowFunc

这个限制允许我们从一个窗口中的当前行和所有先前行中获取金额。由于第二行和第三行位于同一GroupId中，因此这些值为Current Row。因此，他们立即总结。 本文的第一部分到此结束。最后 - 从实际操作中使用窗口函数的示例。 了解窗口函数本质的最佳方法是一个例子。假设您有关于付款订阅者的数据。付款到达合同。但是这份合同的附属合同存在负余额。我们希望分配收到的资金以偿还子公司的债务。 因此，我们需要了解我们将从主合同账户中注销多少钱以及我们将向孩子转账多少钱。我们来看看表格：

其中，ContractId是主合同的标识符， ChildContractId - 子契约标识符， PayId - 付款ID， CustAccount - 儿童合同余额， PayAmount - 付款。 从表中可以清楚地看出，对于每个儿童合同，付款金额为800.这是因为付款是在父母合同上。 所以我们的任务是计算从父母到子女合同的转账金额。 为此，请总结CustAccount和PayAmount。但是，简单的余额和付款并不适合我们。毕竟，要偿还第二个附属合同的债务，我们必须考虑第一份合同和付款的剩余余额。 我们怎么能在这种情况下采取行动？我们可以选择：

SELECT
ContractId,
ChildContractId,
PayId,
CustAccount,
PayAmount,
PayAmount + (SELECT SUM(CustAccount) FROM dbo.Pays p2 WHERE p1.PayId = p2.PayId AND p2.ChildContractId < = p1.ChildContractId) AS [SUM]
FROM dbo.Pays p1

此查询解决了该任务，但子查询破坏了整个图片 - 它增加了查询执行时间。让我们应用添加的窗口函数：

SELECT
ContractId,
ChildContractId,
PayId,
CustAccount,
PayAmount,
PayAmount + SUM(CustAccount) OVER (ORDER BY ChildContractId) AS [SUM]
FROM dbo.Pays p1

此选项更快，更简洁。在我们的例子中，我们得到窗口中CustAccount字段的总和，该字段由ChildContractId字段组成。 这些查询的结果将是表格：

根据Sum列中获得的数据，我们确定从父合约转移到子合同的金额。对于合同1000000002，我们全额偿还了债务，因此支付金额为200.对于协议1000000003，债务部分偿还 - 支付金额等于计算后的余额和支付余额之和第一条记录（-1000 + 600 = -400）。