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
>>
>>--
>>//www.freelists.org/webpage/oracle-l
>>
>>
>>
>>
>>
>>
MB> --
MB> //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
