| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Fwd: Re[2]: Instead of SUM() I require MULTIPLY
Saturday, December 10, 2005, 7:40:51 AM, Jonathan Gennick (jonathan_at_gennick.com) wrote: Friday, December 9, 2005, 10:21:22 PM, Mark Brinsmead (mark.brinsmead_at_shaw.ca) wrote: MB> Jonathan, your soution is pretty clever, and actually more useful than MB> you seem to think.
Actually, I have to credit Anthony Molinaro. I read it in his book. I think the solution as actually been around for some time, but it was he who most recently reminded me of it.
MB> Sadly, there remains one minor shortcoming that is not so easy to fix. MB> The final result is only APPROXIMATE!
Yes, this little pitfall hit me as I was going to sleep last night. So now we have another caveat to add to the list.
Your solution for negative numbers is great. Very elegant. I like it.
Best regards,
Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
Friday, December 9, 2005, 10:21:22 PM, Mark Brinsmead (mark.brinsmead_at_shaw.ca) wrote: MB> Jonathan, your soution is pretty clever, and actually more useful than MB> you seem to think.
MB> The shortcomings you mentioned aren't insurmountable.
MB> What you need to do is:
MB> * SUM the logarithms of absolute values of non-negative numbers and MB> take the exponent. MB> This will be the MAGNITUDE of your result. MB> EXP(SUM(case when x > 0 then ln(x) when x < 0 then ln(-x) else MB> NULL end)) MB> * COUNT the negative values. (Their parity will determine the SIGN MB> of your result.) MB> COUNT(case when x < 0 then 1 else null end) MB> * COUNT the zero values (If the count is non-zero, then your final MB> result is ZERO) MB> COUNT(case when x = 0 then 1 else null end)
MB> From these, computing the final product is easy -- I won't waste bits MB> on the details.
MB> Sadly, there remains one minor shortcoming that is not so easy to fix. MB> The final result is only APPROXIMATE! We are performing some fairly MB> complex floating-point operations to approximate what should (or a least MB> *could*) be an integer computation. For example, when I loaded up a MB> test table with the values 2, 2, 2, 3, -1, -1, -1, 0, -1 I came up with MB> these results: MB> MAGNITUDE: 24.00000000000000000000000000000000000005 MB> NEGATIVES: 3 MB> ZEROES: 1 MB> Not bad really -- there is definitely a rounding error, but it's pretty MB> minor. I suspect this error will increase according to something like MB> the number of distinct values in the table; it could actually become MB> significant over a sufficiently large amount of data. Happily, 39 MB> signficant (decimal) digits go a *long* way; this is not nearly so awfulMB> as doing binary floating-point operations with (something like) a 23-bit MB> mantissa...
MB> Jonathan Gennick wrote:
>>A caveat to my previous response. That trick works only for positive
>>numbers. It'll fail if a zero or negative value is encountered.
>>
>>Best regards,
>>
>>Jonathan Gennick --- Brighten the corner where you are
>>http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
>>
>>
>>
>>Friday, December 9, 2005, 5:13:09 PM, Ethan Post (post.ethan_at_gmail.com) wrote:
>>EP> I just came up with a function I would like, but don't think exists.
>>
>>EP> TABLE TEST (X NUMBER)
>>EP> =====================
>>EP> 2
>>EP> 2
>>EP> 4
>>
>>EP> select sum(x) from test;
>>
>>EP> will return 8...
>>
>>EP> what I need is
>>
>>EP> select multiply(x) from test;
>>
>>EP> will return 16, because 2*2*4 is 16.
>>
>>EP> Anyone ever seen a SQL aggregate function like this? I don't think it exists
>>EP> but I hold out hope.
>>
>>EP> - Ethan
>>
>>--
>>http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>>
>>
>>
MB> --
MB> http://www.freelists.org/webpage/oracle-l
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Dec 10 2005 - 06:57:28 CST
 |
 |