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 ) id 1k4hvU-00043n-Q9 for pgsql-sql@arkaria.postgresql.org; Sun, 09 Aug 2020 09:47:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k4hvR-00024r-Om for pgsql-sql@arkaria.postgresql.org; Sun, 09 Aug 2020 09:47:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k4gAg-000661-1E for pgsql-sql@lists.postgresql.org; Sun, 09 Aug 2020 07:55:10 +0000 Received: from smtp-out1.eurodata.de ([212.89.148.71]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k4gAb-0000h7-5O for pgsql-sql@lists.postgresql.org; Sun, 09 Aug 2020 07:55:08 +0000 Received: from localhost.localdomain (localhost [127.0.0.1]) by smtp-out1.eurodata.de (Postfix) with ESMTP id 693562001130 for ; Sun, 9 Aug 2020 09:55:01 +0200 (CEST) Received: from mx1.eurodata.de (mx1.eurodata.de [212.89.148.81]) by smtp-out1.eurodata.de (Postfix) with ESMTP id 4876F2001123 for ; Sun, 9 Aug 2020 09:55:01 +0200 (CEST) Received: from ed-imap.hip99.ncc.eurodata.de ([10.2.2.99]) by mx1.eurodata.de with ESMTP id 0797t1ub008658-0797t1uc008658; Sun, 9 Aug 2020 09:55:01 +0200 Received: from exchange-srv01.edsb.eurodata.de (mailex.eurodata.de [10.2.2.78]) by ed-imap.hip99.ncc.eurodata.de (Postfix) with ESMTP id 240DF17F91; Sun, 9 Aug 2020 09:55:01 +0200 (CEST) Received: from exchange-srv01.edsb.eurodata.de (10.2.2.78) by exchange-srv01.edsb.eurodata.de (10.2.2.78) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256_P256) id 15.1.1979.3; Sun, 9 Aug 2020 09:55:00 +0200 Received: from exchange-srv01.edsb.eurodata.de ([fe80::ce6:f150:3e14:b0c6]) by exchange-srv01.edsb.eurodata.de ([fe80::ce6:f150:3e14:b0c6%12]) with mapi id 15.01.1979.003; Sun, 9 Aug 2020 09:55:00 +0200 From: =?iso-8859-1?Q?St=F6cker=2C_Martin?= To: "pgsql-sql@lists.postgresql.org" , "ml@ft-c.de" Subject: AW: recursive sql Thread-Topic: recursive sql Thread-Index: AQHWbhZfaqLl5XUpkkuTq72lqCZHj6kvaGHU Date: Sun, 9 Aug 2020 07:55:00 +0000 Message-ID: References: In-Reply-To: Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [212.89.147.235] Content-Type: multipart/alternative; boundary="_000_f890658548ac4f8cb7e6cef6d9179605etldatenservicede_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_f890658548ac4f8cb7e6cef6d9179605etldatenservicede_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Franz I think you don't need to use recursivy but window function will do. Mit freundlichen Gr=FC=DFen Martin St=F6cker ----------------------------------------- ETL Datenservice GmbH Widdersdorfer Str. 415 | D-50933 K=F6ln Telefon: +49(0)2219544010 Fax: +49(0)2219544015 Email: Martin.Stoecker@etl-datenservice.de ETL Datenservice GmbH Widdersdorfer Str. 415 =B7 50933 K=F6ln Gesch=E4ftsf=FChrer: Dr. Dirk Goldner, ppa Melanie Lillich Amtsgericht K=F6ln =B7 HRB 75439 =B7 USt.-Id: DE 122 805 685 www.etl-datenservice.de Email: info@etl-datenservice.de Die STB Datenservice ist jetzt ETL Datenservice. Sie erreichen uns nun unter dem Zusatz @etl-datenservice.de ________________________________ Von: ml@ft-c.de Gesendet: Sonntag, 9. August 2020 08:28:44 An: pgsql-sql@lists.postgresql.org Betreff: recursive sql 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 -- as c2 from tt -- <- I need tt on this place thank you for help Franz --_000_f890658548ac4f8cb7e6cef6d9179605etldatenservicede_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi Franz


I think you don't need to use recursivy but window function will do.


Mit freundlichen Gr=FC=DFen

Martin St=F6cker<= span style=3D"font-size:12pt"> 
-----------------------------------------
ETL Datenservice GmbH
Widdersdorfer Str. 415 | D-50933 K= =F6ln
Telefon: +49(0)2219544010
Fax: +49(0)2219544015
Email: Martin.Stoecker@etl-datenservice.= de

E= TL Datenservice GmbH
Widdersdorfer Str. 415 =B7 50933 K=F6ln
Gesch=E4ftsf=FChrer: Dr. Dirk Goldner, ppa M= elanie Lillich
Amtsgericht K=F6ln =B7 HRB 75439 =B7 USt.-Id= : DE 122 805 685
www.etl-datenservice.de
Email: info@etl-datenservice.de

Die STB Datenservice ist jetzt ETL Datenservic= e.
Sie erreichen uns nun unter dem Zusatz @etl-datenservice.de
Von: ml@ft-c.de <ml@ft= -c.de>
Gesendet: Sonntag, 9. August 2020 08:28:44
An: pgsql-sql@lists.postgresql.org
Betreff: recursive sql
 
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 c= 2

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


 --_000_f890658548ac4f8cb7e6cef6d9179605etldatenservicede_--