前回のOracleでユーザ定義集計関数を作成したので、今度はSQL Server 2005で作ってみた。ちなみに、わんくま内のblogを検索してみたらSQL CLRについてのエントリがあまり無い。
人気茄子…やろうと思えばDataTableでガリガリやれないこともない…
…
まあ、これからですよ！これから！ははは
気を取り直して、SQL CLRで作るユーザ定義集計関数。いってみよー
大まかな流れ
Oracleの場合と違い、SQL Serverでユーザ定義集計関数を作成する場合、アセンブリを作成する必要がのですが
- .NETでコーディングしてアセンブリ作成（署名忘れずに※１）
- 1で作成したクラスを紐付けたユーザー定義集計関数を定義
と、あまり変わらないじゃん♪ （え？アセンブリを署名するのが面倒？まあ、やらないでもいい方法があるんで♪）
ユーザー定義集計関数インターフェース
インターフェースというと語弊が若干あるのですが、要する実装する必要があるインターフェース＆定義する必要のあるメソッド及びアトリビュートです。
クラスに対して必要なもの
- Microsoft.SqlServer.Server.SqlUserDefinedAggregate アトリビュート（クラス）の定義
- Microsoft.SqlServer.Server.IBinarySerialize インターフェースの継承
メンバに対して必要なもの
- public void Init()
- public void Accumulate(SqlString value)
- public void Merge(CsvConcatenation other)
- public SqlString Terminate()
- public void Read(BinaryReader r) ※IBinarySerializeの実装
- public void Write(BinaryWriter w) ※IBinarySerializeの実装
うム。上の四つはOracleと一緒だ（笑
ということで、サクサクいきましょう。
CsvConcate集計関数
こういう、関数の実装部分を定義するクラスの名前っていつも悩みますね。動詞にするかしないかってことで。今回はSQL Server 2005に登録する関数と同じ名前にしたかったので、CsvConcateにしましたが、クラス名だけならCsvConcatenationにしちゃってもいいんですよね。
とまあ、クラス名の話はさておき、文字列をCSV形式で集計してくれるユーザー定義集計関数を作りたいと思います。
まずはMicrosoft.SqlServer.Server.SqlUserDefinedAggregate アトリビュートの定義
|
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Microsoft.SqlServer.Server.Format.UserDefined, //シリアライズ形式（ネイティブかユーザ定義）
IsInvariantToNulls = true, // NULLに対して不変かどうか（NULLを集計しない場合はTrue）
IsInvariantToDuplicates = false, // 重複値に対して不変かどうか（重複した値を集計しない場合はTrue）
IsInvariantToOrder = false, // 現在は未使用（未実装）
MaxByteSize = 8000)] // MAXサイズ
public class CsvConcate : Microsoft.SqlServer.Server.IBinarySerialize |
定義の内容はコメントの通り。NULLや重複値については用途に合わせて使い分けて下さい。
次はInit～Treminateまでのメソッドです。
|
/// <summary>
/// 中間結果を保持
/// </summary>
private StringBuilder intermediateResult;
/// <summary>
/// 初期化
/// </summary>
public void Init()
{
intermediateResult = new StringBuilder();
}
/// <summary>
/// 実際の集計処理
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
if (value.IsNull)
{
intermediateResult.Append(string.Empty).Append(',');
}
intermediateResult.Append(value.Value).Append(',');
}
/// <summary>
/// 部分計算された場合のマージ
/// </summary>
/// <param name="other"></param>
public void Merge(CsvConcate other)
{
intermediateResult.Append(other.intermediateResult);
}
/// <summary>
/// 終了処理
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = string.Empty;
// 最後のカンマはおさらば
if (intermediateResult != null && intermediateResult.Length > 0)
output = intermediateResult.ToString(0, intermediateResult.Length - 1);
return new SqlString(output);
} |
そんでもってIBinarySerialize インターフェースの実装(Microsoft.SqlServer.Server.Format.Nativeの場合は必要なし？)
|
#region IBinarySerialize メンバ
void IBinarySerialize.Read(BinaryReader r)
{
if (r == null) throw new ArgumentNullException("r");
intermediateResult = new StringBuilder(r.ReadString());
}
void IBinarySerialize.Write(BinaryWriter w)
{
if (w == null) throw new ArgumentNullException("w");
w.Write(intermediateResult.ToString());
}
#endregion |
アセンブリを署名するのを忘れずに（キーペアは「sn」などで作成しましょう）※１
ユーザー定義集計関数の登録
さて、署名付きのアセンブリが作成できたのなら、あとは登録するだけです。とりあえず、やることは三つ
- アセンブリの登録
- ユーザー定義集計関数の定義（クラスとの紐付け）
登録するアセンブリの配置場所ですが、こいつは適当「UserLib」に切っておいて、そこに作成したアセンブリ「StringUtility.dll」を配置します。
| C:\Program Files\Microsoft SQL Server\90\UserLib\StringUtility.dll |
（別にSQL Serverと同じフォルダでなくても構いません。）
それと、CLR統合機能を予め有効化する必要があるので、以下のSQLを発行(参考文献：夏椰さんのを参照）
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO |
あとはこいつを登録してやるだけです。以下のSQLを発行
|
-- アセンブリのパス
declare @asmblyPath nvarchar(1000)
set @asmblyPath = 'C:\Program Files\Microsoft SQL Server\90\UserLib\StringUtility.dll'
-- アセンブリの登録
CREATE ASSEMBLY StringUtility FROM @asmblyPath
WITH permission_set=Safe;
GO
-- ユーザー定義集計関数の定義
CREATE AGGREGATE Concatenate(@input nvarchar(4000))
RETURNS nvarchar(4000)
EXTERNAL NAME [StringUtility].[Wankuma.Ganf.SqlClr.StringUtility.CsvConcate];
GO |
これで、完成。では実際にNorthWindで使ってみましょう。（AdventureWorksでもいいですが、あれは結構件数が入っているので、バッファがすぐにあふry）
使い方
こちらも、Oracle同様、他の集計関数と同じ使い方です。
|
select country, dbo.csvconcate(employeeid)
from employees group by country |
参考文献
MSDN CLR ユーザー定義集計の要件
http://technet.microsoft.com/ja-jp/library/ms161551.aspx
ユーザー定義集計関とは違いますが、数夏椰さんのとこでいくつかSQL CLRが公開されています。
夏椰の庵 - Secluded Spot of Kaya - BOX
※１ アセンブリの署名を行わなくても、アセンブリの登録は可能です。その場合は、データベースのTRUSTWORTHYをONにしましょう。