Advanced tutorial on analytic functions in Oracle/SQLServer

Question

Can anyone recommend a good tutorial (or book perhaps) that covers advanced topics in the use of Analytic Functions?

I'm looking for something that covers both Oracle and SQLServer - or two separate guides if a single one doesn't exist. Something that offers good examples of using nontrivial functions (LAG, LEAD, GROUPING, PERCENTILE) would be helpful.

Please don't point me at the basic Oracle Technet or MSDN reference material - I've seen that. I'm looking for something that goes beyond just syntax or trivial examples.

Answer 1

For Oracle, there's a good chapter in Tom Kyte's Expert Oracle (Signature Edition). The earlier edition of Expert Oracle lacks this chapter. This goes into a lot more detail than most online articles I've seen.

Preview on Google Books by searching on "Tom Kyte Analytic Functions"

Much of this should be appropriate to SQL Server, although specific syntax may vary (as usual).

Apress also have a book called 'Oracle SQL Recipes' - I don't own this, but again the preview on Google Books ('analytic function recipes') looks like it may be very useful - although it comes from an example rather than concept based approach.

Answer 2

I like the tutorial/introduction for the PostgreSQL implementation. It does not only cover the mere syntax but also introduces the concepts behind them. It explains quite nicely what a a window is and what a frame inside a window is.

The PostgreSQL syntax is pretty much compatible with the Oracle syntax, so that should help you there. Not sure how much of that is implemented in SQL Server.

Anyway here is the link: http://www.pgcon.org/2009/schedule/events/128.en.html

Direct Link to the PDF: http://www.pgcon.org/2009/schedule/attachments/98_Windowing%20Functions.pdf