计算 SQLServer 中的运行总数

小开

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

小开

假设窗口在 SQLServer2008上能够像在其他地方一样工作(我已经尝试过了) ，试试这个:

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

MSDN 说它在 SQLServer2008中可用(也许在2005年也可用?)但我手头没有实例可以尝试。

编辑: 显然，SQL Server 不允许在没有指定“ PARTITION BY”(将结果分组，但不像 GROUP BY 那样聚合)的情况下使用窗口规范(“ OVER (...)”)。烦人—— MSDN 语法参考表明它是可选的，但是目前我只有 SqlServer2000实例。

我给出的查询适用于 Oracle 10.2.0.3.0和 PostgreSQL 8.4-beta

小开

最佳答案

更新，如果您正在运行 SQL Server 2012，请参阅: < a href = “ https://stackoverflow. com/a/10309947”> https://stackoverflow.com/a/10309947

问题是 Over 子句的 SQLServer 实现是有些局限。

Oracle (和 ANSI-SQL)允许您执行以下操作:

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

SQLServer 没有为此问题提供干净的解决方案。我的直觉告诉我，这是一个少见的情况下，光标是最快的，虽然我将不得不做一些基准测试的大结果。

更新技巧很方便，但我觉得它相当脆弱。看起来，如果您正在更新一个完整的表，那么它将按照主键的顺序进行。因此，如果您设置您的日期为主键升序，您将 probably是安全的。但是您依赖于一个没有文档说明的 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

测试一:

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

小开

以下将产生所需的结果。

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

在 SomDate 上拥有聚集索引将极大地提高性能。

小开

SQL2005及更高版本中的 APPY 操作符用于以下目的:

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

小开

你也可以在同一个表中反规范化-存储运行总数:

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

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

小开

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

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

小开

在 SQLServer2012中，可以将 SUM ()与完毕子句一起使用。

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

SQL Fiddle

小开

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

小提琴演示

更新我也对这个 用变量更新或者 奇怪的更新很好奇。所以通常情况下它是可以工作的，但是我们怎样才能确保它每次都可以工作呢？这里有一个小技巧(在这里找到它-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)无法解释的行为。

小开

使用一个相关的子查询，非常简单，就是这样:

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

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

GROUPBY 是在日期出现不止一次的情况下使用的，您只希望在结果集中看到它一次。

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

小开

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

你可浏览此网页了解更多资料 Http://askme.indianyouth.info/details/calculating-simple-running-totals-in-sql-server-12

小开

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

小开

如果您使用的是 Sql 服务器2008R2以上。那么，这将是最短的方法来做;

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

LAG 用于获取前一行的值。

[1] :

小开

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

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;

小开

这里有两种计算跑步总量的简单方法:

方法1 : 如果您的数据库管理系统支持分析函数，那么可以这样编写

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

方法2 : 如果您的数据库版本/DBMS 本身不支持分析函数，则可以使用 OUTER APPY

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