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: | Raw Message | Whole Thread | 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 |