Ever missed a Prod(columnName) function that works like a sum but it multiplies the column values?
If you have then you probably know that there's a workaround using a bit of high school math knowledge about base 10 logarithms.
It goes like this:
SELECT exp(sum(log(c1))) as MultiplicationResult FROM test
However this little helper doesn't yield correct results for a large enough set, because it goes from integer to decimal.
So there's a possibilty of an error.
This is also a perfect example of a user defined aggregate that can be implemented in CLR. And becuase there's no int to deciaml transition
it yields correct results.
This is a simmple C# mulitplication aggregate:
Project Name = SqlProduct
Class name = Prod.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 300, IsNullIfEmpty=true)]
public struct Prod : IBinarySerialize
{
private decimal product;
public void Init()
{
product = 1;
}
public void Accumulate(SqlDecimal Value)
{
product = product * (decimal)Value;
}
public void Merge(Prod Group)
{
Accumulate(Group.Terminate());
}
public SqlDecimal Terminate()
{
return product;
}
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r)
{
product = r.ReadDecimal();
}
public void Write(System.IO.BinaryWriter w)
{
w.Write(product);
}
#endregion
}
And this is SQL code to test the new CLR multiplication and the T-SQL Exp-Sum-Log multiplication:
CREATE ASSEMBLY SqlProduct
FROM 'D:\Test\SqlProduct.dll'
WITH PERMISSION_SET = SAFE
CREATE AGGREGATE Prod(@input DECIMAL(38,10))
RETURNS DECIMAL(38,10)
EXTERNAL NAME SqlProduct.Prod;
GO
CREATE TABLE test (c1 int)
GO
SET NOCOUNT ON
DECLARE @i INT
SELECT @i = 0
WHILE @i < 20
BEGIN
INSERT INTO test
SELECT RAND()*10+1
SELECT @i = @i + 1
END
SET NOCOUNT OFF
SELECT dbo.Prod(c1) AS MultiplicationResult
FROM test
SELECT exp(sum(log(c1))) AS MultiplicationResult
FROM test
DROP TABLE test
T-SQL version is about 3 times faster but the CLR one is of course 100% accurate.