Re: analytics reference

From: Kim Berg Hansen <kibeha_at_gmail.com>
Date: Tue, 12 Aug 2014 20:37:48 +0200
Message-ID: <CA+S=qd04u+KweQSb+ro7+xFwtvYtWf3uK7+wuLacctDyw7oJBQ_at_mail.gmail.com>

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-l

Received on Tue Aug 12 2014 - 20:37:48 CEST