Обсуждение: 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 !

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