在 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 以及上面的设置和“更新技巧”示例

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

更新技巧很方便，但我觉得它相当脆弱。 看来，如果您要更新完整的表，那么它将按照主键的顺序进行。 因此，如果您将日期设置为主键升序，您可能会是安全的。 但是您依赖于未记录的 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

您要求提供基准，这就是真相。

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

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

最重要的是，对于生产代码，我会使用光标。

测试数据：

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

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

Answer 3

尽管 Sam Saffron 在这方面做了出色的工作，但他仍然没有为这个问题提供递归公用表表达式代码。 对于我们使用 SQL Server 2008 R2 而不是 Denali 的人来说，它仍然是获得运行总计的最快方法，对于 100000 行，它比我工作计算机上的光标快大约 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

< kbd>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 我链接了这个答案，因为它包含一些有关奇怪更新不可靠性的有用信息 - < a href="https://stackoverflow.com/a/15163136/1744834">nvarchar 连接/索引/nvarchar(max) 令人费解的行为。

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

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 语句的比较中使用。

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

Answer 7

您还可以反规范化 - 将运行总计存储在同一个表中：

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/23/denormalizing-to-enforce-business-rules-running-totals.aspx

选择工作速度比任何其他解决方案快得多，但修改可能会更慢

Answer 8

如果您使用的是Sql server 2008 R2以上版本。 那么，这将是最短的方法；

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

LAG 用于获取上一个行值。 你可以谷歌一下以获取更多信息。

[1]：

Answer 9

假设窗口在 SQL Server 2008 上工作就像在其他地方（我已经尝试过）一样，请尝试一下：

select testtable.*, sum(somevalue) over(order by somedate)
from testtable
order by somedate;

MSDN 说它在 SQL Server 2008 中可用（也许 2005 也可用？），但我没有实例可以尝试它。

编辑：嗯，显然 SQL Server 不允许窗口规范（“OVER(...)”）而不指定“PARTITION BY”（将结果分成组，但不以 GROUP BY 的方式聚合）。 烦人——MSDN 语法参考表明它是可选的，但我目前只有 SqlServer 2000 实例。

我给出的查询适用于 Oracle 10.2.0.3.0 和 PostgreSQL 8.4-beta。 所以告诉 MS 赶上;)

Answer 10

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

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;

Answer 11

这里有 2 种计算运行总计的简单方法：

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

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

方法 2：如果满足以下条件，您可以使用 OUTER APPLY：您的数据库版本/DBMS 本身不支持分析函数

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 分组

Answer 12

我相信使用下面简单的 INNER JOIN 操作可以实现运行总计。

SELECT
     ROW_NUMBER() OVER (ORDER BY SomeDate) AS OrderID
    ,rt.*
INTO
    #tmp
FROM
    (
        SELECT 45 AS ID, CAST('01-01-2009' AS DATETIME) AS SomeDate, 3 AS SomeValue
        UNION ALL
        SELECT 23, CAST('01-08-2009' AS DATETIME), 5
        UNION ALL
        SELECT 12, CAST('02-02-2009' AS DATETIME), 0
        UNION ALL
        SELECT 77, CAST('02-14-2009' AS DATETIME), 7
        UNION ALL
        SELECT 39, CAST('02-20-2009' AS DATETIME), 34
        UNION ALL
        SELECT 33, CAST('03-02-2009' AS DATETIME), 6
    ) rt

SELECT
     t1.ID
    ,t1.SomeDate
    ,t1.SomeValue
    ,SUM(t2.SomeValue) AS RunningTotal
FROM
    #tmp t1
    JOIN #tmp t2
        ON t2.OrderID <= t1.OrderID
GROUP BY
     t1.OrderID
    ,t1.ID
    ,t1.SomeDate
    ,t1.SomeValue
ORDER BY
    t1.OrderID

DROP TABLE #tmp

Answer 13

以下将产生所需的结果。

SELECT a.SomeDate,
       a.SomeValue,
       SUM(b.SomeValue) AS RunningTotal
FROM TestTable a
CROSS JOIN TestTable b
WHERE (b.SomeDate <= a.SomeDate) 
GROUP BY a.SomeDate,a.SomeValue
ORDER BY a.SomeDate,a.SomeValue

在 SomeDate 上拥有聚集索引将大大提高性能。

Answer 14

使用连接
另一种变体是使用连接。 现在查询可能如下所示：

    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;

有关更多信息，您可以访问此链接
http://askme.indianyouth.info/details /计算-简单-运行-总计-in-sql-server-12

Answer 15

BEGIN TRAN
CREATE TABLE #Table (_Id INT IDENTITY(1,1) ,id INT ,    somedate VARCHAR(100) , somevalue INT)


INSERT INTO #Table ( id  ,    somedate  , somevalue  )
SELECT 45 , '01/Jan/09', 3 UNION ALL
SELECT 23 , '08/Jan/09', 5 UNION ALL
SELECT 12 , '02/Feb/09', 0 UNION ALL
SELECT 77 , '14/Feb/09', 7 UNION ALL
SELECT 39 , '20/Feb/09', 34 UNION ALL
SELECT 33 , '02/Mar/09', 6 

;WITH CTE ( _Id, id  ,  _somedate  , _somevalue ,_totvalue ) AS
(

 SELECT _Id , id  ,    somedate  , somevalue ,somevalue
 FROM #Table WHERE _id = 1
 UNION ALL
 SELECT #Table._Id , #Table.id  , somedate  , somevalue , somevalue + _totvalue
 FROM #Table,CTE 
 WHERE #Table._id > 1 AND CTE._Id = ( #Table._id-1 )
)

SELECT * FROM CTE

ROLLBACK TRAN