Thread: comparing rows

comparing rows

From
"hjenkins"
Date:
Hello, all,

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.

Thanks for any help.

Regards,
H. Jenkins


Re: comparing rows

From
Reece Hart
Date:

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'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

Re: comparing rows

From
David Fetter
Date:
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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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