木野狐 (Neil Chen)

• 我的参与
• 最新评论
• 我的标签

#1楼 2006-11-16 10:36 chuanzai[未注册用户]

这样效率如何？   回复  引用    

#2楼[楼主] 2006-11-16 10:39 木野狐      

@chuanzai
性能还是不错的。不过我没有做压力测试。   回复  引用  查看    

#3楼 2009-04-17 10:55 夜之悲哀      

老兄，如果要构造两个参数的聚合函数怎么办呢？
微软的方法好像行不通
http://technet.microsoft.com/zh-cn/library/ms131056.aspx" target="_new">http://technet.microsoft.com/zh-cn/library/ms131056.aspx
我试过了，不行
QQ：75211498   回复  引用  查看    

#4楼[楼主] 2009-04-17 17:49 木野狐(Neil Chen)      

@夜之悲哀

这里有答案的：
http://blogs.msdn.com/sqlclr/archive/2006/06/22/643551.aspx

需要创建一个 UserDefinedType 封装参数，然后再来一个 UserDefinedFunction 构造和传递这个包含多个参数的对象，然后就可以调用。

我试验成功的代码如下：

use test
go

create table test4
(
id int identity(1,1) primary key,
val int,
weight int
)
go

insert into test4(val, weight)
select 1, 2 union all
select 1, 3 union all
select 3, 2 union all
select 4, 3
go


UserDefinedType:
=======================
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000)]
public struct TestData : INullable, IBinarySerialize
{
public override string ToString()
{
throw new Exception("The method or operation is not implemented.");
}

public bool IsNull
{
get
{
return false;
}
}

public static TestData Null
{
get
{
throw new Exception("The method or operation is not implemented.");
}
}

public static TestData Parse(SqlString s)
{
throw new Exception("The method or operation is not implemented.");
}

public int Value;
public int Weight;


#region IBinarySerialize Members

void IBinarySerialize.Read(System.IO.BinaryReader r)
{
Value = r.ReadInt32();
Weight = r.ReadInt32();
}


void IBinarySerialize.Write(System.IO.BinaryWriter w)
{
w.Write(Value);
w.Write(Weight);
}
#endregion
}


UserDefinedFunction:
=============================
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static TestData MakeTestData(int value, int weight)
{
TestData o = new TestData();
o.Value = value;
o.Weight = weight;
return o;
}
};



SqlUserDefinedAggregate:
========================
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(TestData t)
{
sum += (long)t.Value * (long)t.Weight;
count += (int)t.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;
}
}
}


部署后，调用示例代码：
select dbo.WeightedAvg(dbo.MakeTestData(val, weight))
from test4

输出结果为 2.

验证：
select (1*2 + 1*3 + 3*2 + 4*3) / (2+3+2+3)

输出也是 2.

  回复  引用  查看