Select ranges based on sequential breaks

Поиск
Список
Период
Сортировка
Hi,

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.

Thanks,

-Mike

В списке pgsql-general по дате отправления: