[解決済み] SQL Serverで実行中の合計を計算する

質問

次のような表があるとします（名称は 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で使用することができます。

特に、aggregating-set-statementのトリックを使ったこの手の手法に興味があります。

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 文は、行を正しい順序で処理します。この問題については、何か明確な答えが得られるかもしれませんね。

でも、もしかしたら他の方法を提案できる人がいるかもしれませんね？

edit: 現在では SqlFiddle を設定し、上記の「update trick」の例とします。

解決するには？

更新情報 SQL Server 2012 を実行している場合は、こちらをご覧ください。 https://stackoverflow.com/a/10309947

問題は、SQL Server の Over 節の実装が やや限定的 .

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は、この問題に対するきれいな解決策を与えてくれません。私の直感では、これはカーソルが最も速い稀なケースの1つだと思いますが、大きな結果についていくつかのベンチマークを行う必要があるでしょう。

更新のトリックは便利ですが、かなり壊れやすいと感じます。テーブル全体を更新する場合、主キーの順番で更新されるようです。つまり、日付を主キーの昇順に設定すると、次のようになります。 probably は安全です。しかし、あなたは文書化されていないSQL Serverの実装の詳細に依存しています（また、クエリが2つのプロックによって実行されることになった場合、何が起こるのでしょうか、参照してください。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

ベンチマークを求められたので......これがその概要です。

SAFEで最も高速な方法はCursorで、cross-joinの相関サブクエリよりも1桁高速です。

絶対的に早いのは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