具有多个参数的 SQLCLR 自定义聚合 [英] SQLCLR custom aggregate with multiple parameters
问题描述
我无法理解 CLR 用户定义聚合的工作原理.
I have trouble understanding of how CLR User-Defined Aggregates work.
我必须创建一些具有多个参数的自定义 CLR 聚合.重点是根据第二个参数获取第一个参数的值.
I have to create some custom CLR aggregates with multiple parameters. The point is to get the value of the first parameter depending on the second.
例如,我的表中有以下值,我需要每个 Type
的最老员工 Name
:
For example, I have the following values in my table, and I need the oldest employee Name
for each Type
:
Type | Name | Age
--------------------------------
Manager | emp 1 | 35
Manager | emp 2 | 42
Developer | emp 3 | 36
Developer | emp 4 | 45
Developer | emp 5 | 22
所以我想写一个这样的查询来使用我的程序集获取结果:
So I would like to write a query like this to get the result by using my assembly:
Select Type, dbo.fOldestEmployee(Name, Age) AS [Name]
From xxx
Group By Type
这将响应:
Type | Name
----------------------
Manager | emp 2
Developer | emp 4
看起来使用 CLR 用户定义的聚合是可能的,但我很难找到这种实现的具体示例.
It look like it's possible with a CLR User-Defined Aggregate, but I have difficulty finding a concrete example of this kind of implementation.
目前我有这个.我创建了一个类来收集数据,但我如何对它们进行排序(或做其他事情)?
For the moment I have this. I create a class to collect the datas, but how can I sort (or do other thing) to them?
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Collections;
using System.IO;
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToOrder = false, // order changes the result
IsInvariantToNulls = false, // nulls change the result
IsInvariantToDuplicates = false, // duplicates change the result
MaxByteSize = -1)]
public struct sOlder
{
private List<MyData> _datas;
public void Init()
{
_datas = new List<MyData>();
}
public void Accumulate(SqlString valueField, SqlInt32 ValueInt)
{
if (!valueField.IsNull && !ValueInt.IsNull)
{
_datas.Add(new MyData
{
ValField = valueField.Value,
ValInt = ValueInt.Value
});
}
}
public void Merge (sOlder Group)
{
_datas.AddRange(Group._datas);
}
public SqlString Terminate ()
{
//...
}
public class MyData
{
public String ValField { get; set; }
public Int32 ValInt { get; set; }
}
}
有什么想法吗?
推荐答案
无需存储所有记录的列表 - 您只需要存储迄今为止看到的最旧记录的详细信息.
There's no need to store a list of all the records - you only need to store the details of the oldest record you've seen so far.
这样的事情应该可以工作:
Something like this should work:
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToOrder = true,
IsInvariantToNulls = true,
IsInvariantToDuplicates = true,
MaxByteSize = -1)]
public struct sOlder : IBinarySerialize
{
private struct MyData
{
public string Name { get; set; }
public int? Age { get; set; }
public int CompareTo(MyData other)
{
if (Age == null) return other.Age == null ? 0 : -1;
if (other.Age == null) return 1;
return Age.Value.CompareTo(other.Age.Value);
}
public static bool operator <(MyData left, MyData right)
{
return left.CompareTo(right) == -1;
}
public static bool operator >(MyData left, MyData right)
{
return left.CompareTo(right) == 1;
}
}
private MyData _eldestPerson;
public void Init()
{
_eldestPerson = default(MyData);
}
public void Accumulate(SqlString name, SqlInt32 age)
{
if (!name.IsNull && !age.IsNull)
{
var currentPerson = new MyData
{
Name = name.Value,
Age = age.Value
};
if (currentPerson > _eldestPerson)
{
_eldestPerson = currentPerson;
}
}
}
public void Merge (sOlder other)
{
if (other._eldestPerson > _eldestPerson)
{
_eldestPerson = other._eldestPerson;
}
}
public SqlString Terminate()
{
return _eldestPerson.Name;
}
public void Write(BinaryWriter writer)
{
if (_eldestPerson.Age.HasValue)
{
writer.Write(true);
writer.Write(_eldestPerson.Age.Value);
writer.Write(_eldestPerson.Name);
}
else
{
writer.Write(false);
}
}
public void Read(BinaryReader reader)
{
if (reader.ReadBoolean())
{
_eldestPerson.Age = reader.ReadInt32();
_eldestPerson.Name = reader.ReadString();
}
else
{
_eldestPerson = default(MyData);
}
}
}
这篇关于具有多个参数的 SQLCLR 自定义聚合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!