agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: ml@ft-c.de
To: pgsql-sql@lists.postgresql.org
Subject: Re: recursive sql
Date: Sun, 9 Aug 2020 17:04:27 +0200
Message-ID: <f3d074ba-3bba-3772-0775-55600405e847@ft-c.de> (raw)
In-Reply-To: <542D840A-878F-445F-9D3A-B093E14EA2A0@gmail.com>
References: <eaf4082e-79ac-7a5d-bcf8-63ce66087365@ft-c.de>
<542D840A-878F-445F-9D3A-B093E14EA2A0@gmail.com>
Hello,
It works - the result ist correct
Thank you,
Franz
On 8/9/20 4:22 PM, Igor Andriychuk wrote:
> I copied over your typo :-), should be:
>
> *with* *recursive* r *as*(
> *select*ts, c, row_id*from*rnk*where*rnk.row_id= 1
> *union*
> *select*rnk.ts, rnk.c*0.33+ r.c**0.67*, rnk.row_id
> *from*
> r
> *join*
> rnk
> *on*
> r.row_id= rnk.row_id- 1
> ),
> rnk*as*(
> *select**, *row_number*() *over*(*order**by*ts) row_id*from*tt
> )
> *select*ts, c*from*r*order**by*ts;
>
>> On Aug 8, 2020, at 11:28 PM, ml@ft-c.de <mailto:ml@ft-c.de> wrote:
>>
>> Hello,
>>
>> the table
>> create table tt (
>> ts timestamp,
>> c numeric) ;
>>
>> insert into tt values
>> ('2019-12-31',1), ('2020-01-01',2),
>> ('2020-07-02',3), ('2020-07-06',4),
>> ('2020-07-07',5), ('2020-07-08',6);
>>
>> My question: It is possible to get an
>> additional column (named c2)
>> with
>> ( c from current row ) + ( c2 from the previous row ) as c2
>>
>> the result:
>> ts c c2
>> .. 1 1 -- or null in the first row
>> .. 2 3
>> .. 3 6
>> .. 4 10
>> ...
>>
>> with recursive ema as ()
>> select ts, c,
>> -- many many computed_rows
>> -- <code> as c2
>> from tt -- <- I need tt on this place
>>
>>
>> thank you for help
>> Franz
>>
>>
>
view thread (16+ messages)
Message-ID: <f3d074ba-3bba-3772-0775-55600405e847@ft-c.de>
Permalink: ../f3d074ba-3bba-3772-0775-55600405e847@ft-c.de/
Also on: postgresql.org/message-id/f3d074ba-3bba-3772-0775-55600405e847@ft-c.de
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ml@ft-c.de, pgsql-sql@lists.postgresql.org
Subject: Re: recursive sql
In-Reply-To: <f3d074ba-3bba-3772-0775-55600405e847@ft-c.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox