Peter Larsson Blog

Patron Saint of Lost Yaks

SQLCLR aggregate function

Phew! Now it's finally done.
I haven't coded outside SQL Server since 2007 and that was with VB version 6.0.

Well, I felt the need to start code again (at least for SQLCLR), since there are many tasks that will be easier to do with a SQLCLR routine. To start easy, I coded a "least square regression" routine and you can download it from this location

Regression.dll http://regression.developerworkshop.net/dws.Regression.dll
Install.sql http://regression.developerworkshop.net/Install.sql
Sample.sql http://regression.developerworkshop.net/Sample.sql (good linear regression)
Sample2.sql http://regression.developerworkshop.net/Sample2.sql (better linear regression)
Sample3.sql http://regression.developerworkshop.net/Sample3.sql (polynomial regression)


Update:
* I have created a homepage for this SQLCLR function http://regression.developerworkshop.net

For the Sample2 above, the result for c: drive on Server1 look like this

<dws bestfit="linear" r2="0.99" type="least square regression">
 <linear A="15417024.67" b="-388.60" r2="0.99" y="A+b*x" x="(y-A)/b" />
 <exponential A="1.9E+102" b="-0.00"r2="0.98" y="A*e^(b*x)" x="ln(y/A)/b" />
 <logarithmic A="162488444.70" b="-15345855.35" r2="0.99" y="A+b*ln(x)" x="e^((y-A)/b)" />
 <power A="INF" b="-224.35" r2="0.98" y="A*x^b" x="e^(ln(y/A)/b)" />
</dws>


For Sample3 above, the result can look like

<dws a="-4.00"b="6.64"c="1.64"r2="0.98"y="a + b*x + c*x^2 + d*x^3" />



With these constants, you can easy calcuate what freespace there will be on a certain date, or on which date the freespace is x megabytes. 

It has an ugly hack, because it seems VS2008 doesn't accept multiple parameters for Accumulate routine. I have to wait until VS2010 has fixed the strong name bug before doing this the right way.
I also have begun to code routines for Polynomial Regression too, along with Circle, Ellipse and parabolic regression. My idea is that this library of statistical and mathematical functions will continue to extend.

Nevertheless, the ugly hack works, and I would love some feedback.

There are plenty of uses for this routine. For example, if you store disk space amount in a history table, apply the BestFit formula on top of the data, and you can predict when space is out, or at least when space will be at a certain level in the future.

If you can find more uses, please let me know. And thanks to Mladen Prajdic and Florian Reischl for the last pieces of the xml return puzzle.

Cheers!

Legacy Comments


noeldr
2009-08-26
re: SQLCLR aggregate function
where is the C# code ?

Peso
2009-08-26
re: SQLCLR aggregate function
You want the source code?

noeldr
2009-08-26
re: SQLCLR aggregate function
why Not?
Unless you have ulterior profitable motives :-)

Peso
2009-08-26
re: SQLCLR aggregate function
I don't have C# code, I have VB.Net code.

Also, yes the DLL will be available for order later on but for now it's free, as long as the ugly hack is there.

Simon Sabin
2009-08-31
re: SQLCLR aggregate function
Don't use VS to deploy your aseembly and you will be fine. Do it by hand.

Or use Niels Berglunds SQLCLR project