Thread: Retrieving value of column X days later
Hi, Let's say I've got a table : create table test ( when date, foo numeric, bar numeric, alice numeric, bob numeric); insert into test values ('2016-01-01',1,2,3,4); insert into test values ('2016-01-02',5,6,7,8); insert into test values ('2016-01-03',9,10,11,12); insert into test values ('2016-01-04',13,14,15,16); insert into test values ('2016-01-05',17,18,19,20); What I would like to do is the following : (1) Given "select alice,bob from test where foo=1 and bar=2" I would like to return the values of alice, bob *and* the value of foo four days later (i.e. "17" in the above example). (2) But there may be times where there are insufficient data points, and so I would want to retrieve the last available value (e.g. "select alice,bob from test where foo=9 and bar=10", there is 4 days hence, therefore it would return the last available, i.e. "17" in this example, even though that is only 2 days hence). Any ideas welcome ! Thanks !
On 7 August 2016 at 21:23, Tim Smith <randomdev4+postgres@gmail.com> wrote:
Hi,
Let's say I've got a table :
create table test (
when date,
foo numeric,
bar numeric,
alice numeric,
bob numeric);
insert into test values ('2016-01-01',1,2,3,4);
insert into test values ('2016-01-02',5,6,7,8);
insert into test values ('2016-01-03',9,10,11,12);
insert into test values ('2016-01-04',13,14,15,16);
insert into test values ('2016-01-05',17,18,19,20);
What I would like to do is the following :
(1) Given "select alice,bob from test where foo=1 and bar=2" I would
like to return the values of alice, bob *and* the value of foo four
days later (i.e. "17" in the above example).
(2) But there may be times where there are insufficient data points,
and so I would want to retrieve the last available value (e.g. "select
alice,bob from test where foo=9 and bar=10", there is 4 days hence,
therefore it would return the last available, i.e. "17" in this
example, even though that is only 2 days hence).
Any ideas welcome !
Thanks !
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
You can do something like this:
select alice,bob,(select foo from test as t where t.when>=test.when and t.when<=test.when+('4 day'::interval) order by when desc limit 1 ) from test where foo=1 and bar=2;
The "t.when>=test.when" part's purpose is not to select too many records. It works without this but you get better performance if there are many records in your table.
Regards,
Sándor
2016-08-07 22:23 GMT+03:00 Tim Smith <randomdev4+postgres@gmail.com>:
create table test (
when date,
foo numeric,
bar numeric,
alice numeric,
bob numeric);
insert into test values ('2016-01-01',1,2,3,4);
insert into test values ('2016-01-02',5,6,7,8);
insert into test values ('2016-01-03',9,10,11,12);
insert into test values ('2016-01-04',13,14,15,16);
insert into test values ('2016-01-05',17,18,19,20);
I had to rename column "when" into "when_d", as I do not like quoting identifiers.
Try this query with window functions:
SELECT *,lead(foo,4) OVER (ORDER BY when_d),
last_value(foo) OVER (ORDER BY when_d RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM test;
This will give you the ability to lookup needed values.
You'll have to use subquery though, as window functions are evaluated after the `WHERE` clause.
--
Victor Y. Yegorov
Thank you Sándor. Will experiment with this over the next couple of days. On 7 August 2016 at 21:05, Sándor Daku <daku.sandor@gmail.com> wrote: > On 7 August 2016 at 21:23, Tim Smith <randomdev4+postgres@gmail.com> wrote: >> >> Hi, >> >> Let's say I've got a table : >> >> create table test ( >> when date, >> foo numeric, >> bar numeric, >> alice numeric, >> bob numeric); >> >> insert into test values ('2016-01-01',1,2,3,4); >> insert into test values ('2016-01-02',5,6,7,8); >> insert into test values ('2016-01-03',9,10,11,12); >> insert into test values ('2016-01-04',13,14,15,16); >> insert into test values ('2016-01-05',17,18,19,20); >> >> >> What I would like to do is the following : >> >> >> (1) Given "select alice,bob from test where foo=1 and bar=2" I would >> like to return the values of alice, bob *and* the value of foo four >> days later (i.e. "17" in the above example). >> >> >> (2) But there may be times where there are insufficient data points, >> and so I would want to retrieve the last available value (e.g. "select >> alice,bob from test where foo=9 and bar=10", there is 4 days hence, >> therefore it would return the last available, i.e. "17" in this >> example, even though that is only 2 days hence). >> >> >> Any ideas welcome ! >> >> Thanks ! >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > You can do something like this: > > select alice,bob,(select foo from test as t where t.when>=test.when and > t.when<=test.when+('4 day'::interval) order by when desc limit 1 ) from test > where foo=1 and bar=2; > > The "t.when>=test.when" part's purpose is not to select too many records. It > works without this but you get better performance if there are many records > in your table. > > Regards, > Sándor >
Thank you Victor Will experiment with this over the next couple of days. On 7 August 2016 at 21:41, Victor Yegorov <vyegorov@gmail.com> wrote: > 2016-08-07 22:23 GMT+03:00 Tim Smith <randomdev4+postgres@gmail.com>: >> >> create table test ( >> when date, >> foo numeric, >> bar numeric, >> alice numeric, >> bob numeric); >> >> insert into test values ('2016-01-01',1,2,3,4); >> insert into test values ('2016-01-02',5,6,7,8); >> insert into test values ('2016-01-03',9,10,11,12); >> insert into test values ('2016-01-04',13,14,15,16); >> insert into test values ('2016-01-05',17,18,19,20); > > > I had to rename column "when" into "when_d", as I do not like quoting > identifiers. > > Try this query with window functions: > > SELECT *,lead(foo,4) OVER (ORDER BY when_d), > last_value(foo) OVER (ORDER BY when_d RANGE BETWEEN UNBOUNDED > PRECEDING AND UNBOUNDED FOLLOWING) > FROM test; > > This will give you the ability to lookup needed values. > You'll have to use subquery though, as window functions are evaluated after > the `WHERE` clause. > > > -- > Victor Y. Yegorov