On my last post I gave a briefing about what is an aggregate function and now I'm going to implement my own aggregate function.
How to do it?
1. Open a new SqlServer project.
2. Add a new Aggregate template to your project:
3. Implement four methods:
Init: In this method we initialize the fields.
Accumulate: This method will be executed on each row.
Merge: This method will be executed in a situation that the aggregation process runs in multiple threads and the result need to be combined.
Terminate: Returns the result.
4. Enabling the SQLCLR by executing this script:
EXEC sp_configure 'clr enabled', 1 RECONFIGURE WITH OVERRIDE
{To watch the current SQLCLR status runs this script: EXEC sp_configure 'clr enabled'}
For example I implemented my own aggregate function in C# that runs directly in the database.
My aggregate function calculates the multiplication of a selected field in each row.
For example we have this lottery tickets table:
|
Lottery_Code |
Owner |
Chance_To_Win |
|
1 |
Shani |
0.25 |
|
2 |
Doron |
0.25 |
|
3 |
Roni |
0.25 |
|
4 |
Yuval |
0.75 |
What we have to do if we want to implement sql function that calculates the chance that all the lottery tickets will win?
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native,IsNullIfEmpty=true,
IsInvariantToNulls=true)]
public struct MultiSum
{
private SqlInt32 multiSummary;
public void Init()
{
// Init the summary counter
multiSummary = 1;
}
public void Accumulate(SqlInt32 Value)
{
// Business logic
if (Value.IsNull)
return;
multiSummary *= Value;
}
//Calculate the result from multiple threads
public void Merge(MultiSum Group)
{
Accumulate(Group.multiSummary);
}
public SqlString Terminate()
{
if (multiSummary==SqlInt32.Null)
return SqlString.Null;
return multiSummary.ToString();
}
}
SELECT dbo.MultiSum(ChanceToWin) AS Expr1
FROM lottery_tickets
The result is ~0.017
[The chance that everyone will win in the same time is:
The chance that the first win (0.25) multiple the chances that the second (0.25) and so on… all of this is equal to 0.25 * 0.25 * 0.25 * 0.75 = ~0.017]