SQL Server中计算累积总和的多种方法

SQL Server中,计算累积总和是一个常见的需求。本文将介绍几种实现这一功能的方法,并通过示例进行说明。首先,需要创建一个测试表,并填充一些数据。

创建测试表

CREATE TABLE RunTotalTestData ( id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, value INT NOT NULL );

填充测试数据:

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字段的升序获取累积总和。

使用相关标量查询

一种传统的方法是使用相关标量查询来获取到目前为止的累积总和。查询可能如下所示:

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

查询结果中,除了实际的行值外,还包含了累积总和。标量查询简单地获取了当前行值的ID小于或等于的行的value字段的总和。让看看执行计划:

数据库获取表中的所有行,并使用嵌套循环再次获取用于计算总和的行。这也可以在统计信息中看到:

表 'RunTotalTestData'。扫描次数15,逻辑读取30,物理读取0...

使用JOIN

另一种变体是使用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值相等或更小的行进行连接。计划如下:

计划看起来有些不同,实际上表只读取了两次。这可以通过统计信息更清楚地看到。

表 'RunTotalTestData'。扫描次数2,逻辑读取31...

相关标量查询的计算成本为0.0087873,而JOIN版本的成本为0.0087618。差异不大,但必须记住,处理的是极小的数据量。

使用条件

在现实生活场景中,通常使用限制条件,那么这些查询是如何应用条件的呢。基本规则是条件必须在这两种变体中定义两次。一次用于获取行,第二次用于计算总和的行。

如果想计算奇数值的累积总和,相关标量版本可能如下所示:

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 running total
1 1 1
4 7 8
5 9 17
7 13 30
11 57 87
13 59 146

使用JOIN版本,可能是这样的:

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;

当实际上有更多的条件时,正确维护条件可能会相当痛苦。特别是如果它们是动态构建的。

计算数据分区的累积总和

如果需要为数据的不同分区计算累积总和,一种方法是在连接中使用更多的条件。例如,如果需要为奇数和偶数计算累积总和,相关标量查询可能如下所示:

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子句中。当使用JOIN版本时,可能类似于:

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 Server2012

SQL Server2012使生活变得更加简单。在该版本中,可以在OVER子句中定义ORDER BY子句。

要获取所有行的累积总和,查询可能如下所示:

SELECT a.id, a.value, SUM(a.value) OVER ( ORDER BY a.id ) FROM RunTotalTestData a ORDER BY a.id;

语法允许定义分区的排序(在这个例子中包括所有行),然后按该顺序计算汇总。

要为数据定义条件,不再需要重复。奇数的累积总和可能如下所示:

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;

最后,分区可能是:

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;

计划看起来非常不同。例如,所有行的简单累积总和看起来像:

表 'Worktable'。扫描次数15,逻辑读取85,物理读取0...

表 'RunTotalTestData'。扫描次数1,逻辑读取2,物理读取0...

尽管乍一看扫描次数相当高,但它并不是针对实际表,而是一个工作表。工作表用于存储中间结果,然后读取这些结果以创建计算结果。

此查询的计算成本现在为0.0033428,而之前使用JOIN版本时为0.0087618。相当大的改进。

  • SUM (Transact-SQL)
  • OVER Clause (Transact-SQL)
沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485