在 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 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

更新，如果您正在运行 SQL Server 2012，请参阅： https ://stackoverflow.com/a/10309947

问题是 Over 子句的 SQL Server 实现 有些受限。

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 没有为您提供此问题的干净解决方案。我的直觉告诉我，这是光标最快的罕见情况之一，尽管我必须对大结果进行一些基准测试。

更新技巧很方便，但我觉得它相当脆弱。似乎如果您要更新一个完整的表，那么它将按照主键的顺序进行。因此，如果您将日期设置为主键升序，您将 probably 是安全的。但是您依赖于未记录的 SQL Server 实现细节（如果查询最终由两个 proc 执行，我想知道会发生什么，请参阅：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 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

这里有两种简单的方法来计算运行总数：

方法 1 ：如果您的 DBMS 支持分析函数，则可以这样编写

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

方法 2 ：如果您的数据库版本/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 发布，翻译遵循 CC BY-SA 4.0 许可协议