SQL Server 中的自定义聚合
SQL Server 没有您需要的聚合函数？为什么不创建您自己的。
引言
SQL Server 内置的聚合函数数量有限。基本的聚合函数，如 COUNT、MIN、MAX 等虽然已经实现，但总体数量仍然不多。这可能导致某些计算必须在存储过程、函数甚至客户端进行。
然而，SQL Server 支持 CLR 集成，并且能够定义一个在 .NET 项目中实现的自定义聚合函数。这种组合使得创建自定义聚合函数成为可能。
第一个聚合函数：计算乘积
C# 实现
创建聚合函数的过程相当直接。我们需要一个简单的类库（DLL）项目，目标为 .NET Framework 3.5（SQL Server 2008 支持的最大版本），实现必要的结构，然后将程序集和聚合函数注册到 SQL Server 中。
聚合函数是通过定义一个带有 SqlUserDefinedAggregate 属性的 struct 来创建的。使用该属性，我们可以定义例如以下选项：
Format：struct的序列化格式。通常是Native或UserDefined。对于Native格式，框架会处理序列化和反序列化结构所需的所有步骤。IsInvariantToDuplicates(bool)：接收相同值两次或多次是否会影响结果？IsInvariantToNulls(bool)：接收NULL值是否会改变结果？IsInvariantToOrder(bool)：值的顺序是否会影响结果？IsNullIfEmpty(bool)：空集合是否会产生NULL值？Name(string)：聚合函数的名称。
该 struct 本身至少必须包含以下方法：
Init：当要使用结构的一个实例处理一组新值时调用。Accumulate：每个值都会传递给Accumulate方法，该方法负责进行必要的计算等。Merge：当原始值集被分成多个独立组，并在累积了组特定值之后，将组合并到另一个组时使用此方法。Terminate：最后，当所有值都已处理完毕后，Terminate返回结果。
请注意，在使用聚合函数时，结构可能会被重用。因此，务必在 Init 方法中执行所有必要的初始化，并且不要假定 struct 的实例是全新的。
合并最好通过一个小型（简化）图示来解释：
查询处理器可能会将一组值划分为称为组的更小子集。进行聚合时，每个组都有自己的结构实例来处理该子集。每个实例首先被初始化，然后累积该组中的每个值。之后，该组会被合并到另一个组。最后，当所有组都合并完毕后，聚合就会终止，并将结果返回给使用者。因此，聚合函数必须设计成能够支持在累积时操作部分集合。
到目前为止，全是文字，没有代码。让我们来看看结构：
/// <summary>
/// Calculates the product of numerical values
/// </summary>
[System.Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Microsoft.SqlServer.Server.Format.Native,
IsInvariantToDuplicates = false, // receiving the same value again
// changes the result
IsInvariantToNulls = false, // receiving a NULL value changes the result
IsInvariantToOrder = true, // the order of the values doesn't
// affect the result
IsNullIfEmpty = true, // if no values are given the result is null
Name = "Product" // name of the aggregate
)]
public struct Product {
/// <summary>
/// Used to store the product
/// </summary>
public System.Data.SqlTypes.SqlDouble Result { get; private set; }
/// <summary>
/// Used to inform if the accumulation has received values
/// </summary>
public bool HasValue { get; private set; }
/// <summary>
/// Initializes a new Product for a group
/// </summary>
public void Init() {
this.Result = System.Data.SqlTypes.SqlDouble.Null;
this.HasValue = false;
}
/// <summary>
/// Calculates the product of the previous values and the value received
/// </summary>
/// <param name="number">Value to include</param>
public void Accumulate(System.Data.SqlTypes.SqlDouble number) {
if (!this.HasValue) {
// if this is the first value received
this.Result = number;
} else if (this.Result.IsNull) {
//if the calculated value is null, stay that way
} else if (number.IsNull) {
//if the value received is null the result is null
this.Result = System.Data.SqlTypes.SqlDouble.Null;
} else {
//multiply the values
this.Result = System.Data.SqlTypes.SqlDouble.Multiply(this.Result, number);
}
this.HasValue = true;
}
/// <summary>
/// Merges this group to another group instantiated for the calculation
/// </summary>
/// <param name="group"></param>
public void Merge(Product group) {
// Count the product only if the other group has values
if (group.HasValue) {
this.Result = System.Data.SqlTypes.SqlDouble.Multiply
(this.Result, group.Result);
}
}
/// <summary>
/// Ends the calculation and returns the result
/// </summary>
/// <returns></returns>
public System.Data.SqlTypes.SqlDouble Terminate() {
return this.Result;
}
}
此聚合函数仅使用 **可直接复制** 的数据类型，这意味着 SQL Server 和 .NET Framework 对 struct 中定义的字段具有共同的表示形式。因此，该聚合函数被定义为 Native 格式，无需采取额外的步骤进行序列化。
四个强制方法都已实现，由于这是一个非常简单的计算，我相信代码不需要详细解释。只有一个注意事项：由于值集中可能存在 NULL 值，NULL 被视为特殊情况处理，因此 NULL 乘以任何值都始终得到 NULL。
注册到 SQL Server
项目成功生成后，下一步是将程序集注册到 SQL Server。在将程序集添加到数据库之前，我选择创建一个新的架构。这不是必需的，但我认为将自定义聚合函数放在一个独立的地方会更好。
-- Create a new schema for the aggregates
CREATE SCHEMA Aggregates;
创建架构后，让我们上传程序集：
-- Add the assembly into SQL Server
--
-- NOTE: Change the disk and the path!
--
CREATE ASSEMBLY CustomAggregates
AUTHORIZATION dbo
FROM '?:\???\CustomAggregates.dll'
WITH PERMISSION_SET SAFE;
现在程序集已存储在 SQL Server 中，我们可以将新创建的聚合函数注册到数据库：
-- Add the aggregate into SQL Server
CREATE AGGREGATE Aggregates.Product (@number float) RETURNS float
EXTERNAL NAME CustomAggregates.Product;
聚合函数是在 Aggregates 架构中创建的。数据类型 float 用于此目的，因为它是 SQL Server 中 SQLDouble 数据类型的等效数据类型。
现在我们准备测试该聚合函数。首先，一个简单的测试运行：
-- Test-run 1
SELECT Aggregates.Product(a.Val) AS Result
FROM (SELECT 2.1 AS Val UNION ALL
SELECT 5.3 AS Val) a
结果是：
Result
------
11,13
现在进行第二次测试运行，如果集合中包含 NULL 怎么办？
-- Test-run 2, NULL in the set
SELECT Aggregates.Product(a.Val) AS Result
FROM (SELECT 2.1 AS Val UNION ALL
SELECT NULL AS Val UNION ALL
SELECT 5.3 AS Val) a
结果是 NULL，正如前面所讨论的。
Result
------
NULL
最后一次测试运行是使用空集合：
-- Test-run 3, empty set
SELECT Aggregates.Product(NULL) AS Result
这也导致了 NULL，正如预期的那样。
稍作增强：几何平均值
下一个聚合函数与乘积非常相似，因为它是几何平均值。几何平均值的定义如下：
由于此计算（在此形式下）无法在累积阶段完成，因此我们在累积和合并阶段计算乘积，并在 Terminate 方法中计算最终结果。结构可能如下所示：
/// <summary>
/// Calculates the geometric mean of numerical values
/// </summary>
[System.Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Microsoft.SqlServer.Server.Format.Native,
IsInvariantToDuplicates = false, // receiving the same value again
// changes the result
IsInvariantToNulls = false, // receiving a NULL value changes the result
IsInvariantToOrder = true, // the order of the values doesn't
// affect the result
IsNullIfEmpty = true, // if no values are given the result is null
Name = "GeometricMean" // name of the aggregate
)]
public struct GeometricMean {
/// <summary>
/// Used to store the product
/// </summary>
public System.Data.SqlTypes.SqlDouble Product { get; private set; }
/// <summary>
/// Number of values in the set
/// </summary>
public double ValueCount { get; private set; }
/// <summary>
/// Initializes a new Product for a group
/// </summary>
public void Init() {
this.Product = System.Data.SqlTypes.SqlDouble.Null;
this.ValueCount = 0;
}
/// <summary>
/// Calculates the product of the previous values and the value received
/// </summary>
/// <param name="number">Value to include</param>
public void Accumulate(System.Data.SqlTypes.SqlDouble number) {
if (this.ValueCount == 0) {
// if this is the first value received
this.Product = number;
} else if (this.Product.IsNull) {
//if the calculated value is null, stay that way
} else if (number.IsNull) {
//if the value is null the result is null
this.Product = System.Data.SqlTypes.SqlDouble.Null;
} else {
//multiply the values
this.Product = System.Data.SqlTypes.SqlDouble.Multiply(this.Product, number);
}
this.ValueCount++;
}
/// <summary>
/// Merges this group to another group instantiated for the calculation
/// </summary>
/// <param name="group"></param>
public void Merge(GeometricMean group) {
//Count the product only if the other group has values
if (group.ValueCount > 0) {
this.Product = System.Data.SqlTypes.SqlDouble.Multiply(
this.Product, group.Product);
}
this.ValueCount += group.ValueCount;
}
/// <summary>
/// Ends the calculation for this group and returns the result
/// </summary>
/// <returns></returns>
public System.Data.SqlTypes.SqlDouble Terminate() {
return this.ValueCount > 0 && !this.Product.IsNull
? System.Math.Pow(this.Product.Value, 1 / this.ValueCount)
: System.Data.SqlTypes.SqlDouble.Null;
}
}
让我们注册这个聚合函数：
-- Add the aggregate into SQL Server
CREATE AGGREGATE Aggregates.GeometricMean (@number float) RETURNS float
EXTERNAL NAME CustomAggregates.GeometricMean;
然后进行测试：
-- Test-run 1
SELECT Aggregates.GeometricMean(a.Val) AS Result
FROM (SELECT 34 AS Val UNION ALL
SELECT 27 AS Val UNION ALL
SELECT 45 AS Val UNION ALL
SELECT 55 AS Val UNION ALL
SELECT 22 AS Val UNION ALL
SELECT 34 AS Val) a
结果是：
Result
------
34,5451100372458
使用分区
分区也可以正常用于自定义聚合函数。例如，如果我们按两个不同类别划分上述数据，并希望获得每个类别的几何平均值，查询将如下所示：
-- Test-run 4, using partitioning
SELECT DISTINCT
a.Cat,
Aggregates.GeometricMean(a.Val) OVER (PARTITION BY a.Cat) AS Result
FROM (SELECT 1 AS Cat, 34 AS Val UNION ALL
SELECT 1 AS Cat, 27 AS Val UNION ALL
SELECT 1 AS Cat, 45 AS Val UNION ALL
SELECT 2 AS Cat, 55 AS Val UNION ALL
SELECT 2 AS Cat, 22 AS Val UNION ALL
SELECT 2 AS Cat, 34 AS Val) a
运行此查询后，结果是：
Cat Result
--- ------
1 34,5688605753326
2 34,5213758169679
使用多个参数：连接
在创建聚合函数时，float 肯定不是唯一可以使用的。因此，在最后一个示例中，让我们看看其他一些内容：
- 使用多个参数。
- 使用
UserDefined格式。 - 使用
SQLString和SQLBoolean。
该聚合函数使用给定的分隔符连接 string。第三个参数控制聚合函数的行为。如果 NullYieldsToNull 为 true，那么 NULL 值将导致结果为 NULL。当 NullYieldsToNull 为 false 时，NULL 值将被完全忽略。实现如下：
/// <summary>
/// Concatenates the strings with a given delimiter
/// </summary>
[System.Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Microsoft.SqlServer.Server.Format.UserDefined,
IsInvariantToDuplicates = false, // Receiving the same value again
// changes the result
IsInvariantToNulls = false, // Receiving a NULL value changes the result
IsInvariantToOrder = false, // The order of the values affects the result
IsNullIfEmpty = true, // If no values are given the result is null
MaxByteSize = -1, // Maximum size of the aggregate instance.
// -1 represents a value larger than 8000 bytes,
// up to 2 gigabytes
Name = "Concatenate" // Name of the aggregate
)]
public struct Concatenate : Microsoft.SqlServer.Server.IBinarySerialize {
/// <summary>
/// Used to store the concatenated string
/// </summary>
public System.Text.StringBuilder Result { get; private set; }
/// <summary>
/// Used to store the delimiter
/// </summary>
public System.Data.SqlTypes.SqlString Delimiter { get; private set; }
/// <summary>
/// Used to inform if the string has a value
/// </summary>
public bool HasValue { get; private set; }
/// <summary>
/// Used to inform if the string is NULL
/// </summary>
public bool IsNull { get; private set; }
/// <summary>
/// Is the concatenation resulting a NULL if some of the values contain NULL
/// </summary>
public bool NullYieldsToNull { get; private set; }
/// <summary>
/// Initializes a new Concatenate for a group
/// </summary>
public void Init() {
this.Result = new System.Text.StringBuilder("");
this.HasValue = false;
this.IsNull = false;
}
/// <summary>
/// Inserts a new string into the existing already concatenated string
/// </summary>
/// <param name="stringval">Value to include</param>
/// <param name="delimiter">Delimiter to use</param>
/// <param name="nullYieldsToNull">Is the concatenation resulting a NULL
/// if some of the values contain NULL</param>
public void Accumulate(System.Data.SqlTypes.SqlString stringval,
System.Data.SqlTypes.SqlString delimiter,
System.Data.SqlTypes.SqlBoolean nullYieldsToNull) {
if (!this.HasValue) {
// if this is the first value received
if (nullYieldsToNull && stringval.IsNull) {
this.IsNull = true;
} else if (stringval.IsNull) {
} else {
this.Result.Append(stringval.Value);
}
this.Delimiter = delimiter;
this.NullYieldsToNull = nullYieldsToNull.Value;
} else if (this.IsNull && nullYieldsToNull.Value) {
//if the concatenated value is null, stay that way
} else if (stringval.IsNull && nullYieldsToNull.Value) {
//if the value is null the result is null
this.IsNull = true;
} else {
//concatenate the values (only if the new value is not null)
if (!stringval.IsNull) {
this.Result.AppendFormat("{0}{1}", delimiter.Value, stringval.Value);
}
}
// true if a value has already been set or the string to be added is not null
this.HasValue = this.HasValue ||
!(stringval.IsNull && !nullYieldsToNull.Value);
}
/// <summary>
/// Merges this group to another group instantiated for the concatenation
/// </summary>
/// <param name="group"></param>
public void Merge(Concatenate group) {
// Merge only if the group has a value
if (group.HasValue) {
this.Accumulate(group.Result.ToString(),
this.Delimiter, this.NullYieldsToNull);
}
}
/// <summary>
/// Ends the operation and returns the result
/// </summary>
/// <returns></returns>
public System.Data.SqlTypes.SqlString Terminate() {
return this.IsNull ? System.Data.SqlTypes.SqlString.Null :
this.Result.ToString();
}
#region IBinarySerialize
/// <summary>
/// Writes the values to the stream in order to be stored
/// </summary>
/// <param name="writer">The BinaryWriter stream</param>
public void Write(System.IO.BinaryWriter writer) {
writer.Write(this.Result.ToString());
writer.Write(this.Delimiter.Value);
writer.Write(this.HasValue);
writer.Write(this.NullYieldsToNull);
writer.Write(this.IsNull);
}
/// <summary>
/// Reads the values from the stream
/// </summary>
/// <param name="reader">The BinaryReader stream</param>
public void Read(System.IO.BinaryReader reader) {
this.Result = new System.Text.StringBuilder(reader.ReadString());
this.Delimiter = new System.Data.SqlTypes.SqlString(reader.ReadString());
this.HasValue = reader.ReadBoolean();
this.NullYieldsToNull = reader.ReadBoolean();
this.IsNull = reader.ReadBoolean();
}
#endregion IBinarySerialize
}
这需要一些解释。由于我们在字段中使用的数据类型在 SQL Server 中不存在，因此格式被标记为 UserDefined。在这种情况下，必须实现 IBinarySerialize 接口才能序列化和反序列化结果。另请注意，现在我们必须自己进行序列化，因此设置了 MaxByteSize。
NULL 现在被单独处理（在 IsNull 属性中），这样 StringBuilder 始终可以包含一个实例，但我们仍然可以控制是否应返回 NULL。
让我们看看测试结果。首先是注册：
---------------------------------------------
-- Concatenation
---------------------------------------------
CREATE AGGREGATE Aggregates.Concatenate (@string nvarchar(max),
@delimiter nvarchar(max),
@nullYieldsToNull bit) RETURNS nvarchar(max)
EXTERNAL NAME CustomAggregates.Concatenate;
然后是测试：
-- Test-run 1
SELECT Aggregates.Concatenate(a.Val, ', ', 0) AS Result
FROM (SELECT 'A' AS Val UNION ALL
SELECT 'B' AS Val UNION ALL
SELECT 'C' AS Val) a
结果是：
Result
------
A, B, C
因此，这个简单的案例可以正常工作。那么 NULL 值呢？
-- Test-run 2, NULL in the set, NullYieldsToNull = false
SELECT Aggregates.Concatenate(a.Val, ', ', 0) AS Result
FROM (SELECT 'A' AS Val UNION ALL
SELECT 'B' AS Val UNION ALL
SELECT NULL AS Val UNION ALL
SELECT 'C' AS Val) a
结果是：
Result
------
A, B, C
和
-- Test-run 3, NULL in the set, NullYieldsToNull = true
SELECT Aggregates.Concatenate(a.Val, ', ', 1) AS Result
FROM (SELECT 'A' AS Val UNION ALL
SELECT 'B' AS Val UNION ALL
SELECT NULL AS Val UNION ALL
SELECT 'C' AS Val) a
结果是：
Result
------
NULL
下载的脚本中包含了更多测试运行。
关于性能
我被问过几次关于性能的问题，所以我决定在文章中添加一些讨论。
使用自定义聚合函数永远无法与内置聚合函数的性能相媲美。一个原因是自定义聚合函数使用 MSIL（或 CIL），因为逻辑是使用 .NET 语言实现的。这意味着 JIT 编译器会参与其中，因此存在编译开销。
另一方面，比较内置和自定义聚合函数并不完全公平。您不会创建新的 SUM 聚合函数，因为它已经存在。自定义聚合函数在将新功能添加到数据库（尚不存在的功能）方面非常有用。因此，在比较性能时，应该作为一个整体进行。这意味着比较应至少包括：
- 执行时间。
- 读取的页面数量。
- CPU 使用率（DBMS 服务器和使用者）。
- 网络流量等。
但为了举例说明内置聚合函数和自定义聚合函数之间的区别，让我们同时使用 AVG 和 GeometricMean。首先，我们需要一些数据，所以让我们创建一个表：
CREATE TABLE SomeNumbers (
Value decimal not null
);
然后用 100,000 行随机数据填充表。这需要一些时间，请耐心等待。
SET NOCOUNT ON
TRUNCATE TABLE SomeNumbers;
DECLARE @counter int;
BEGIN
SET @counter = 1;
WHILE @counter <= 100000 BEGIN
INSERT INTO SomeNumbers VALUES (RAND() * CAST(10 AS DECIMAL));
SET @counter = @counter + 1;
END;
END;
如果我们取算术平均值（AVG），读取的页面数量是 234。
SELECT AVG(Value)
FROM SomeNumbers;
统计
Table 'SomeNumbers'. Scan count 1, logical reads 234, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 26 ms.
现在让我们将其与几何平均值进行比较：
SELECT Aggregates.GeometricMean(Value)
FROM SomeNumbers;
这将导致：
Table 'SomeNumbers'. Scan count 1, logical reads 234, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 83 ms.
因此，可以肯定地说，在这种情况下，自定义聚合函数的速度大约是内置聚合函数的 4 倍。但同样，它计算的是不同的东西。
运行此示例时，您可能会遇到算术溢出。这是因为几何平均值计算元素乘积，如果不存在零，此计算会很快溢出。对于大量数据或大数字，计算本身应以不同的方式进行。
常见陷阱
关于可能导致头疼的常见陷阱的几点说明：
- 当您修改源代码并重新编译程序集时，它不会自动在 SQL Server 中刷新，因为数据库有自己的程序集副本。使用
ALTER ASSEMBLY命令可以在不删除聚合函数的情况下“刷新”数据库中的 DLL。 - 如果方法的签名发生更改（例如，参数数据类型更改或添加了参数），在从数据库中删除更改的聚合函数之前，无法使用
ALTER ASSEMBLY。 - SQL Server 和 CLR 之间的匹配数据类型并不总是很容易知道。请使用 Microsoft 提供的数据类型映射文档：System.Data.SqlTypes 命名空间
- SQL Server 中
SQLString的等效类型是nvarchar，而不是varchar。在CREATE AGGREGATE语句中使用varchar会导致错误 6552。 - 此外，调用
SQLString.Concat（*来自数据库的某个 SQLString 实例*，*C# 字符串*）可能会因错误 6552 而失败，因为顺序不同。
这次就到这里。希望您觉得自定义聚合函数很有用。如果您有多余的时间，我很感激您的评论和投票。谢谢。
历史
- 2011 年 3 月 18 日：创建
- 2011 年 3 月 27 日
- 更正：
GeometricMean的合并步骤未包括元素的加法。感谢 Marc Brooks 指出这一点。 - 添加了关于性能的讨论。
- 更正：