Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-sql-owner+archive@lists.postgresql.org>)
	id 1k4msC-0008IT-Cj
	for pgsql-sql@arkaria.postgresql.org; Sun, 09 Aug 2020 15:04:32 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-sql-owner+archive@lists.postgresql.org>)
	id 1k4msB-0003ME-A5
	for pgsql-sql@arkaria.postgresql.org; Sun, 09 Aug 2020 15:04:31 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <ml@ft-c.de>)
	id 1k4msB-0003M7-3Y
	for pgsql-sql@lists.postgresql.org; Sun, 09 Aug 2020 15:04:31 +0000
Received: from einhorn-mail.in-berlin.de ([217.197.80.20])
	by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <ml@ft-c.de>)
	id 1k4ms9-0006vQ-4X
	for pgsql-sql@lists.postgresql.org; Sun, 09 Aug 2020 15:04:30 +0000
X-Envelope-From: ml@ft-c.de
X-Envelope-To: <pgsql-sql@lists.postgresql.org>
Received: from authenticated.user (localhost [127.0.0.1]) by
 einhorn.in-berlin.de  with ESMTPSA id 079F4RbE017499
        (version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384 bits=256 verify=NOT)
        for <pgsql-sql@lists.postgresql.org>; Sun, 9 Aug 2020 17:04:28 +0200
Reply-To: ml@ft-c.de
Subject: Re: recursive sql
To: pgsql-sql@lists.postgresql.org
References: <eaf4082e-79ac-7a5d-bcf8-63ce66087365@ft-c.de>
 <542D840A-878F-445F-9D3A-B093E14EA2A0@gmail.com>
From: ml@ft-c.de
Message-ID: <f3d074ba-3bba-3772-0775-55600405e847@ft-c.de>
Date: Sun, 9 Aug 2020 17:04:27 +0200
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: <542D840A-878F-445F-9D3A-B093E14EA2A0@gmail.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Language: de-DE
Content-Transfer-Encoding: 8bit
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Precedence: bulk

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
>>
>>
>