Select ranges based on sequential breaks

Поиск
Список
Период
Сортировка
Искать
От
Mike Toews
Тема
Select ranges based on sequential breaks
Дата
Msg-id
4A36919F.8030907@sfu.ca
Список
Дерево обсуждения
Select ranges based on sequential breaks Mike Toews <mwtoews@sfu.ca>
Re: Select ranges based on sequential breaks Scott Marlowe <scott.marlowe@gmail.com>
Re: Select ranges based on sequential breaks Mike Toews <mwtoews@sfu.ca>
Re: Select ranges based on sequential breaks "Joel Nothman" <jnothman@student.usyd.edu.au>
Re: Select ranges based on sequential breaks David Wilson <david.t.wilson@gmail.com>
Re: Select ranges based on sequential breaks Joel Nothman <jnothman@student.usyd.edu.au>
Re: Select ranges based on sequential breaks Joel Nothman <jnothman@student.usyd.edu.au>
Re: Select ranges based on sequential breaks Mike Toews <mwtoews@sfu.ca>
Re: Select ranges based on sequential breaks Scott Marlowe <scott.marlowe@gmail.com>
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 по дате отправления
От: James B. Byrne
Дата:
Сообщение: Re: cygwin and postgresql
От: rodeored
Дата:
Сообщение: interval is ignored
FAQ