# RE: Re[2]: Instead of SUM() I require MULTIPLY

*From*: "Anthony Molinaro" <amolinaro@xxxxxxxx>*To*: <jonathan@xxxxxxxxxxx>, "Oracle-L Freelists" <Oracle-L@xxxxxxxxxxxxx>*Date*: Sun, 11 Dec 2005 09:47:52 -0500

Jonathan, Thanks for the plug :) Additionally, if you're on 10g, you can use MODEL to create a running product which has no problems with negatives because logarithms are not used (since it uses plain multiplication, you still have to decide how to handle nulls and 0, but that's trivial and shouldn't hamper the readability of the solution). For example: create table dropme (id number); insert into dropme values (1); insert into dropme values (1); insert into dropme values (2); insert into dropme values (3); insert into dropme values (4); insert into dropme values (5); select id, tmp running_prod from dropme model dimension by(row_number()over(order by id) rn ) measures(id, 0 tmp) rules (tmp[any] = nvl2(id[cv()-1],tmp[cv()-1]*id[cv()],id[cv()])); SQL> select id, tmp running_prod 2 from dropme 3 model 4 dimension by(row_number()over(order by id) rn ) 5 measures(id, 0 tmp) 6 rules 7 (tmp[any] = nvl2(id[cv()-1],tmp[cv()-1]*id[cv()],id[cv()])); ID RUNNING_PROD ---------- ------------ 1 1 1 1 2 2 3 6 4 24 5 120 6 rows selected. SQL> update dropme set id = id*-1 where id in (1,3,5); 4 rows updated. SQL> select id, tmp running_prod 2 from dropme 3 model 4 dimension by(row_number()over(order by id) rn ) 5 measures(id, 0 tmp) 6 rules 7 (tmp[any] = nvl2(id[cv()-1],tmp[cv()-1]*id[cv()],id[cv()])); ID RUNNING_PROD ---------- ------------ -5 -5 -3 15 -1 -15 -1 15 2 30 4 120 6 rows selected. No one seems to be using it much (yet!), but MODEL is very powerful and allows you to do so much with so little. If you've done any PROLOG, you'd immediately see how cool MODEL can be. Regards, Anthony -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Gennick Sent: Saturday, December 10, 2005 7:57 AM To: Oracle-L Freelists Subject: Fwd: Re[2]: Instead of SUM() I require MULTIPLY Saturday, December 10, 2005, 7:40:51 AM, Jonathan Gennick (jonathan@xxxxxxxxxxx) wrote: Friday, December 9, 2005, 10:21:22 PM, Mark Brinsmead (mark.brinsmead@xxxxxxx) 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@xxxxxxxxxxx Friday, December 9, 2005, 10:21:22 PM, Mark Brinsmead (mark.brinsmead@xxxxxxx) 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 awful MB> 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@xxxxxxxxxxx >> >> >> >>Friday, December 9, 2005, 5:13:09 PM, Ethan Post (post.ethan@xxxxxxxxx) 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-l -- http://www.freelists.org/webpage/oracle-l

**Follow-Ups**:**RE: Re[2]: Instead of SUM() I require MULTIPLY***From:*Lex de Haan

## Other related posts:

- » Re: Re[2]: Instead of SUM() I require MULTIPLY
- » Re: Re[2]: Instead of SUM() I require MULTIPLY
- » RE: Re[2]: Instead of SUM() I require MULTIPLY
- » Re: Re[2]: Instead of SUM() I require MULTIPLY
- » RE: Re[2]: Instead of SUM() I require MULTIPLY
- » RE: Re[2]: Instead of SUM() I require MULTIPLY
- » RE: Re[2]: Instead of SUM() I require MULTIPLY
- » Re: Re[2]: Instead of SUM() I require MULTIPLY