From: | Sándor Daku <daku(dot)sandor(at)gmail(dot)com> |
---|---|
To: | Tim Smith <randomdev4+postgres(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Retrieving value of column X days later |
Date: | 2016-08-07 20:05:48 |
Message-ID: | CAKyoTgZZUata3vc+67P8A=L=MCwTeLpCiL6uuLaL6+Mif13OMw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 7 August 2016 at 21:23, Tim Smith <randomdev4+postgres(at)gmail(dot)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(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Victor Yegorov | 2016-08-07 20:41:09 | Re: Retrieving value of column X days later |
Previous Message | Tim Smith | 2016-08-07 19:23:07 | Retrieving value of column X days later |