Обсуждение: How can I calculate differences between values

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?

On Tue, Nov 10, 2009 at 6:40 AM, A B <gentosaker@gmail.com> wrote:

> For example
> if the table contains:
>
> 4, 100
> 5, 200
> 5, 210
> 5, 231
>
>
> I want the values
>
> 5,10
> 5,21

> I guess that will work, but I'm curious, are there other ways?

I think this kind of operation is best suited for CTEs and Windowing.
I just wish I was more experienced with it to give more specific
advice.  However, here are the doc that might start you on the right
path:
http://www.postgresql.org/docs/8.4/interactive/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
http://www.postgresql.org/docs/8.4/interactive/sql-select.html

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

With 8.4's analytic capabilities you can do this:

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

Thank you! That worked perfectly!

Hi;

also you can try this query which should work for any version

SELECT
    a.userid, a.data, max(f.data), a.data - max(f.data)
FROM
    (SELECT userid, data FROM foo EXCEPT SELECT userid, min(data) FROM foo GROUP BY userid) a LEFT JOIN foo f ON (f.userid = a.userid AND f.data < a.data) GROUP BY a.userid, a.data ORDER BY a.userid, a.data

A B wrote:

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;   

Thank you! That worked perfectly!