愚者の経験

「また今度」はほとんどこない

カテゴリーアーカイブ: CLR

SQL ServerでEval

文字列を計算式として評価する関数です。
SQLServer単独ではストアド+動的SQL実行という回りくどいやり方になります。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction]
    public static SqlMoney Eval(SqlString input)
    {
        DataTable dt = new DataTable();
        return decimal.Parse(dt.Compute(input.Value,””).ToString());
    }
};

decimal.Parseに辿り着くのに時間が…。返り値は通貨型にしてあります。

SQL ServerでSplit

C#の勉強も兼ねてSQLCLR関数も少し作っています。

結構使えそうなのが「Split関数」です。返り値をテーブル値関数にすれば


declare @IDs nvarchar(max)

set @IDs = ‘100,101,102,103’

select * from table1
where ID番号 in (select ArrayValue from dbo.Split(@IDs,’,’))


のようにin句の中にパラメータを利用することが可能になります。
パフォーマンスは悪いかもしれませんが「ユーザーにデータを任意に複数選択させる」といった場合に、今までSQL文の動的実行(「exec」や「sp_executesql」)が必要であったのに比べて
かなり書きやすいのではないかと思います。

参考URL:http://stackoverflow.com/questions/3403447/regex-split-implementation-for-sql-server

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;

public partial class UserDefinedFunctions
{
    private struct IndexedValue
    {
        public int Index;
        public string Value;
        public IndexedValue(int index, string value)
        {
            Index = index;
            Value = value;
        }
    }
    [SqlFunction(FillRowMethodName = “FillRow”)]
    public static IEnumerable Split(SqlChars input, SqlString delimiter)
    {
        string[] separator = { delimiter.Value };
        string[] split = input.ToSqlString().Value.Split(separator,StringSplitOptions.None);
        IndexedValue[] SplitList=new IndexedValue[split.Length];

        for (int i = 0; i < split.Length; i++)
        {
            SplitList[i] = new IndexedValue(i + 1, split[i]);
        }

        return SplitList;
    }
    public static void FillRow(object obj, out SqlInt32 ArrayIndex, out SqlString ArrayValue)
    {
        IndexedValue v = (IndexedValue)obj;
        ArrayIndex = v.Index;
        ArrayValue = v.Value;
    }
};



C#の構造体は「struct」で書きます。
構造体名と同名のコンストラクタ(?)を書いて初期化できる、ということです。

この関数をSQLServerに登録するにはコマンドプロンプトで

C:\Windows\Microsoft.NET\Framework\v3.5\csc.exe /t:library C:\Split.cs


のような感じでcsファイルからdllを作成し、任意のデータベースで以下のSQLを実行します。

create assembly Split

from ‘c:\Split.dll’        –←作成したdllファイルのパス
with permission_set=safe
go

create function Split
(@input nvarchar(max),@delimiter nvarchar(255))
returns
table(ArrayIndex int,ArrayValue nvarchar(4000))
as external NAME Split.UserDefinedFunctions.Split

SQLCLRが無効になっている場合は先に有効にしてください。

sp_configure ‘clr enabled’,1
reconfigure

Splitで切るだけならばかなり簡単だったのですが、範囲指定などの利便性を考慮して
インデックスをつけようと思いましたがこれが結構わからなかったです。
SQLCLRの関数ではスコープ変数が使えない(?)ようでしたので…。

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は可能)

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