次の方法で共有

CLR ユーザー定義集計関数を呼び出す

  • [アーティクル]

適用対象:SQL Server

Transact-SQL SELECT ステートメントでは、システム集計関数に適用されるすべての規則に従って、共通言語ランタイム (CLR) ユーザー定義集計を呼び出すことができます。

さらに、次の規則も適用されます。

  • 現在のユーザーには、ユーザー定義集計に対する EXECUTE アクセス許可が必要です。

  • ユーザー定義集計は、<schema_name>の形式で 2 部構成の名前を使用して呼び出す必要があります。<udagg_name>.

  • ユーザー定義集計の引数型は、CREATE AGGREGATE ステートメントで定義されているように、集計の input_type と一致するか、暗黙的に変換できる必要があります。

  • ユーザー定義集計の戻り値の型は、CREATE AGGREGATE ステートメントの return_type と一致する必要があります。

例

ある。 文字列値を連結するユーザー定義集計

次のコードは、テーブル内の列から取得された文字列値のセットを連結するユーザー定義集計関数の例です。

  • C# を する
  • Visual Basic .NET の 
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined, //use clr serialization to serialize the intermediate result
    IsInvariantToNulls = true, //optimizer property
    IsInvariantToDuplicates = false, //optimizer property
    IsInvariantToOrder = false, //optimizer property
    MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
    /// <summary>
    /// The variable that holds the intermediate result of the concatenation
    /// </summary>
    public StringBuilder intermediateResult;

    /// <summary>
    /// Initialize the internal data structures
    /// </summary>
    public void Init()
    {
        this.intermediateResult = new StringBuilder();
    }

    /// <summary>
    /// Accumulate the next value, not if the value is null
    /// </summary>
    /// <param name="value"></param>
    public void Accumulate(SqlString value)
    {
        if (value.IsNull)
        {
            return;
        }

        this.intermediateResult.Append(value.Value).Append(',');
    }

    /// <summary>
    /// Merge the partially computed aggregate with this aggregate.
    /// </summary>
    /// <param name="other"></param>
    public void Merge(Concatenate other)
    {
        this.intermediateResult.Append(other.intermediateResult);
    }

    /// <summary>
    /// Called at the end of aggregation, to return the results of the aggregation.
    /// </summary>
    /// <returns></returns>
    public SqlString Terminate()
    {
        string output = string.Empty;
        //delete the trailing comma, if any
        if (this.intermediateResult != null
            && this.intermediateResult.Length > 0)
        {
            output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
        }

        return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
        intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.intermediateResult.ToString());
    }
}

コードを MyAgg.dllにコンパイルしたら、次のように SQL Server に集計を登録できます。

CREATE ASSEMBLY MyAgg
    FROM 'C:\MyAgg.dll';
GO

CREATE AGGREGATE MyAgg(@input NVARCHAR (200))
    RETURNS NVARCHAR (MAX)
    EXTERNAL NAME MyAgg.Concatenate;

Note

/clr:pure コンパイラ オプションを使用してコンパイルされた Visual C++ データベース オブジェクト (スカラー値関数など) は、SQL Server での実行ではサポートされていません。

ほとんどの集計と同様に、ロジックの大部分は Accumulate メソッドにあります。 ここでは、Accumulate メソッドにパラメーターとして渡される文字列は、Init メソッドで初期化された StringBuilder オブジェクトに追加されます。 Accumulate メソッドがまだ呼び出されていないと仮定すると、渡された文字列を追加する前に、コンマも StringBuilder に追加されます。 計算タスクの終了時に、Terminate メソッドが呼び出され、StringBuilder が文字列として返されます。

たとえば、次のスキーマを持つテーブルについて考えてみます。

CREATE TABLE BookAuthors
(
    BookID INT NOT NULL,
    AuthorName NVARCHAR (200) NOT NULL
);

このテーブルに次の行を挿入します。

INSERT BookAuthors
VALUES
    (1, 'Johnson'),
    (2, 'Taylor'),
    (3, 'Steven'),
    (2, 'Mayler'),
    (3, 'Roberts'),
    (3, 'Michaels');

次のクエリを実行すると、以下のような結果になります。

SELECT BookID, dbo.MyAgg(AuthorName)
FROM BookAuthors
GROUP BY BookID;
BookID AuthorName
1 Johnson
2 Taylor, Mayler
3 Roberts, Michaels, Steven

B. 2 つのパラメーターを持つユーザー定義集計

次の例は、Accumulate メソッドに 2 つのパラメーターを持つ集計を示しています。

  • C# を する
  • Visual Basic .NET の 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(
    Format.Native,
    IsInvariantToDuplicates = false,
    IsInvariantToNulls = true,
    IsInvariantToOrder = true,
    IsNullIfEmpty = true,
    Name = "WeightedAvg")]
public struct WeightedAvg
{
    /// <summary>
    /// The variable that holds the intermediate sum of all values multiplied by their weight
    /// </summary>
    private long sum;

    /// <summary>
    /// The variable that holds the intermediate sum of all weights
    /// </summary>
    private int count;

    /// <summary>
    /// Initialize the internal data structures
    /// </summary>
    public void Init()
    {
        sum = 0;
        count = 0;
    }

    /// <summary>
    /// Accumulate the next value, not if the value is null
    /// </summary>
    /// <param name="Value">Next value to be aggregated</param>
    /// <param name="Weight">The weight of the value passed to Value parameter</param>
    public void Accumulate(SqlInt32 Value, SqlInt32 Weight)
    {
        if (!Value.IsNull && !Weight.IsNull)
        {
            sum += (long)Value * (long)Weight;
            count += (int)Weight;
        }
    }

    /// <summary>
    /// Merge the partially computed aggregate with this aggregate
    /// </summary>
    /// <param name="Group">The other partial results to be merged</param>
    public void Merge(WeightedAvg Group)
    {
        sum += Group.sum;
        count += Group.count;
    }

    /// <summary>
    /// Called at the end of aggregation, to return the results of the aggregation.
    /// </summary>
    /// <returns>The weighted average of all inputed values</returns>
    public SqlInt32 Terminate()
    {
        if (count > 0)
        {
            int value = (int)(sum / count);
            return new SqlInt32(value);
        }
        else
        {
            return SqlInt32.Null;
        }
    }
}

C# または Visual Basic .NET ソース コードをコンパイルした後、次の Transact-SQL を実行します。 このスクリプトでは、C ドライブのルート ディレクトリにある WghtAvg.dll という名前の DLL を想定しています。 また、データベース名は test です。

USE test;
GO

-- EXECUTE sp_configure 'clr enabled', 1;
-- RECONFIGURE WITH OVERRIDE;
-- GO
IF EXISTS (SELECT name
           FROM systypes
           WHERE name = 'MyTableType')
    DROP TYPE MyTableType;
GO

IF EXISTS (SELECT name
           FROM sysobjects
           WHERE name = 'WeightedAvg')
    DROP AGGREGATE WeightedAvg;
GO

IF EXISTS (SELECT name
           FROM sys.assemblies
           WHERE name = 'MyClrCode')
    DROP ASSEMBLY MyClrCode;
GO

CREATE ASSEMBLY MyClrCode
    FROM 'C:\WghtAvg.dll';
GO

CREATE AGGREGATE WeightedAvg(@value INT, @weight INT)
    RETURNS INT
    EXTERNAL NAME MyClrCode.WeightedAvg;
GO

CREATE TYPE MyTableType AS TABLE (
    ItemValue INT,
    ItemWeight INT);
GO

DECLARE @myTable AS MyTableType;

INSERT INTO @myTable
VALUES (1, 4),
(6, 1);

SELECT dbo.WeightedAvg(ItemValue, ItemWeight)
FROM @myTable;
GO

関連コンテンツ

  • CLR ユーザー定義集計 を する