Thread: 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:
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:
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> 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:
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!