Re: comparing rows

On Mon, Dec 10, 2007 at 02:00:24PM -0800, Reece Hart wrote:
>
> On Mon, 2007-12-10 at 13:31 -0800, hjenkins wrote:
>
> > I would like to take a timeseries of data and extract the rows of data
> > flanking the gaps in it. So I need to compare timestamps from two
> > adjacent
> > rows, and determine if the interval is greater than the standard
> > sampling
> > interval.
>
> It often helps for us to have a snippet of a table definition to frame
> replies. I'll assume that you have a "data" table with a timestamp
> column called "ts". I suspect you could use a subquery, like this:
>
> => select D1.ts as ts1,(select ts from data D2 where D2.ts>D1.ts limit
> 1) as ts2 from data D1;

I'd make this a JOIN on some (set of) column(s). Let's call those
columns a, b and c, and let's assume none are NULLable.

SELECT d1.ts AS ts1, d2.ts AS ts2
FROM
data d1
JOIN
data d2
ON (
(d1.a, d2.b, d2.c) = (d2.a, d2.b, d2.c)
AND
d1.ts < d2.ts
)

Cheers,
David.

>
> I'm uncertain about the performance of this subquery in modern PGs. If
> this query works for you, then you can wrap the whole thing in a view or
> another subquery in order to compute ts2-ts1, like this:
>
> => select ts1,ts2,ts2-ts1 as delta from ( <above query> ) X;
>
>
> This will get you only the timestamps of adjacent rows with large
> deltas. The easiest way to get the associated data is to join on the
> original data table where ts1=ts or ts2=ts.
>
>
> -Reece
>
> --
> Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate