Select ranges based on sequential breaks

From: Mike Toews <mwtoews(at)sfu(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Select ranges based on sequential breaks
Date: 2009-06-15 18:23:27
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general


I'm having difficulty constructing a query that will find breaks where
data change in a time-series. I've done some searching for this too, but
I haven't found anything.

Here is my example situation, consider my source table:
date bin
2009-01-01 red
2009-01-02 red
2009-01-03 blue
2009-01-04 blue
2009-01-05 blue
2009-01-06 red
2009-01-07 blue
2009-01-08 blue
2009-01-09 red
2009-01-10 red

I would like to get the first and last of each consecutive series based
on column "bin". My result for the table would look like:
first last bin
2009-01-01 2009-01-02 red
2009-01-03 2009-01-05 blue
2009-01-06 2009-01-06 red
2009-01-07 2009-01-08 blue
2009-01-09 2009-01-10 red

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.




Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2009-06-15 18:49:39 Re: horizontal sharding
Previous Message AJAY A 2009-06-15 18:12:32 Amazon EC2 | Any recent developments