有时，需要汇报累计值以显示一些操作的执行过程。例如，可能需要汇报销售日报，如表5.6所示的结果。
表5.6 销售日报
|
Date(日 期) |
Sales(销 售 额)($) |
Total Sales(销售额总计)($) |
|
7/1/2001 |
665262.96 |
665262.96 |
|
7/2/2001 |
15394.33 |
680657.29 |
|
7/3/2001 |
16588.46 |
697245.75 |
|
7/4/2001 |
7907.98 |
705153.72 |
|
7/5/2001 |
16588.46 |
721742.18 |
|
7/6/2001 |
15815.95 |
737558.13 |
|
7/7/2001 |
8680.48 |
746238.61 |
|
7/8/2001 |
8680.48 |
754919.10 |
|
7/9/2001 |
23105.31 |
778024.40 |
|
7/10/2001 |
11664.97 |
789689.37 |
|
7/11/2001 |
15815.95 |
805505.32 |
|
7/12/2001 |
15618.95 |
821124.28 |
7/13/2001 |
7907.98 |
829032.25 |
|
7/14/2001 |
27677.92 |
856710.17 |
|
7/15/2001 |
12409.84 |
869120.02 |
|
Date(日 期) |
Sales(销 售 额)($) |
Total Sales(销售额总计)($) |
|
7/16/2001 |
15815.95 |
884935.97 |
|
… |
… |
… |
可以看出，销售额总计的值等于前一日期的销售额总和加上当前行的销售额。这种类型的合计总是计算包括当前记录在内的累计总和，因此称之为累加和。
对累加和的查询包括两个部分。第一部分很简单。只通过对日期的分组来累加销售额：
SELECT OrderDate,
SUM(TotalDue) AS Sales
FROM Sales.SalesOrderHeader AS A
GROUP BY OrderDate
ORDER BY OrderDate
我们希望通过使用以下的伪码查询来找出特定日期的累加和：
――这不是一个有效的SQL语句
SELECT SUM(TotalDue) AS Expr1
FROM Sales.SalesOrderHeader
WHERE (OrderDate <= <Specific_Date>)
注意，以上查询并不是一个有效的T-SQL脚本，它只是用于解释如何计算某一个特定时间点的累加和。以上脚本中的<Specific_Date>在T-SQL中并不可用，但它在我们的伪码查询中代表一个特定的日期。为了把这段伪码转变成可用的T-SQL，我们可以使用OrderDate列作为每个行的特定日期。(本小节所用的脚本包含在\SqlScripts文件夹中的RunningTotalsExamplesFromText.sql文件中)
Ø 使用子查询来计算累加和
1. 使用子查询来获得一个结果集中的所有值。将以下查询保存为SubQueryMethod.sql。
SELECT OrderDate,
SUM(TotalDue) AS Sales,
(
SELECT SUM(TotalDue)
FROM Sales.SalesOrderHeader
WHERE (OrderDate <= A.OrderDate)
)
AS [Actual Sales]
FROM Sales.SalesOrderHeader AS A
GROUP BY OrderDate
ORDER BY OrderDate
2. 在SQL Server Management Studio中查看上述查询的估计执行计划。您将看到两个查询并行执行以获取结果集。
尝试使用不同的方法。我们可以在一个用户定义函数中封装计算累加和的过程。
Ø 使用用户定义函数来计算累加和
1. 首先，执行以下脚本创建一个用户定义函数：
CREATE FUNCTION SalesToDate
(
@ThisDate datetime
)
RETURNS money
AS
BEGIN
RETURN (SELECT SUM(TotalDue) AS Expr1
FROM Sales.SalesOrderHeader
WHERE (OrderDate <= @ThisDate))
END
2. 现在执行以下查询，将第一步的查询和这一步的查询保存为UserDefFunctionMethod.sql。
SELECT OrderDate,
SUM(TotalDue) AS Sales,
dbo.SalesToDate(A.OrderDate) AS [Actual sales]
FROM Sales.SalesOrderHeader AS A
GROUP BY OrderDate
ORDER BY OrderDate
哪一种技术更好呢？可以执行以下过程来比较这两种技术。
Ø 比较两个查询的执行性能
1. 在SQL Server Management Studio中打开查询菜单。
2. 选择“包括客户端统计信息”项。
3. 再次执行前面保存过的两套查询。现在将出现随“结果”选项卡一起显示出来的“客户端统计信息”选项卡。可以通过其中提供的信息来了解这两种查询所消耗的时间。
表5.7只显示了这两种查询的统计信息的部分内容。
注意 这个表格只显示了有效的统计信息的一部分。
表5.7 客户端统计信息的示例
|
子查询方法 |
查询试验1 |
平均 |
|
客户端处理时间 |
10375 |
10375.000 |
|
总执行时间 |
10835 |
10835.000 |
|
服务器应答等待时间 |
460 |
460.000 |
|
客户端处理时间 |
35677 |
35677.000 |
|
总执行时间 |
39502 |
39502.000 |
|
服务器应答等待时间 |
3825 |
3825.000 |
可以看出，使用用户定义函数的查询所消耗的时间是使用子查询的查询所消耗时间的3倍以上。
