在SQL Server中计算累计总数

Question

想象一下以下表格（名为TestTable）：

id     somedate    somevalue
--     --------    ---------
45     01/Jan/09   3
23     08/Jan/09   5
12     02/Feb/09   0
77     14/Feb/09   7
39     20/Feb/09   34
33     02/Mar/09   6

我想要一个按日期顺序返回累计总数的查询，例如：

id     somedate    somevalue  runningtotal
--     --------    ---------  ------------
45     01/Jan/09   3          3
23     08/Jan/09   5          8
12     02/Feb/09   0          8
77     14/Feb/09   7          15  
39     20/Feb/09   34         49
33     02/Mar/09   6          55

我知道在SQL Server 2000/2005/2008中有各种方法可以实现这个功能。

我特别感兴趣的是使用聚合集合语句技巧的这种方法：

INSERT INTO @AnotherTbl(id, somedate, somevalue, runningtotal) 
   SELECT id, somedate, somevalue, null
   FROM TestTable
   ORDER BY somedate

DECLARE @RunningTotal int
SET @RunningTotal = 0

UPDATE @AnotherTbl
SET @RunningTotal = runningtotal = @RunningTotal + somevalue
FROM @AnotherTbl

...这是非常高效的，但我听说存在一些问题，因为不能保证UPDATE语句会按照正确的顺序处理行。也许我们可以得到一些关于这个问题的明确答案。

但也许还有其他人可以建议的方法？

编辑：现在有一个SqlFiddle，其中包含设置和上面的“更新技巧”示例

- codeulike

---

在你的update...set语句中添加一个order by，你就能得到一个保证。 - Simon D

但是 Order by 不能应用于 UPDATE 语句... 是吗？ - codeulike

如果您正在使用SQL Server 2012，请参阅http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals，了解更多信息。 - Aaron Bertrand

Answer 1

更新，如果你正在运行SQL Server 2012，请参考：https://dev59.com/-XRA5IYBdhLWcg3wsgHq#10309947

问题在于SQL Server实现的Over子句相对有限。

Oracle（和ANSI-SQL）允许你做一些事情：

 SELECT somedate, somevalue,
  SUM(somevalue) OVER(ORDER BY somedate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM Table

SQL Server没有为这个问题提供一个清洁的解决方案。我的直觉告诉我，这是那些罕见情况之一，其中光标是最快的，不过我将不得不对大型结果进行一些基准测试。

更新技巧很方便，但我感觉它相当脆弱。似乎如果您要更新整张表，则它会按照主键的顺序进行。因此，如果您将日期设置为升序的主键，则可能是安全的。但您正在依赖未经记录的SQL Server实现细节(另外，如果查询最终由两个进程执行，我想知道会发生什么，参见: MAXDOP):

完整的工作示例：

drop table #t 
create table #t ( ord int primary key, total int, running_total int)

insert #t(ord,total)  values (2,20)
-- notice the malicious re-ordering 
insert #t(ord,total) values (1,10)
insert #t(ord,total)  values (3,10)
insert #t(ord,total)  values (4,1)

declare @total int 
set @total = 0
update #t set running_total = @total, @total = @total + total 

select * from #t
order by ord 

ord         total       running_total
----------- ----------- -------------
1           10          10
2           20          30
3           10          40
4           1           41

您要求进行基准测试，以下是简要说明。

最快且安全的方法是使用游标，它比交叉联接的相关子查询快一个数量级。

绝对最快的方法是使用 UPDATE 技巧。我唯一担心的是，在所有情况下都不能确定更新是否会以线性方式进行。在查询中没有明确表明这一点。

总之，对于生产代码，我建议使用游标。

测试数据：

create table #t ( ord int primary key, total int, running_total int)

set nocount on 
declare @i int
set @i = 0 
begin tran
while @i < 10000
begin
   insert #t (ord, total) values (@i,  rand() * 100) 
    set @i = @i +1
end
commit

测试1：

SELECT ord,total, 
    (SELECT SUM(total) 
        FROM #t b 
        WHERE b.ord <= a.ord) AS b 
FROM #t a

-- CPU 11731, Reads 154934, Duration 11135 

测试2：

SELECT a.ord, a.total, SUM(b.total) AS RunningTotal 
FROM #t a CROSS JOIN #t b 
WHERE (b.ord <= a.ord) 
GROUP BY a.ord,a.total 
ORDER BY a.ord

-- CPU 16053, Reads 154935, Duration 4647

测试3：

DECLARE @TotalTable table(ord int primary key, total int, running_total int)

DECLARE forward_cursor CURSOR FAST_FORWARD 
FOR 
SELECT ord, total
FROM #t 
ORDER BY ord


OPEN forward_cursor 

DECLARE @running_total int, 
    @ord int, 
    @total int
SET @running_total = 0

FETCH NEXT FROM forward_cursor INTO @ord, @total 
WHILE (@@FETCH_STATUS = 0)
BEGIN
     SET @running_total = @running_total + @total
     INSERT @TotalTable VALUES(@ord, @total, @running_total)
     FETCH NEXT FROM forward_cursor INTO @ord, @total 
END

CLOSE forward_cursor
DEALLOCATE forward_cursor

SELECT * FROM @TotalTable

-- CPU 359, Reads 30392, Duration 496

测试 4：

declare @total int 
set @total = 0
update #t set running_total = @total, @total = @total + total 

select * from #t

-- CPU 0, Reads 58, Duration 139

- Sam Saffron

---

谢谢。所以你的代码示例是为了演示它将按主键顺序求和，我想。有趣的是要知道在处理更大的数据集时，游标是否仍然比连接更有效率。 - codeulike

我刚刚测试了CTE @Martin，没有任何方法可以接近更新技巧 - 光标在读取方面似乎更低。这是一个分析器跟踪http://i.stack.imgur.com/BbZq3.png - Sam Saffron

@Martin Denali将会为这个问题提供一个相当不错的解决方案。链接如下：http://msdn.microsoft.com/en-us/library/ms189461(v=SQL.110).aspx - Sam Saffron

+1 针对这个回答所做的所有工作 - 我喜欢 UPDATE 选项；在这个 UPDATE 脚本中可以建立一个分区吗？例如，如果有一个额外的字段“汽车颜色”，这个脚本能否返回每个“汽车颜色”分区内的累计总数？ - whytheq

初始的（Oracle（和ANSI-SQL））答案现在在SQL Server 2017中可用。非常优雅，谢谢！ - DaniDev

Answer 2

在 SQL Server 2012 中，您可以使用 SUM() 函数和 OVER() 子句。

select id,
       somedate,
       somevalue,
       sum(somevalue) over(order by somedate rows unbounded preceding) as runningtotal
from TestTable

SQL Fiddle

- Mikael Eriksson

Answer 3

虽然Sam Saffron为此问题做出了很好的工作，但他仍未提供递归公共表达式代码。对于我们使用SQL Server 2008 R2而不是Denali的人来说，这仍然是获取运行总数的最快方法，对于我工作电脑上的10万行，它比游标快10倍，并且也是内联查询。 因此，在这里（我假设表中有一个ord列，它是连续的数字没有间隙，为了快速处理，还应该有唯一约束）：

;with 
CTE_RunningTotal
as
(
    select T.ord, T.total, T.total as running_total
    from #t as T
    where T.ord = 0
    union all
    select T.ord, T.total, T.total + C.running_total as running_total
    from CTE_RunningTotal as C
        inner join #t as T on T.ord = C.ord + 1
)
select C.ord, C.total, C.running_total
from CTE_RunningTotal as C
option (maxrecursion 0)

-- CPU 140, Reads 110014, Duration 132

SQL Fiddle演示

更新 我也对使用变量进行更新或者奇怪的更新很感兴趣。通常情况下它可以正常工作，但是我们如何确保它每次都能正常工作呢？好吧，这里有一个小技巧（在这里找到的 - http://www.sqlservercentral.com/Forums/Topic802558-203-21.aspx#bm981258）- 您只需检查当前和上一个ord并在它们与您期望的不同时使用1/0赋值：

declare @total int, @ord int

select @total = 0, @ord = -1

update #t set
    @total = @total + total,
    @ord = case when ord <> @ord + 1 then 1/0 else ord end,
    ------------------------
    running_total = @total

select * from #t

-- CPU 0, Reads 58, Duration 139

根据我所看到的，如果你在表上有适当的聚集索引/主键（在我们的情况下，它将被索引为ord_id），更新将始终以线性方式进行（从未遇到过除零错误）。也就是说，你需要自己决定是否要在生产代码中使用它 :)。

更新2：我链接了这个答案，因为它包括一些关于奇怪更新不可靠性的有用信息 - nvarchar连接/索引/nvarchar(max)的不可解释行为。

- Roman Pekar

---

是的，可能可以使用apply查询来完成，但这个查询非常快速，而关联子查询就不能这么说了 :( - Roman Pekar

如果您已经有了数据的序数，并且正在寻找 SQL 2008 R2 上简洁的（非游标）基于集合的解决方案，那么这似乎是完美的选择。 - Nick.McDermaid

并非每个运行总计查询都具有连续的序号字段。有时您拥有的是日期时间字段，或者记录已从排序中间删除。这可能就是为什么它不经常使用的原因。 - Reuben

@Reuben 如果你的表够小，你总是可以将它转储到带有顺序号的临时表中，但是有时这个解决方案可能不容易应用。 - Roman Pekar

@RomanPekar 刚刚看到你2014年的更新，包括订单检查和除零弹射座椅，太棒了！ - codeulike

Answer 4

SQL 2005及更高版本中的APPLY运算符可用于此操作：

select
    t.id ,
    t.somedate ,
    t.somevalue ,
    rt.runningTotal
from TestTable t
 cross apply (select sum(somevalue) as runningTotal
                from TestTable
                where somedate <= t.somedate
            ) as rt
order by t.somedate

- Mike Forman

---

适用于小型数据集，缺点是内部查询和外部查询必须具有相同的“where”子句。 - Sire

由于我的一些日期完全相同（精确到秒的小数），所以我不得不在内部和外部表中添加row_number() over (order by txndate)以及一些复合索引来使其运行。简单而明了的解决方案。顺便说一句，测试了交叉应用与子查询...交叉应用略快。 - pghcpa

这非常干净，适用于小数据集；比递归CTE更快。 - jtate

这也是一个不错的解决方案（适用于小数据集），但您还必须意识到它意味着日期列必须是唯一的。 - Roman Pekar

Answer 5

SELECT TOP 25   amount, 
    (SELECT SUM(amount) 
    FROM time_detail b 
    WHERE b.time_detail_id <= a.time_detail_id) AS Total FROM time_detail a

你还可以使用ROW_NUMBER()函数和临时表来创建一个任意列，以便在内部SELECT语句中进行比较。

- Sam Axe

---

这真的很低效...但是在SQL Server中没有真正干净的方法来做到这一点。 - Sam Saffron

绝对是低效的 - 但它能完成任务，而且不会有任何关于执行顺序正确与否的疑问。 - Sam Axe

谢谢，拥有不同的答案很有用，而且有效的批评也很有用。 - codeulike

Answer 6

使用相关子查询。非常简单，这里是代码：

SELECT 
somedate, 
(SELECT SUM(somevalue) FROM TestTable t2 WHERE t2.somedate<=t1.somedate) AS running_total
FROM TestTable t1
GROUP BY somedate
ORDER BY somedate

代码可能不完全正确，但我相信这个想法是正确的。

如果一个日期出现多次，使用GROUP BY可以确保在结果集中只看到一次。

如果您不介意看到重复的日期，或者想要看到原始值和ID，则以下内容是您想要的：

SELECT 
id,
somedate, 
somevalue,
(SELECT SUM(somevalue) FROM TestTable t2 WHERE t2.somedate<=t1.somedate) AS running_total
FROM TestTable t1
ORDER BY somedate

- KthProg

---

谢谢...简单就是美。为了提高性能，需要添加一个索引，但这很简单（采用了数据库引擎调整顾问的建议之一 ;)），然后它就像一枪般快速运行。 - Doug_Ivison

Answer 7

如果您使用的是 Sql server 2008 R2 及以上版本，则最简单的方法是：

Select id
    ,somedate
    ,somevalue,
LAG(runningtotal) OVER (ORDER BY somedate) + somevalue AS runningtotal
From TestTable 

LAG 用于获取上一行的值。您可以通过 Google 查找更多信息。

[1]:

- shambhu yadav

---

我相信 LAG 只存在于 SQL Server 2012 及以上版本（而非 2008）。 - AaA

使用LAG()并不能改善SUM(somevalue) OVER(...)，后者在我看来更加简洁。 - Paul Maxwell

Answer 8

您还可以进行反规范化操作-在同一表中存储运行总数： http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/23/denormalizing-to-enforce-business-rules-running-totals.aspx 查询比其他解决方案更快，但修改可能较慢。

- A-K

Answer 9

虽然最好的方法是使用窗口函数来完成，但也可以使用简单的相关子查询来完成。

Select id, someday, somevalue, (select sum(somevalue) 
                                from testtable as t2
                                where t2.id = t1.id
                                and t2.someday <= t1.someday) as runningtotal
from testtable as t1
order by id,someday;

- Krahul3

Answer 10

以下是两种简单的计算累计总数的方法：

方法一：如果您的数据库管理系统支持分析函数，则可以按以下方式编写代码：

SELECT     id
           ,somedate
           ,somevalue
           ,runningtotal = SUM(somevalue) OVER (ORDER BY somedate ASC)
FROM       TestTable

方法二：如果您的数据库版本/DBMS本身不支持分析函数，则可以使用OUTER APPLY。

SELECT     T.id
           ,T.somedate
           ,T.somevalue
           ,runningtotal = OA.runningtotal
FROM       TestTable T
           OUTER APPLY (
                           SELECT   runningtotal = SUM(TI.somevalue)
                           FROM     TestTable TI
                           WHERE    TI.somedate <= S.somedate
                       ) OA;

注：- 如果您需要分别计算不同分区的运行总数，可以按照此处发布的方法执行：计算跨行运行总数并按ID分组

- san