叫用 CLR 使用者定義聚合函數
適用於：
SQL Server
在 Transact-SQL SELECT 語句中，您可以叫用 Common Language Runtime （CLR） 使用者定義匯總，受限於套用至系統聚合函數的所有規則。
下列其他規則適用：
目前用戶必須具有使用者定義匯總
EXECUTE許可權。使用者定義匯總必須使用以 <schema_name>格式的兩部分名稱來叫用。<udagg_name>。
使用者定義匯總的自變數類型必須符合或隱含轉換成匯總的 input_type，如
CREATE AGGREGATE語句中所定義。使用者定義匯總的傳回型別必須符合
CREATE AGGREGATE語句中的 return_type。
例子
一個。 使用者定義匯總串連字串值
下列程式代碼是使用者定義的聚合函數範例，其會串連一組從數據表中的數據行擷取的字串值：
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;
注意
SQL Server 中不支援使用 /clr:pure 編譯程式選項編譯的資料庫物件，例如純量值函式等 C++資料庫物件。
和大部分匯總一樣，大部分的邏輯都位於 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 | 作者名稱 |
|---|---|
1 |
Johnson |
2 |
Taylor, Mayler |
3 |
Roberts, Michaels, Steven |
B. 具有兩個參數的使用者定義匯總
下列範例顯示 Accumulate 方法上有兩個參數的匯總。
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。 此腳本假設 DLL 稱為 WghtAvg.dll，且位於 C 磁碟驅動器的根目錄中。 也會假設名為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