Sumの途中経過を表示する

棚卸数 50

日付     移動数  在庫数
10/01     100        150
10/13     80          230
10/25     -130       100
11/06     40          140
11/10     -50         90
11/13     -60         30

在庫数 30

在庫関係の帳票を作る場合このようなデータを見せる場合がある。
このように上から順に足した結果を表示するのは非常に面倒。

SQLServerだけでやろうとした場合、カーソルや作業用の一時テーブルを使うことになると思うが
なんとかできないものかと考えた。
(自分には他の方法は思いつかないだけなので方法を知っている方がいましたらぜひ教えてください。)
そのうち「完全に」出来るようになると思うのでメモ。

参考URL:CLR ユーザー定義集計関数の呼び出し
上記にあるようなユーザー定義集計で文字列を結合する。
Over句が使用可能なはずなので行数を維持しつつ文字列集計する。
更にRow_Number関数で行番号を振る。

日付    移動数 在庫数    CLR集計                           行番号
10/01     100      150        100,80,-130,40,-50,-60       1
10/13     80        230        100,80,-130,40,-50,-60       2
10/25     -130     100        100,80,-130,40,-50,-60       3
11/06     40        140        100,80,-130,40,-50,-60       4
11/10     -50       90          100,80,-130,40,-50,-60       5
11/13     -60       30          100,80,-130,40,-50,-60       6

すると上記のようなデータになる。

ALTER FUNCTION [dbo].[CommaSum]
(
 @String nvarchar(max),
 @CalcCount int
)
RETURNS nvarchar(max)
AS
BEGIN
 declare @instr1 int set @instr1=-1
 declare @instr2 int set @instr1=-1
 declare @Calc nvarchar(50) set @Calc=0
 declare @i int set @i=0
 while @i<@CalcCount begin
  set @instr2=charindex(‘,’,@String,@instr1+1)
  set @Calc=@Calc+cast(SUBSTRING(@String,@instr1+1,(case when @instr2=0 then LEN(@String)+1 else @instr2-1 end)-@instr1) as money)
  –set @Calc=SUBSTRING(@String,@instr1+1,(case when @instr2=0 then LEN(@String)+1 else @instr2-1 end)-@instr1)
  set @instr1=@instr2
  set @i=@i+1
 end
 return @Calc
END

あとは上記のようなSplitのような感じでコンマを区切り、
各要素を足していくユーザー定義関数を作成する。
この時足し算を行う回数を任意に指定出来るようにしておけばRow_Numberを引数にあてて

「100,80,-130,40,-50,-60」に対して

1行目はコンマ1つ目まで足す  →100
2行目はコンマ2つ目まで足す  →100+80
3行目はコンマ3つ目まで足す  →100+80+(-130)
4行目はコンマ4つ目まで足す  →100+80+(-130)+40

となり任意の結果が得られる。

しかしユーザー定義集計のSqlUserDefinedAggregateAttribute クラスでは
重要な「IsInvariantToOrder」プロパティが未実装であり、
これに依るものかなぜかOver句でorder byを指定できずでエラーになるため
ユーザー定義集計時の並び順がさっぱりわからない上に変わらない。(partition byは可能)

もう少し待たないとこの方法は使えない。そもそもパフォーマンスがいいのかも不明。
一時テーブルにインサート&カーソルよりは早いことを期待したい。