| 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 |
| Message-ID: | 4A36919F.8030907@sfu.ca |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| 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 |