Re: build a query

From: Andreas Mosmann <mosmann_at_expires-31-08-2011.news-group.org>
Date: Thu, 18 Aug 2011 23:06:56 +0200
Message-ID: <1313701616.33_at_user.newsoffice.de>

joel garry schrieb am 18.08.2011 in
<6afde31e-02a3-49c0-9a67-8748180b29db_at_j9g2000prj.googlegroups.com>:

> On Aug 18, 5:46 am, Andreas Mosmann <mosm..._at_expires-31-08-2011.news-
> group.org> wrote:

>> ddf schrieb am 18.08.2011 in
>> <d6e122f6-d480-4ecc-a6d1-0cff4d33e..._at_bl1g2000vbb.googlegroups.com>:
>> 
>>> SQL> select ref, prev_loc, location, prev_loc_obj_ct, object_count
>>>   2  from
>>>   3  (select ref,
>>>   4          nvl(lag(ref) over (order by ref, location), ref)
>>> prev_ref,
>>>   5          nvl(lag(location) over (order by ref, location), ref)
>>> prev_loc,
>>>   6          location,
>>>   7          object_count,
>>>   8          nvl(lag(object_count) over (order by ref, location),
>>> object_count) prev_loc_obj_ct
>>>   9  from location)
>>>  10  where nvl(prev_loc_obj_ct, object_count) <> object_count
>>>  11  and prev_ref = ref
>>>  12  /
>> 
>> ..
>> 
>>> David Fitzjarrell
>> 
>> I love this kind of sql. It seems to me to be oversized in this case,
>> but in general it is great. It makes many things easier and faster.
>> 
>> Do you know what kind of license you need to use these analytic
>> functions?
>> Is the enterprise license enough or is there more to be licensed?
>> From which Oracle version these functions exist?
>> 
>> Thanks in advance
>> Andreas
>> 
>> --
>> wenn email, dann AndreasMosmann <bei> web <punkt> de

> These kinds of functions are called analytics, and are included in all
> editions as part of the sql language. There is a more advanced
> analytics option called OLAP, extra cost only available in EE.

> Some functions go way back, others have been added over time.
> http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3170642805938
>
> shows an 8i question, 9 years ago - many good examples throughout
> asktom. FAQ says 8.1.6 for lag and lead, etc.
> http://www.orafaq.com/wiki/Oracle_8i#Oracle_8i_.288.1.6.29_-_November_1999

> I don't use analytics enough to keep them straight, I've found these
> links good to remind me when I need them:

> http://www.orafaq.com/node/1874
> http://psoug.org/reference/analytic_functions.html

> Most people say analytics rock, since it often reduces what would be
> many scans to one, I mostly agree, but I think there may be some cases
> where the performance can still be better with procedural processing.
> But as Tom and others often say, if you can do it in the sql engine,
> that's likely to be best. I surely agree with that, much better to
> deal with exceptional cases as needed. I don't know that anyone has
> figured out how to determine the exceptional cases beforehand.

> jg
> --
> _at_home.com is bogus.
> Shades o' Canter and Siegal:
> http://www.signonsandiego.com/news/2011/aug/18/can-you-get-green-card-online/

Thanks for the links
there are some functions I use very often (count/lead/lag) and so I am familiar with them, but sometimes I need more and it is a little difficult to find good information. Seems that there are only few people using it. (Not that few, as spatial ...)

Andreas

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de

Received on Thu Aug 18 2011 - 16:06:56 CDT