Sunday, November 15, 2009

Comparative Window Functions...

I've been known as a huge fan of Analytic functions (as evidenced by the Rock and Roll linkability!)

And - they could be getting better in the near future. Read this document for a proposal to allow analytics to access the current row value to be compared against any other row value in a defined window.

I've already supplied them with my feedback (which started with "this is an awesome idea") - and you can too - by posting it here. They'll be checking back to see what you say.

Also, this is being proposed as well:

Another window function extension, not contained in the attached proposal, is the notion of VALUE based windows. Currently, we have ROW based (or physical) and RANGE based (logical) windows. RANGE window has limitation in that there can only be one sort key in window ORDER BY. On the other hand, ROW based window is agnostic to column value and can be non-deterministic.

The new VALUE based window allows one to include all rows with "n" values before or after the current row's value. For example, VALUE 2 PRECEDING and 3 FOLLOWING would include all rows with 2 values that are prior to current row's value and all rows with 3 values that come after the current row's value in sort order.

ticker txndate volume
orcl 1 10
orcl 2 10 <--------------------------- start of window for (orcl,6,12)
orcl 2 11
orcl 2 11
orcl 3 11
orcl 6 12 <=== assume this is current row
orcl 7 12
orcl 11 11
orcl 11 12
orcl 11 12
orcl 13 11 <------------------------- end of window for (orcl,6,12)

Similar RANGE window would have rows [orcl,6,12] through [orcl,7,12]. Similar
ROW window would include rows [orcl,3,1] through [orcl,11,11].

The VALUE based window would find usefulness when there are gaps in the dataset. For example, a query like "find the intra-day maximum for a stock in the past three trading days". Today, to do this one has to aggregate on trading date and then compute the moving max (in the past 3 days).

VALUE based window can have multiple keys in ORDER BY.

Thanks in advance for any feedback or ideas you might have on this.