RE: Re[2]: Instead of SUM() I require MULTIPLY
- From: "Anthony Molinaro" <amolinaro@xxxxxxxx>
- To: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>, <jonathan@xxxxxxxxxxx>, "Oracle-L Freelists" <Oracle-L@xxxxxxxxxxxxx>
- Date: Sun, 11 Dec 2005 12:09:17 -0500
Lex,
MODEL ugly? What is it they say about beauty, it's in the
eye of the beholder? ;)
Whether or not MODEL is intuitive and/or elegant depends on
your background.
For those who've done logic programming with PROLOG or any
type of ai stuff using lisp/scheme, MODEL would be very
intuitive and it's elegance would only be ruined by the fact
that it is part of SELECT rather than being on its own.
OTOH, if you're more of a dba not a developer/programmer,
then sure, MODEL would seem unnatural (particularly if you've
haven't done any type of logic programming at all).
I think the addition of MODEL was a great idea, just not sure
it should have been part of the SELECT syntax (this is something
Jonthan brought up when I wrote an article on MODEL for oreilly
last summer; perhaps MODEL would have been better received had it
been independent of SELECT).
Probably would be nicer if a result set was "fed" into the MODEL
clause for processing, rather than smushing it altogether by
making MODEL an extension of SELECT.
From my experience with MODEL, it's clear that whatever you
can do with it (even the stuff MODEL was intended to allow you
to do in straight SQL; recursive list processing,
graph processing, best-fit models, etc..),
can be done in pl/sql, so, I don't think
it will ever be the case that someone is using MODEL
to do something because it can't be accomplished in plsql.
If that's the case then when will it be used?
I suspect in the cases where it seems more natural
to that particular programmer and/or they wish
to accomplish the task in a single sql statement w/out
going to plsql at all.
Better or worse? Only time will tell, but in the meantime,
I enjoy new SQL extensions as they typically open new ideas
For problem solving (even if they aren't quite practical).
Best Regards,
Anthony
-----Original Message-----
From: Lex de Haan [mailto:lex.de.haan@xxxxxxxxxxxxxx]
Sent: Sunday, December 11, 2005 11:22 AM
To: Anthony Molinaro; jonathan@xxxxxxxxxxx; 'Oracle-L Freelists'
Subject: RE: Re[2]: Instead of SUM() I require MULTIPLY
yep, I agree -- MODEL is very powerful -- but at the same time,
if you ask for my personal opinion, the Oracle MODEL syntax is
not very intuitive and *far* from elegant.
I would even dare to say that it is really ugly :-)
and doesn't belong in a language like SQL.
but hey, it's there, so why not go ahead and use it.
I personally prefer to extend SQL with user-defined PL/SQL functions
(for the regular scalar functions that are missing) and with additional
aggregate functions using the data cartridge stuff --
which started this thread. Keeps your SQL code clean and simple.
kind regards,
Lex.
---------------------------------------------------------------------
Jonathan Lewis Seminar http://www.naturaljoin.nl/events/seminars.html
---------------------------------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Anthony Molinaro
Sent: Sunday, December 11, 2005 15:48
To: jonathan@xxxxxxxxxxx; Oracle-L Freelists
Subject: RE: Re[2]: Instead of SUM() I require MULTIPLY
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
