Re: How can I calculate differences between values

With 8.4's analytic capabilities you can do this:

select * from (
select userid, data - lag(data) over (partition by userid order by data)
diff
from foo) q
where diff is not null;

On Tue, Nov 10, 2009 at 5:40 PM, A B <gentosaker(at)gmail(dot)com> wrote:

> Hello there!
>
> I have a table foo( userid integer, data integer); with the
> constraint unique(userid,data)
>
> Now I wish to select one userid and calculate the differences between
> the data -values (when they are sorted) .
> For example
> if the table contains:
>
> 4, 100
> 5, 200
> 5, 210
> 5, 231
>
>
> I want the values
>
> 5,10
> 5,21
>
> what should happen to the 4,100 record you may ask, I will try to
> exclude that case by requireing each userid to have at least two data
> values.
>
> The question is now: is there a clever and efficient way of
> calculating these differences in data values?
>
> The only thing I can think of is picking a data value and
> select min(data) from foo where userid=5 and data>200
> and then calculate the difference and do what I want with it, and the
> repeat this process with the last selected data value.
> I guess that will work, but I'm curious, are there other ways?
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>