Re: Select ranges based on sequential breaks

On Tue, 23 Jun 2009 04:41:44 +1000, Mike Toews <mwtoews(at)sfu(dot)ca> wrote:
> Window functions appear to be the best solution for this style of
> problem, and I'm looking forward to their applications. However, I'm
> sticking with 8.3 for at least a year, so I'm not able to explore this
> solution yet. For now, I can only post-process the output in a non-SQL
> environment. I also need to do other fun stuff, like cumulative sums,
> which is also challenging with SQL, but much easier and intuitive with R.

As a largely procedural programmer, the PL/SQL solution is quite appealing
to me, and would be similarly simple to calculate cumulative sums. The
integration of SELECT statements within PL/SQL also seems much tighter
than with other PL languages. Unfortunately, one can't send a cursor or a
set of results directly as a PL argument.

I'm having a skim through Celko's chapter 24, but it doesn't seem to be
close to my needs either.

On Tue, 23 Jun 2009 08:05:14 +1000, Mike Toews <mwtoews(at)sfu(dot)ca> wrote:
...
> # Determine where the rows are different; 1=different rows, 0=same rows
> dat$breaks <- ifelse(dat$bin != c(TRUE,
> as.character(dat$bin[-nrow(dat)])), 1, 0)
>
> # Determine where the continuous parts are:
> dat$part <- factor(cumsum(dat$breaks))

Yes, as far as I can tell, this is almost identical to my WINDOW-based
solution in finding when there is a change, marking it with 0 or 1 and the
using cumulative sum to number the partitions. This could be similarly
done in PL/SQL but it seemed more sensible to just do the whole thing
rather than using GROUP BY after enumeration.

- Joel