JL Computer Consultancy
Building a non-trivial example of Analytic Functions |
June 2006 |
This attached document is in RTF format, size 134K
This article came about because someone sent me an interesting question about a problem they had. The problem was sufficiently challenging that I decided to see if I could solve it (it helped that the email included a couple of short scripts to generate some test data). I solved the problem by working through several iterations of using analytic functions to approach the final solution – and realised that the steps taken could be a useful introduction to using analytic functions. So I wrote the attached article around the code.
The article was first published in the UKOUG magazine in June 2006, and is scheduled for the Q3 issue of the IOUG magazine.
There is an associated zip file containing three scripts with version-specific SQL for the demonstration.
Note: After the article was published, the person who sent me the original question sent me a follow-up note which included the comment:: “One possible deterrent for newbies who'd come from the 8i perspective might have been things like Metalink bug 2864055 - Wrong results using DISTINCT in inline view using analytic functions.”
It’s easy enough to get the wrong results anyway when you start using analytics, it’s much worse when the wrong results aren’t your fault; but stick with analytics, they are very powerful. One word of warning, though – use ‘simple’ SQL to crunch your data down to a human-sized report, then use the analytic functions to ‘add value’. Be very cautious about applying the analytic functions directly to large volumes of data – the performance may not be good.
Download ZIP file with full source
