棚卸数 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は可能)
もう少し待たないとこの方法は使えない。そもそもパフォーマンスがいいのかも不明。
一時テーブルにインサート&カーソルよりは早いことを期待したい。