Imagine the following table (called TestTable):
想象一下下表(称为TestTable):
id somedate somevalue-- -------- ---------45 01/Jan/09 323 08/Jan/09 512 02/Feb/09 077 14/Feb/09 739 20/Feb/09 3433 02/Mar/09 6I would like a query that returns a running total in date order, like:
我想要一个以日期顺序返回运行总计的查询,例如:
id somedate somevalue runningtotal-- -------- --------- ------------45 01/Jan/09 3 323 08/Jan/09 5 812 02/Feb/09 0 877 14/Feb/09 7 15 39 20/Feb/09 34 4933 02/Mar/09 6 55I 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:
我对使用aggregate-set-statement技巧的这种方法特别感兴趣:
INSERT INTO @AnotherTbl(id, somedate, somevalue, runningtotal) SELECT id, somedate, somevalue, null FROM TestTable ORDER BY somedateDECLARE @RunningTotal intSET @RunningTotal = 0UPDATE @AnotherTblSET @RunningTotal = runningtotal = @RunningTotal + somevalueFROM @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
110
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 TableSQL 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 = 0update #t set running_total = @total, @total = @total + total select * from #torder by ord ord total running_total----------- ----------- -------------1 10 102 20 303 10 404 1 41You 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.
最快的SAFE方法是Cursor,它比交叉连接的相关子查询快一个数量级。
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.
绝对最快的方法是UPDATE技巧。我唯一担心的是,我不确定在任何情况下,更新都将以线性方式进行。查询中没有任何内容明确说明。
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 intset @i = 0 begin tranwhile @i < 10000begin insert #t (ord, total) values (@i, rand() * 100) set @i = @i +1endcommitTest 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:
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 4647Test 3:
DECLARE @TotalTable table(ord int primary key, total int, running_total int)DECLARE forward_cursor CURSOR FAST_FORWARD FOR SELECT ord, totalFROM #t ORDER BY ordOPEN forward_cursor DECLARE @running_total int, @ord int, @total intSET @running_total = 0FETCH 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 ENDCLOSE forward_cursorDEALLOCATE forward_cursorSELECT * FROM @TotalTable-- CPU 359, Reads 30392, Duration 496Test 4:
declare @total int set @total = 0update #t set running_total = @total, @total = @total + total select * from #t-- CPU 0, Reads 58, Duration 139#2
#3
37
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的我们来说,它仍然是获得总计运行速度的最快方式,它比我的工作计算机上的光标快10倍,对于100000行,它也是内联查询。所以,这里是(我假设表中有一个ord列,它的顺序号没有间隙,为了快速处理,这个数字也应该有唯一约束):
;with CTE_RunningTotalas( 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_totalfrom CTE_RunningTotal as Coption (maxrecursion 0)-- CPU 140, Reads 110014, Duration 132sql小提琴演示
updateI 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:
updateI也对此更新感到好奇,包括变量或奇怪的更新。所以通常它工作正常,但我们如何确保它每次都有效?好吧,这里有一个小技巧(在这里找到 - http://www.sqlservercentral.com/Forums/Topic802558-203-21.aspx#bm981258) - 您只需检查当前和之前的ord并使用1/0分配,以防万一与你的期望不同:
declare @total int, @ord intselect @total = 0, @ord = -1update #t set @total = @total + total, @ord = case when ord <> @ord + 1 then 1/0 else ord end, ------------------------ running_total = @totalselect * from #t-- CPU 0, Reads 58, Duration 139From 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的索引),更新将以线性方式一直进行(从未遇到除以零)。也就是说,由您来决定是否要在生产代码中使用它:)
#4
26
The APPLY operator in SQL 2005 and higher works for this:
SQL 2005及更高版本中的APPLY运算符适用于此:
select t.id , t.somedate , t.somevalue , rt.runningTotalfrom TestTable t cross apply (select sum(somevalue) as runningTotal from TestTable where somedate <= t.somedate ) as rtorder by t.somedate#5
10
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 aYou 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
5
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_totalFROM TestTable t1GROUP BY somedateORDER BY somedateThe 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_totalFROM TestTable t1ORDER BY somedate#7
4
You can also denormalize - store running totals in the same table:
您还可以非规范化 - 在同一个表中存储运行总计:
Selects work much faster than any other solutions, but modifications may be slower
选择工作的速度比任何其他解决方案快得多,但修改可能会更慢
#8
3
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 testtableorder 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
2
The following will produce the required results.
以下将产生所需的结果。
SELECT a.SomeDate, a.SomeValue, SUM(b.SomeValue) AS RunningTotalFROM TestTable aCROSS JOIN TestTable bWHERE (b.SomeDate <= a.SomeDate) GROUP BY a.SomeDate,a.SomeValueORDER BY a.SomeDate,a.SomeValueHaving a clustered index on SomeDate will greatly improve the performance.
在SomeDate上拥有聚簇索引将极大地提高性能。
#10
2
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 runningtotalFrom TestTable LAG is use to get previous row value. You can do google for more info.
LAG用于获取上一行值。你可以谷歌获取更多信息。
[1]:
#11
1
I believe a running total can be achieved using the simple INNER JOIN operation below.
我相信使用下面的简单INNER JOIN操作可以实现运行总计。
SELECT ROW_NUMBER() OVER (ORDER BY SomeDate) AS OrderID ,rt.*INTO #tmpFROM ( 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 ) rtSELECT t1.ID ,t1.SomeDate ,t1.SomeValue ,SUM(t2.SomeValue) AS RunningTotalFROM #tmp t1 JOIN #tmp t2 ON t2.OrderID <= t1.OrderIDGROUP BY t1.OrderID ,t1.ID ,t1.SomeDate ,t1.SomeValueORDER BY t1.OrderIDDROP TABLE #tmp#12
1
Using joinAnother variation is to use join. Now the query could look like:
使用joinAnother变体是使用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 linkhttp://askme.indianyouth.info/details/calculating-simple-running-totals-in-sql-server-12
更多你可以访问这个链接http://askme.indianyouth.info/details/calculating-simple-running-totals-in-sql-server-12
#13
1
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 runningtotalfrom testtable as t1order by id,someday;#14
0
BEGIN TRANCREATE 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 ALLSELECT 23 , '08/Jan/09', 5 UNION ALLSELECT 12 , '02/Feb/09', 0 UNION ALLSELECT 77 , '14/Feb/09', 7 UNION ALLSELECT 39 , '20/Feb/09', 34 UNION ALLSELECT 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 CTEROLLBACK TRAN#1
110
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 TableSQL 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 = 0update #t set running_total = @total, @total = @total + total select * from #torder by ord ord total running_total----------- ----------- -------------1 10 102 20 303 10 404 1 41You 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.
最快的SAFE方法是Cursor,它比交叉连接的相关子查询快一个数量级。
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.
绝对最快的方法是UPDATE技巧。我唯一担心的是,我不确定在任何情况下,更新都将以线性方式进行。查询中没有任何内容明确说明。
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 intset @i = 0 begin tranwhile @i < 10000begin insert #t (ord, total) values (@i, rand() * 100) set @i = @i +1endcommitTest 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:
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 4647Test 3:
DECLARE @TotalTable table(ord int primary key, total int, running_total int)DECLARE forward_cursor CURSOR FAST_FORWARD FOR SELECT ord, totalFROM #t ORDER BY ordOPEN forward_cursor DECLARE @running_total int, @ord int, @total intSET @running_total = 0FETCH 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 ENDCLOSE forward_cursorDEALLOCATE forward_cursorSELECT * FROM @TotalTable-- CPU 359, Reads 30392, Duration 496Test 4:
declare @total int set @total = 0update #t set running_total = @total, @total = @total + total select * from #t-- CPU 0, Reads 58, Duration 139#2
102
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 runningtotalfrom TestTable#3
37
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的我们来说,它仍然是获得总计运行速度的最快方式,它比我的工作计算机上的光标快10倍,对于100000行,它也是内联查询。所以,这里是(我假设表中有一个ord列,它的顺序号没有间隙,为了快速处理,这个数字也应该有唯一约束):
;with CTE_RunningTotalas( 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_totalfrom CTE_RunningTotal as Coption (maxrecursion 0)-- CPU 140, Reads 110014, Duration 132sql小提琴演示
updateI 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:
updateI也对此更新感到好奇,包括变量或奇怪的更新。所以通常它工作正常,但我们如何确保它每次都有效?好吧,这里有一个小技巧(在这里找到 - http://www.sqlservercentral.com/Forums/Topic802558-203-21.aspx#bm981258) - 您只需检查当前和之前的ord并使用1/0分配,以防万一与你的期望不同:
declare @total int, @ord intselect @total = 0, @ord = -1update #t set @total = @total + total, @ord = case when ord <> @ord + 1 then 1/0 else ord end, ------------------------ running_total = @totalselect * from #t-- CPU 0, Reads 58, Duration 139From 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的索引),更新将以线性方式一直进行(从未遇到除以零)。也就是说,由您来决定是否要在生产代码中使用它:)
#4
26
The APPLY operator in SQL 2005 and higher works for this:
SQL 2005及更高版本中的APPLY运算符适用于此:
select t.id , t.somedate , t.somevalue , rt.runningTotalfrom TestTable t cross apply (select sum(somevalue) as runningTotal from TestTable where somedate <= t.somedate ) as rtorder by t.somedate#5
10
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 aYou 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
5
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_totalFROM TestTable t1GROUP BY somedateORDER BY somedateThe 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_totalFROM TestTable t1ORDER BY somedate#7
4
You can also denormalize - store running totals in the same table:
您还可以非规范化 - 在同一个表中存储运行总计:
Selects work much faster than any other solutions, but modifications may be slower
选择工作的速度比任何其他解决方案快得多,但修改可能会更慢
#8
3
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 testtableorder 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
2
The following will produce the required results.
以下将产生所需的结果。
SELECT a.SomeDate, a.SomeValue, SUM(b.SomeValue) AS RunningTotalFROM TestTable aCROSS JOIN TestTable bWHERE (b.SomeDate <= a.SomeDate) GROUP BY a.SomeDate,a.SomeValueORDER BY a.SomeDate,a.SomeValueHaving a clustered index on SomeDate will greatly improve the performance.
在SomeDate上拥有聚簇索引将极大地提高性能。
#10
2
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 runningtotalFrom TestTable LAG is use to get previous row value. You can do google for more info.
LAG用于获取上一行值。你可以谷歌获取更多信息。
[1]:
#11
1
I believe a running total can be achieved using the simple INNER JOIN operation below.
我相信使用下面的简单INNER JOIN操作可以实现运行总计。
SELECT ROW_NUMBER() OVER (ORDER BY SomeDate) AS OrderID ,rt.*INTO #tmpFROM ( 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 ) rtSELECT t1.ID ,t1.SomeDate ,t1.SomeValue ,SUM(t2.SomeValue) AS RunningTotalFROM #tmp t1 JOIN #tmp t2 ON t2.OrderID <= t1.OrderIDGROUP BY t1.OrderID ,t1.ID ,t1.SomeDate ,t1.SomeValueORDER BY t1.OrderIDDROP TABLE #tmp#12
1
Using joinAnother variation is to use join. Now the query could look like:
使用joinAnother变体是使用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 linkhttp://askme.indianyouth.info/details/calculating-simple-running-totals-in-sql-server-12
更多你可以访问这个链接http://askme.indianyouth.info/details/calculating-simple-running-totals-in-sql-server-12
#13
1
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 runningtotalfrom testtable as t1order by id,someday;#14
0
BEGIN TRANCREATE 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 ALLSELECT 23 , '08/Jan/09', 5 UNION ALLSELECT 12 , '02/Feb/09', 0 UNION ALLSELECT 77 , '14/Feb/09', 7 UNION ALLSELECT 39 , '20/Feb/09', 34 UNION ALLSELECT 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 CTEROLLBACK TRAN