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?
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@gmail.com> wrote:
Show quoted text
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, 231I want the values
5,10
5,21what 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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, 231I 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:
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:
Show quoted text
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!