Re: Select ranges based on sequential breaks

Scott Marlowe wrote:
> On Mon, Jun 15, 2009 at 12:23 PM, Mike Toews<mwtoews(at)sfu(dot)ca> wrote:
>
>> This is easy to compute using a spreadsheet or in R, but how would I do this
>> with SQL? I'm using 8.3. Advice is appreciated.
>>
>
> FYI (and I'm no expert in this area) R is available as a pl for
> postgres, look for pl/R or plR
>
FYI, here is how I implement ranges on sequential breaks in R. Sorry, I
haven't meddled with plR yet, although I'm experience with both R and
postgres. This is all R code:

# Randomly sampled bins: "red", "blue"
dat <- data.frame(date=seq(as.Date("2009-01-01"), by="days", length.out=20))
dat$bin <- factor(sample(c("red","blue"), 10, replace=TRUE,
prob=c(0.4,0.6)))

# 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))

# Results vary due to random sampling
print(dat)

... and on the SQL side, simple aggregates like min(), max(etc) can be
used with "GROUP BY part" to determine the start/end dates, length of
duration, etc.

-Mike