Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

T-SQL vs CLR: SQL Server 2005 group multiplication

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;

<span class="kwrd">public</span> <span class="kwrd">void</span> Init()
{
    product = 1;
}

<span class="kwrd">public</span> <span class="kwrd">void</span> Accumulate(SqlDecimal Value)
{
    product = product * (<span class="kwrd">decimal</span>)Value;
}

<span class="kwrd">public</span> <span class="kwrd">void</span> Merge(Prod Group)
{
    Accumulate(Group.Terminate());
}

<span class="kwrd">public</span> SqlDecimal Terminate()
{
    <span class="kwrd">return</span> product;
}

<span class="preproc">#region</span> IBinarySerialize Members

<span class="kwrd">public</span> <span class="kwrd">void</span> Read(System.IO.BinaryReader r)
{
    product = r.ReadDecimal();
}

<span class="kwrd">public</span> <span class="kwrd">void</span> Write(System.IO.BinaryWriter w)
{
    w.Write(product);
}

<span class="preproc">#endregion</span>

}

 

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

<span class="kwrd">SELECT</span> @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.

 

 

Legacy Comments


rockmoose
2007-02-12
re: T-SQL vs CLR: SQL Server 2005 group multiplication
Cool!
Is there a resolution difference between SQL float and CLR decimal to accouht for the inaccuracyy?

rockmoose

Mladen
2007-02-13
re: T-SQL vs CLR: SQL Server 2005 group multiplication
this explains the diff:
http://msdn2.microsoft.com/en-us/library/system.data.sqltypes.sqldecimal.aspx

the diff comes from that in sql you log an int to a decimal. sum that to get another decimal and then you do e^x.
neither Log or e^x are are precise definite numbers so here goes your accuracy.

Anatoly Lubarsky
2007-02-14
re: T-SQL vs CLR: SQL Server 2005 group multiplication
All "high school math knowledge" here is that:
log(A * B) = log(A) + log(B)

Mladen
2007-02-14
re: T-SQL vs CLR: SQL Server 2005 group multiplication
LOL!
thanx Anatoly.

i thought that this didn't need an explenation :))

Bob chief of the sheep
2007-05-29
re: T-SQL vs CLR: SQL Server 2005 group multiplication
And using the decimal rather than the e functions reduces the errors.

SELECT power(10.000, (sum(log(c1)))) as MultiplicationResult FROM test

Gunnar
2007-06-15
re: T-SQL vs CLR: SQL Server 2005 group multiplication
Hi!

Thank you for great code example. I added reference to this blog entry in my company's blog too. Great example for everyone who is going to find out what CLR has offer to us.

Mladen
2007-06-15
re: T-SQL vs CLR: SQL Server 2005 group multiplication
great!

Jayan
2008-08-01
re: T-SQL vs CLR: SQL Server 2005 group multiplication
Will this work??????

declare @temp table(i int)
declare @p numeric(18,2)

insert into @temp values(NULL)
--insert into @temp values(-1)
--insert into @temp values(0)
insert into @temp values(1)
insert into @temp values(2)
insert into @temp values(3)
insert into @temp values(4)
insert into @temp values(5)
insert into @temp values(6)
insert into @temp values(7)


-- select @p=coalesce((@p*(1+(i/100.00)),(1+i/100)) from @temp

select @p=coalesce(@p*i,i) from @temp
select @p
select 1*2*3*4*5*6*7

Gene
2009-07-31
re: T-SQL vs CLR: SQL Server 2005 group multiplication
This is fantasic work. Thanks! This actually saved my butt putting together a Rolled Throughput Universe in BusinessObjects and I desperately needed a PROD/MULT function in SQL Server.

I am however having a problem with the return value. I am only getting 1 or 0 back as a result. Does this have something to do wit the fact that the Write finction is using a BinaryWriter? How do I get this function to actually return the decimal value? Any help would be greatly appreciated. Almost there! :)

~ Gene