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:


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

Reply via email to