# Thinking outside the box

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!

Print | posted on Wednesday, August 26, 2009 12:22 PM | Filed Under [ Algorithms Miscellaneous ]

## #re: SQLCLR aggregate function

where is the C# code ?
8/26/2009 3:36 PM | noeldr

## #re: SQLCLR aggregate function

You want the source code?
8/26/2009 3:50 PM |

## #re: SQLCLR aggregate function

why Not?
Unless you have ulterior profitable motives :-)
8/26/2009 3:56 PM | noeldr

## #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.
8/26/2009 4:46 PM |

## #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
8/31/2009 11:46 PM | Simon Sabin
Comments have been closed on this topic.