在 SQL Server 中计算简单的运行总计
在SQL Server中计算运行总计的一些简单场景。
引言
一个典型的问题是，如何在SQL Server中计算运行总计。 有几种方法可以做到这一点，本文试图解释其中的几种。
测试环境
首先，我们需要一个数据表。 为了简单起见，让我们创建一个只包含自动递增的id和value字段的表。
--------------------------------------------------------------------
-- table for test
--------------------------------------------------------------------
CREATE TABLE RunTotalTestData (
id int not null identity(1,1) primary key,
value int not null
);
并用一些数据填充它
--------------------------------------------------------------------
-- test data
--------------------------------------------------------------------
INSERT INTO RunTotalTestData (value) VALUES (1);
INSERT INTO RunTotalTestData (value) VALUES (2);
INSERT INTO RunTotalTestData (value) VALUES (4);
INSERT INTO RunTotalTestData (value) VALUES (7);
INSERT INTO RunTotalTestData (value) VALUES (9);
INSERT INTO RunTotalTestData (value) VALUES (12);
INSERT INTO RunTotalTestData (value) VALUES (13);
INSERT INTO RunTotalTestData (value) VALUES (16);
INSERT INTO RunTotalTestData (value) VALUES (22);
INSERT INTO RunTotalTestData (value) VALUES (42);
INSERT INTO RunTotalTestData (value) VALUES (57);
INSERT INTO RunTotalTestData (value) VALUES (58);
INSERT INTO RunTotalTestData (value) VALUES (59);
INSERT INTO RunTotalTestData (value) VALUES (60);
场景是在数据按id字段升序排序时获取运行总计。
相关标量查询
一种非常传统的方法是使用相关标量查询来获取到目前为止的运行总计。 查询可能如下所示
--------------------------------------------------------------------
-- correlated scalar
--------------------------------------------------------------------
SELECT a.id, a.value, (SELECT SUM(b.value)
FROM RunTotalTestData b
WHERE b.id <= a.id)
FROM RunTotalTestData a
ORDER BY a.id;
当运行此查询时，结果是
id value running total
-- ----- -------------
1 1 1
2 2 3
3 4 7
4 7 14
5 9 23
6 12 35
7 13 48
8 16 64
9 22 86
10 42 128
11 57 185
12 58 243
13 59 302
14 60 362
所以就是这样。 连同实际的行值，我们有一个运行总计。 标量查询只是获取value字段的总和，该字段来自 ID 小于或等于当前行值的行。 让我们看一下执行计划
发生的情况是数据库从表中获取所有行，并使用嵌套循环再次获取计算总和的行。 这也可以在统计信息中看到
Table 'RunTotalTestData'. Scan count 15, logical reads 30, physical reads 0...
使用连接
另一种变体是使用连接。 现在查询可能如下所示
--------------------------------------------------------------------
-- using join
--------------------------------------------------------------------
SELECT a.id, a.value, SUM(b.Value)
FROM RunTotalTestData a,
RunTotalTestData b
WHERE b.id <= a.id
GROUP BY a.id, a.value
ORDER BY a.id;
结果相同，但技术略有不同。 并非为每行获取总和，而是使用GROUP BY子句创建总和。 行交叉连接，仅将连接限制为B中等于或更小的ID值。 计划
该计划看起来有些不同，实际上发生的是表只被读取两次。 这可以在统计信息中更清楚地看到。
Table 'RunTotalTestData'. Scan count 2, logical reads 31...
相关标量查询的计算成本为0.0087873，而连接版本的成本为0.0087618。 差异不大，但请记住，我们正在处理极少量的数据。
使用条件
在现实生活中，通常使用限制条件，那么这些条件如何应用于这些查询。 基本规则是，必须在这两种变体中两次定义条件。 一次用于获取的行，另一次用于计算总和的行。
如果我们要计算奇数值的运行总计，则相关标量版本可能如下所示
--------------------------------------------------------------------
-- correlated scalar, subset
--------------------------------------------------------------------
SELECT a.id, a.value, (SELECT SUM(b.value)
FROM RunTotalTestData b
WHERE b.id <= a.id
AND b.value % 2 = 1)
FROM RunTotalTestData a
WHERE a.value % 2 = 1
ORDER BY a.id;
结果是：
id value runningtotal
-- ----- ------------
1 1 1
4 7 8
5 9 17
7 13 30
11 57 87
13 59 146
对于连接版本，它可能类似于
--------------------------------------------------------------------
-- with join, subset
--------------------------------------------------------------------
SELECT a.id, a.value, SUM(b.Value)
FROM RunTotalTestData a,
RunTotalTestData b
WHERE b.id <= a.id
AND a.value % 2 = 1
AND b.value % 2 = 1
GROUP BY a.id, a.value
ORDER BY a.id;
当实际有更多条件时，正确维护这些条件可能非常痛苦。 尤其是在动态构建它们时。
计算数据分区的运行总计
如果需要计算数据不同分区的运行总计，一种方法是在连接中使用更多条件。 例如，如果将为奇数和偶数计算运行总计，则相关标量查询可能如下所示
--------------------------------------------------------------------
-- correlated scalar, partitioning
--------------------------------------------------------------------
SELECT a.value%2, a.id, a.value, (SELECT SUM(b.value)
FROM RunTotalTestData b
WHERE b.id <= a.id
AND b.value%2 = a.value%2)
FROM RunTotalTestData a
ORDER BY a.value%2, a.id;
结果
even id value running total
---- -- ----- -------------
0 2 2 2
0 3 4 6
0 6 12 18
0 8 16 34
0 9 22 56
0 10 42 98
0 12 58 156
0 14 60 216
1 1 1 1
1 4 7 8
1 5 9 17
1 7 13 30
1 11 57 87
1 13 59 146
因此，现在分区条件已添加到标量查询的WHERE子句中。 使用连接版本时，它可能类似于
--------------------------------------------------------------------
-- with join, partitioning
--------------------------------------------------------------------
SELECT a.value%2, a.id, a.value, SUM(b.Value)
FROM RunTotalTestData a,
RunTotalTestData b
WHERE b.id <= a.id
AND b.value%2 = a.value%2
GROUP BY a.value%2, a.id, a.value
ORDER BY a.value%2, a.id;
使用 SQL Server 2012
SQL Server 2012使生活更加简单。 使用此版本，可以在OVER子句中定义一个ORDER BY子句。
因此，要获取所有行的运行总计，查询将如下所示
--------------------------------------------------------------------
-- Using OVER clause
--------------------------------------------------------------------
SELECT a.id, a.value, SUM(a.value) OVER (ORDER BY a.id)
FROM RunTotalTestData a
ORDER BY a.id;
该语法允许定义分区的排序（在此示例中包括所有行），并以该顺序计算摘要。
要为数据定义条件，不再需要重复。 奇数的运行总计将如下所示
--------------------------------------------------------------------
-- Using OVER clause, subset
--------------------------------------------------------------------
SELECT a.id, a.value, SUM(a.value) OVER (ORDER BY a.id)
FROM RunTotalTestData a
WHERE a.value % 2 = 1
ORDER BY a.id;
最后，分区将是
--------------------------------------------------------------------
-- Using OVER clause, partition
--------------------------------------------------------------------
SELECT a.value%2, a.id, a.value, SUM(a.value) OVER (PARTITION BY a.value%2 ORDER BY a.id)
FROM RunTotalTestData a
ORDER BY a.value%2, a.id;
计划呢？ 看起来很不一样。 例如，所有行的简单运行总计如下所示
以及统计信息
Table 'Worktable'. Scan count 15, logical reads 85, physical reads 0...
Table 'RunTotalTestData'. Scan count 1, logical reads 2, physical reads 0...
即使扫描计数乍一看很高，它也不会针对实际表，而是针对工作表。 工作表用于存储中间结果，然后读取这些结果以创建计算结果。
此查询的计算成本现在为0.0033428，而以前使用连接版本时为0.0087618。 相当大的改进。
参考文献
历史
- 2011年12月16日：创建。
- 2014年12月13日：更新了无效的引用链接