Re: analytics reference
Date: Tue, 12 Aug 2014 11:49:43 -0700
Message-ID: <CAA2Dszwf8VgTsanL1FtOVyHO9H4i38psPnBJ9kbeJ26S2DF0VA_at_mail.gmail.com>
<shameless plug>
I wrote two chapters covering analytic functions and Model clause in the Pro Oracle SQL book too :)
</shameless plug>
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance,
RAC and EBS
Blog: http://orainternals.wordpress.com/
Oracle ACE Director and OakTable member <http://www.oaktable.com/>
Co-author of the books: Expert Oracle Practices <http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL, <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices>
<http://tinyurl.com/book-expert-plsql-practices>
On Tue, Aug 12, 2014 at 11:37 AM, Kim Berg Hansen <kibeha_at_gmail.com> wrote:
> I just can't live without analytic functions - use them all the time ;-)
> I've used the documentation a lot and then I find that simply the more I
> use them, the more uses I find for them...
>
> I can't resist a shameless plug for my own presentation on analytic
> functions. You can find them on Slideshare:
> http://www.slideshare.net/KimBergHansen/presentations
> Three of those presentations are variations on examples of use of analytic
> functions from my work.
>
> - The one titled "Real cases of indispensability of Oracle SQL
> analytic functions" has both some syntax explanation as well as examples.
> The presentation has link to scripts of the examples.
> - The one titled "Really using Oracle analytic SQL functions" skips
> syntax explanations but explains the examples in more detail.
> - The one titled. "Oracle database - Analytic functions - Advanced
> cases" is the newest that includes example of using analytic functions for
> Time Series Analysis.
>
> Some examples can also be found on my blog under label "analytics":
> http://dspsd.blogspot.dk/search/label/analytics
>
> --
>
> As for MODEL clause, I look to the datawarehouse documentation. It is not
> very good examples IMHO, but the syntax is reasonably OK explained. I have
> not had much use for it. In production I've got just a single piece of code
> with MODEL clause - a forecast of inventory with repeated replenishment.
>
> But for fun I've tried making Conways Game of Life with MODEL clause:
> http://dspsd.blogspot.dk/2012/07/conways-game-of-life-in-model-clause.html
> Not much use, but fun ;-)
>
> --
>
> Good luck with the analytic functions. Just start using them - soon you'll
> find use cases pop up all the time and you start thinking "how could I ever
> live without them" ;-)
>
>
>
> Regards
>
>
> Kim Berg Hansen
>
> http://dspsd.blogspot.com
> _at_kibeha
>
>
>
>
>
>
> On Tue, Aug 12, 2014 at 5:46 PM, Chris Taylor <
> christopherdtaylor1994_at_gmail.com> wrote:
>
>> I had to use a combination of resources together. I the Oracle Data
>> Warehousing documentation (believe it or not) helped me a ton - reading the
>> documentation (in order of each function) and then looking at examples from
>> different blogs to see the query in action (and various forms it could
>> take).
>>
>> Then I took some of the most useful functions and started writing them
>> against database tables where I wanted some details - so I could get used
>> to using them:
>>
>> Links:
>>
>> http://docs.oracle.com/cd/E11882_01/server.112/e25554/analysis.htm#DWHSG021
>>
>> Helpful usage examples:
>>
>> http://www.oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions.php
>> http://www.orafaq.com/node/55
>>
>> http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23sql-1906475.html
>>
>> Book that was really helpful (but was (IMHO) a rehash of the Oracle
>> documentation on the matter in better form):
>> http://www.rampant-books.com/book_0802_advanced_sql_programming.htm by
>> Laurent Schneider
>>
>> Also Laurent Schneider has several posts scattered around the web with
>> some good examples.
>>
>> Best of luck!
>>
>> Chris
>>
>> P.S. When you finally write a MODEL statement and understand it and can
>> manipulate it, it's AWESOME. (I've only done it a handful of times and
>> would have to refresh my own use of it because I never see it in use by
>> anyone besides me)
>>
>>
>>
>> On Tue, Aug 12, 2014 at 10:09 AM, Adric Norris <landstander668_at_gmail.com>
>> wrote:
>>
>>> Can anyone recommend a good reference (preferably with lots of examples)
>>> on the use of Oracle analytic functions? It tends to be something of a
>>> struggle whenever I use the beasties, and I'd like to get my head wrapped
>>> around them a bit better.
>>>
>>> Thanx!
>>>
>>> --
>>> "In the beginning the Universe was created. This made a lot of people
>>> very angry and has been widely regarded as a bad move." -Douglas Adams
>>>
>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 12 2014 - 20:49:43 CEST