February 6, 2012 at 7:13 am
Great discussion on this - very informative. Cheers all!
February 6, 2012 at 11:24 am
Interesting discussion. I bookmarked this one in case I am ever tasked with doing correlation stats in SQL.
February 8, 2012 at 12:20 am
I've been using the EXP(SUM(LOG(...))) formula for 6 years to calculate yield for an interval from daily
yields, and for calculating corellation too (work for an investment company).
No problem with the signs here, as the yields stored this way: -1% = 0.99, +1% = 1.01.
Simple high school math, sometimes I also ask it as a bonus question from candidates on job interviews how they mutiple a lot of numbers in SQL. Some time (rarely) I get the right answer 🙂
Anyway, 2 things I encountered:
- MS-SQL is not very good at math. There were cases when I got false result on otherwise non-problematic (positive double) numbers.
- this method is short and elegant, but surprisingly not much faster than multiplications in a cursor-loop
February 8, 2012 at 12:31 am
Hugo Kornelis (2/6/2012)
SQL Kiwi (2/6/2012)
We use a CLR aggregate for this.That's definitely cleaner, and easier to understand code. But how does the performance of a CLR aggregate compare to performance of a complicated formula using native functions only? Have you ever done any perf testing?
As far as I remember, the CLR aggregate was a bit slower - though I might be wrong about that (it doesn't generally take many T-SQL calls to make CLR faster). In practice, though, we had no choice, since we needed something with a little more flexibility than a simple product sum.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply