On Wed, Oct 12, 2005 at 09:05:26PM -0500, pilot pirx wrote: > There is no problem in computing moving average > or cumulative sum etc > with the existing SQL (or dealing with time windows > in general) - it just the SQL gets nasty - examples
Except that the SQL doesn't "just" get nasty, it can quickly get EXTREMELY nasty, and often has really lousy performance as well. I regularly write (fairly) readable, fast queries using (Oracle's) windowing/OLAP functions which AFAICT, would be either extremely difficult or essentially impossible for me to write in SQL-92, other than, perhaps, by writing some special purpose SQL compiler/translator to do the job. E.g., see this lengthy thread from 2001: http://openacs.org/forums/message-view?message_id=25521 http://openacs.org/forums/message-view?message_id=25559 http://openacs.org/forums/message-view?message_id=25607 That thread dates from before I'd ever used Oracle's windowing functions. Note that first non-OLAP SQL query for doing "time-series" like stuff. It's only moderately complicated, but it is also trying to accomplish something conceptually VERY simple, yet its SQL is NOT simple. Writing more complicated queries in that fashion quickly becomes infeasible. Furthermore, that initial query also took about 3 seconds to run. My second non-OLAP way of doing the same query, arguably somewhat clearer, took 75 seconds. At the end of that same thread, after intervening confusion, Daryl Biberdorf pointed out the OLAP lead() and lag() functions to me. By using lead(), the SQL becomes much easier to write and understand, AND its execution become enormously faster - down to 0.1 seconds or so. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
