Creating a CLR user define aggregate (part 2). Use multiple columns in the aggregation function
I part 1 we created a nice user defined aggregate. Now we are going to make it more sophisticated and let its value depend on two parameters ShipCountry and ShipShipCity. You might try having two parameters in Accumulate function of the aggregate but you will get an error
The Accumulate method in user defined aggregate "Bonus" must have exactly one parameter.
We are definitely looking into adding “multi-column aggregates” feature in the future versions of SQL Server. For now you can use a workaround. The idea is to create a worker UDT that contain all the fields required for the aggregation. So if you want to take Orders.ShipCountry and Orders.ShipCity into account the UDT should have two corresponding fields. You also need to create a user defined function that takes a number of parameters and returns an instance of the worker UDT. And finally you create an aggregate that takes the worker UDT as a parameter in its aggregation function.
Let’s say XYZ wants to consider German sales that has been shipped to Berlin as regular sales. To take ShipCity this into account you first need to create a UDT. I won’t implement several methods to keep the sample short.
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize=8000)]
public struct OrderData : INullable, IBinarySerialize
{
public override string ToString()
{
throw new Exception("The method or operation is not implemented.");
}
public bool IsNull
{
get
{
return false;
}
}
public static OrderData Null
{
get
{
throw new Exception("The method or operation is not implemented.");
}
}
public static OrderData Parse(SqlString s)
{
throw new Exception("The method or operation is not implemented.");
}
public string ShipCountry;
public string ShipCity;
#region IBinarySerialize Members
void IBinarySerialize.Read(System.IO.BinaryReader r)
{
ShipCountry = r.ReadString();
ShipCity = r.ReadString();
}
void IBinarySerialize.Write(System.IO.BinaryWriter w)
{
w.Write(ShipCountry);
w.Write(ShipCity);
}
#endregion
}
Then you need a function that takes two strings and returns a UDT
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static OrderData MakeOrder(string ShipCountry, string ShipCity)
{
OrderData o = new OrderData();
o.ShipCountry = ShipCountry;
o.ShipCity = ShipCity;
return o;
}
};
And finally a new aggregate
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct BonusEx
{
private int m_nRegularSales;
private int m_nGermanSales;
public void Init()
{
m_nRegularSales = 0;
m_nGermanSales = 0;
}
public void Accumulate(OrderData o)
{
if (o.ShipCountry == "Germany" && o.ShipCity != "Berlin")
{
++m_nGermanSales;
}
else
{
++m_nRegularSales;
}
}
public void Merge(BonusEx Group)
{
m_nRegularSales += Group.m_nRegularSales;
m_nGermanSales += Group.m_nGermanSales;
}
public int Terminate()
{
return Math.Min(200, (m_nRegularSales + 3 * m_nGermanSales));
}
}
So now you can get the bonus for each employee with this query
select
Employees.FirstName, Employees.LastName, dbo.BonusEx(dbo.MakeOrder(Orders.ShipCountry, Orders.ShipCity))
from
Employees join Orders on Employees.EmployeeId = Orders.EmployeeId
group by
Employees.EmployeeId, Employees.FirstName, Employees.LastName
Bottom Line
User defined aggregates gives you more flexibility in terms of where you can implement complex business rules. Now you have all the option: client, middle tier and server. You cannot pass several columns into the aggregation function but you can work it around using UDTs.