计算SQL Server中的运行总数

Imagine the following table (called TestTable):

想象如下表(称为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

I would like a query that returns a running total in date order, like:

我希望查询以日期顺序返回运行的总数，如:

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

I know there are various ways of doing this in SQL Server 2000 / 2005 / 2008.

我知道在SQL Server 2000 / 2005 / 2008中有很多方法可以做到这一点。

I am particularly interested in this sort of method that uses the aggregating-set-statement trick:

我对这种方法特别感兴趣，它使用了聚集集语句的技巧:

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

... this is very efficient but I have heard there are issues around this because you can't necessarily guarantee that the UPDATE statement will process the rows in the correct order. Maybe we can get some definitive answers about that issue.

…这是非常有效的，但是我听说有一些问题，因为您不能保证UPDATE语句将按照正确的顺序处理行。也许我们可以就这个问题得到一些肯定的答案。

But maybe there are other ways that people can suggest?

但也许人们还有其他的建议?

edit: Now with a SqlFiddle with the setup and the 'update trick' example above

编辑:现在有一个SqlFiddle在上面的设置和“更新技巧”示例

14 个解决方案

#1

106

Update, if you are running SQL Server 2012 see: https://*.com/a/10309947

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

The problem is that the SQL Server implementation of the Over clause is somewhat limited.

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

Oracle (and ANSI-SQL) allow you to do things like:

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 gives you no clean solution to this problem. My gut is telling me that this is one of those rare cases where a cursor is the fastest, though I will have to do some benchmarking on big results.

SQL Server没有为这个问题提供干净的解决方案。我的直觉告诉我，这是少数几个光标速度最快的例子之一，尽管我将不得不对大结果做一些基准测试。

The update trick is handy but I feel its fairly fragile. It seems that if you are updating a full table then it will proceed in the order of the primary key. So if you set your date as a primary key ascending you will probably be safe. But you are relying on an undocumented SQL Server implementation detail (also if the query ends up being performed by two procs I wonder what will happen, see: MAXDOP):

更新技巧很方便，但我觉得它相当脆弱。如果您正在更新一个完整的表，那么它将按照主键的顺序进行。因此，如果你将日期设置为主键升序，那么你很可能是安全的。但是，您依赖的是一个无文档的SQL Server实现细节(如果查询最终由两个proc执行，我想知道会发生什么情况，请参见:MAXDOP):

Full working sample:

完整的工作示例:

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

You asked for a benchmark this is the lowdown.

你要的是基准这是内幕。

The fastest SAFE way of doing this would be the Cursor, it is an order of magnitude faster than the correlated sub-query of cross-join.

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

The absolute fastest way is the UPDATE trick. My only concern with it is that I am not certain that under all circumstances the update will proceed in a linear way. There is nothing in the query that explicitly says so.

绝对最快的方法是更新技巧。我唯一关心的是，我不确定在任何情况下更新都将以线性方式进行。查询中没有任何东西明确地这样说。

Bottom line, for production code I would go with the cursor.

底线是，对于生产代码，我将使用游标。

Test data:

测试数据:

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

Test 1:

测试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

Test 2:

测试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

Test 3:

测试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

Test 4:

测试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

#2

100

In SQL Server 2012 you can use SUM() with the OVER() clause.

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

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

SQL Fiddle

SQL小提琴

#3

While Sam Saffron did great work on it, he still didn't provide recursive common table expression code for this problem. And for us who working with SQL Server 2008 R2 and not Denali, it's still fastest way to get running total, it's about 10 times faster than cursor on my work computer for 100000 rows, and it's also inline query.
So, here it is (I'm supposing that there's an ord column in the table and it's sequential number without gaps, for fast processing there also should be unique constraint on this number):

虽然Sam Saffron在这方面做了很多工作，但是他仍然没有为这个问题提供递归的通用表表达式代码。对于我们使用SQL Server 2008 R2而不是Denali的人来说，这仍然是获得运行total的最快方式，它比我的工作计算机上的指针快10倍，有100000行，它也是内联查询。这里(我假设表中有一个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 demo

sql小提琴演示

update I also was curious about this update with variable or quirky update. So usually it works ok, but how we can be sure that it works every time? well, here's a little trick (found it here - http://www.sqlservercentral.com/Forums/Topic802558-203-21.aspx#bm981258) - you just check current and previous ord and use 1/0 assignment in case they are different from what you expecting:

我还对这个带有变量或奇怪更新的更新感到好奇。通常情况下它是可以工作的，但是我们怎么能保证每次都能工作呢?这里有一个小技巧(在这里找到——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

From what I've seen if you have proper clustered index/primary key on your table (in our case it would be index by ord_id) update will proceed in a linear way all the time (never encountered divide by zero). That said, it's up to you to decide if you want to use it in production code :)

根据我所看到的，如果您的表上有合适的聚集索引/主键(在我们的例子中，它是由ord_id组成的索引)，那么更新将一直以线性方式进行(从未遇到过除以0)。也就是说，你要决定是否在生产代码中使用它:)

#4

The APPLY operator in SQL 2005 and higher works for this:

在SQL 2005和更高版本的应用操作符:

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

#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

You can also use the ROW_NUMBER() function and a temp table to create an arbitrary column to use in the comparison on the inner SELECT statement.

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

#6

Use a correlated sub-query. Very simple, here you go:

使用相关子查询。很简单，给你:

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

The code might not be exactly correct, but I'm sure that the idea is.

代码可能不完全正确，但我确信其思想是正确的。

The GROUP BY is in case a date appears more than once, you would only want to see it once in the result set.

如果一个日期出现了多次，则GROUP BY只希望在结果集中看到一次。

If you don't mind seeing repeating dates, or you want to see the original value and id, then the following is what you want:

如果你不介意看到重复的日期，或者你想看到原始的值和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

#7

You can also denormalize - store running totals in the same table:

您还可以反规范化——在同一个表中存储运行总数:

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

Selects work much faster than any other solutions, but modifications may be slower

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

#8

Assuming that windowing works on SQL Server 2008 like it does elsewhere (that I've tried), give this a go:

假设在SQL Server 2008上的窗口工作和其他地方一样(我已经尝试过)，请尝试一下:

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

MSDN says it's available in SQL Server 2008 (and maybe 2005 as well?) but I don't have an instance to hand to try it.

MSDN说它可以在SQL Server 2008中使用(也可能是2005年?)，但是我没有一个实例来尝试它。

EDIT: well, apparently SQL Server doesn't allow a window specification ("OVER(...)") without specifying "PARTITION BY" (dividing the result up into groups but not aggregating in quite the way GROUP BY does). Annoying-- the MSDN syntax reference suggests that its optional, but I only have SqlServer 2000 instances around at the moment.

编辑:显然，SQL Server不允许窗口规范(“OVER(…)”)而不指定“PARTITION BY”(将结果分成组，但不像GROUP BY那样进行聚合)。恼人的——MSDN语法引用表明它是可选的，但是目前我只有SqlServer 2000实例。

The query I gave works in both Oracle 10.2.0.3.0 and PostgreSQL 8.4-beta. So tell MS to catch up ;)

我给出的查询在Oracle 10.2.0.3.0和PostgreSQL 8.4-beta中都有效。所以告诉MS去追上;)

#9

The following will produce the required results.

下面将产生所需的结果。

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

Having a clustered index on SomeDate will greatly improve the performance.

在某个日期拥有集群索引将极大地提高性能。

#10

I believe a running total can be achieved using the simple INNER JOIN operation below.

我相信使用下面简单的内部连接操作就可以实现运行总数。

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

#11

Using join Another variation is to use join. Now the query could look like:

使用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;

for more you can visite this link http://askme.indianyouth.info/details/calculating-simple-running-totals-in-sql-server-12

更多信息，请访问这个链接http://askme.indianyouth.info/details/calculating-simple-run - total-sql -server-12

#12

If you are using Sql server 2008 R2 above. Then, It would be shortest way to do;

如果您正在使用上面的Sql server 2008 R2。那么，这是最短的方法;

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

LAG is use to get previous row value. You can do google for more info.

延迟是用来获取先前的行值。你可以用谷歌获取更多信息。

[1]:

#13

Though best way is to get it done will be using a window function, it can also be done using a simple correlated sub-query.

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

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;

#14

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

#1

106