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 1k4kHD-0001iu-FY for pgsql-sql@arkaria.postgresql.org; Sun, 09 Aug 2020 12:18:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k4kHC-0003Hl-Du for pgsql-sql@arkaria.postgresql.org; Sun, 09 Aug 2020 12:18:10 +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 <2.andriychuk@gmail.com>) id 1k4kHC-0003Hc-39 for pgsql-sql@lists.postgresql.org; Sun, 09 Aug 2020 12:18:10 +0000 Received: from mail-pg1-x542.google.com ([2607:f8b0:4864:20::542]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from <2.andriychuk@gmail.com>) id 1k4kHA-0005V1-54 for pgsql-sql@lists.postgresql.org; Sun, 09 Aug 2020 12:18:09 +0000 Received: by mail-pg1-x542.google.com with SMTP id o13so3416872pgf.0 for ; Sun, 09 Aug 2020 05:18:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=eiXTrTk6dkVurCs2Sa4/2E8p9z8lwTI+Tk7FnI2QuPI=; b=q9v6cLCJzRl8OEqCI/caLn5PpimzhWtSfxQ5LqdAzlhzsHrrmNX3IshQridww6Cd73 gLxQoljcdQhldAD6YEyGiGZexIi/qxn2dDclsZvjGKLau1S4mGwOR4eGiSrj0lu6s88E ifDGZRGfSJCs5EoopWprpPW7LMWMYOfBJ2P4xU6uIu6HRlwS9xms3cn/7rJBaP/fIIiP 4lhJjl/8wOWuJi+QhSH9+CzlQwEhqeIIdYSjOM6hksVO/C6Vz6xG7F5rr/WBRhQopdL2 f4OAcFPzENTi9ceDP0uDM4uhD7bnPyJsbTouIZ6M8hKdN05Gx1wlxtYMvXmm1CJmC6q/ 2exQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=eiXTrTk6dkVurCs2Sa4/2E8p9z8lwTI+Tk7FnI2QuPI=; b=nWkh7PEPn5JbX5gOT9OqLbjSSnyskQifUggUxdxtt8FVZC0Go8doL/urSXm+zaRy+l 7Ql/53Sr3T4Qv1DCWIZu4EPdW2uaX4jlrwJB6pdwo8P+coT/uY+2YGNtMJgF5Z+JC5T6 G0kXej4jP+4rwGuEjUfa29/P9v+JLgGO4AYv2FJjCWAmXuMblavipNajhk7q2qZYNbXr jp3c2jwT/UxDonyTN52lG+OP8hU5oSKskkYpKSYTUC/Ao049oK/ustkfUDPntq/sho2h 1c7sGTDbUj0OLxC967bWbW3quoYb67YrcLtnMVRUByrEeubVTPZ+z/bGNu+/z2uvfLDZ idkg== X-Gm-Message-State: AOAM532ZVwge7KEYLOJl6wjaROmPU0+u9sh/QkjGLyuhz1ciejTTe7WL frRUH0NFnGYjJ6bt6WxRUcY= X-Google-Smtp-Source: ABdhPJycxnmuA6zp44eECqewAk6saNHn8XfP6ASG9OU7iyO32+dkjbBJ3BUrAcVMj1ZNbnBgpgrd5A== X-Received: by 2002:a62:b417:: with SMTP id h23mr20071369pfn.118.1596975485404; Sun, 09 Aug 2020 05:18:05 -0700 (PDT) Received: from [192.168.1.19] (198-27-163-68.fiber.dynamic.sonic.net. [198.27.163.68]) by smtp.gmail.com with ESMTPSA id y19sm18738967pfn.77.2020.08.09.05.18.04 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sun, 09 Aug 2020 05:18:04 -0700 (PDT) From: Igor Andriychuk <2.andriychuk@gmail.com> Message-Id: <33E15332-5F19-4D53-83BE-33254C869BC2@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_2AC39A8E-019A-4384-8BFF-C22CA6E6FF66" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.1\)) Subject: Re: recursive sql Date: Sun, 9 Aug 2020 05:18:03 -0700 In-Reply-To: <29781596968237@mail.yandex.com.tr> Cc: "ml@ft-c.de" , "pgsql-sql@lists.postgresql.org" To: Samed YILDIRIM References: <29781596968237@mail.yandex.com.tr> X-Mailer: Apple Mail (2.3608.120.23.2.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_2AC39A8E-019A-4384-8BFF-C22CA6E6FF66 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi Franz, It looks like you are trying to solve a comulative sum. You don=E2=80=99t = need the lag function, instead you should use sum and you will get a = desired result: Select ts, c, sum(c) over(order by ts) c2 from tt order by ts; Best, Igor > On Aug 9, 2020, at 3:38 AM, Samed YILDIRIM wrote: >=20 > Hi Franz, > =20 > Simply you can use window functions[1][2]. > =20 > pgsql-sql=3D# select *, lag(c) over (order by ts) as c2 from tt; > ts | c | c2 > ---------------------+---+---- > 2019-12-31 00:00:00 | 1 | > 2020-01-01 00:00:00 | 2 | 1 > 2020-07-02 00:00:00 | 3 | 2 > 2020-07-06 00:00:00 | 4 | 3 > 2020-07-07 00:00:00 | 5 | 4 > 2020-07-08 00:00:00 | 6 | 5 > (6 rows) > =20 > =20 > I personally prefer to use window functions due to their simplicity. = If you still want to use recursive query: [3] > =20 > pgsql-sql=3D# with recursive rc as ( > select * from (select ts,c,null::numeric as c2 from tt order by ts asc = limit 1) k1 > union > select * from (select tt.ts,tt.c,rc.c as c2 from tt, lateral (select * = from rc) rc where tt.ts > rc.ts order by tt.ts asc limit 1) k2 > ) > select * from rc; > ts | c | c2 > ---------------------+---+---- > 2019-12-31 00:00:00 | 1 | > 2020-01-01 00:00:00 | 2 | 1 > 2020-07-02 00:00:00 | 3 | 2 > 2020-07-06 00:00:00 | 4 | 3 > 2020-07-07 00:00:00 | 5 | 4 > 2020-07-08 00:00:00 | 6 | 5 > (6 rows) > =20 > [1]: https://www.postgresql.org/docs/12/functions-window.html = > [2]: https://www.postgresql.org/docs/12/tutorial-window.html = > [3]: https://www.postgresql.org/docs/12/queries-with.html = > =20 > Best regards. > Samed YILDIRIM > =20 > =20 > =20 > 09.08.2020, 09:29, "ml@ft-c.de" : > Hello, >=20 > the table > create table tt ( > ts timestamp, > c numeric) ; >=20 > 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); >=20 > My question: It is possible to get an > additional column (named c2) > with > ( c from current row ) + ( c2 from the previous row ) as c2 >=20 > the result: > ts c c2 > .. 1 1 -- or null in the first row > .. 2 3 > .. 3 6 > .. 4 10 > ... >=20 > with recursive ema as () > select ts, c, > -- many many computed_rows > -- as c2 > from tt -- <- I need tt on this place >=20 >=20 > thank you for help > Franz >=20 > =20 >=20 --Apple-Mail=_2AC39A8E-019A-4384-8BFF-C22CA6E6FF66 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Hi = Franz,


It looks like you are trying to solve a = comulative sum. You don=E2=80=99t need the lag function, instead you = should use sum and you will get a desired result:

Select ts, c,  sum(c) over(order by ts) c2 from tt order = by ts;

Best,
Igor



On Aug = 9, 2020, at 3:38 AM, Samed YILDIRIM <samed@reddoc.net> = wrote:

Hi Franz,
 
Simply you can use window functions[1][2].
 
pgsql-sql=3D# = select *, lag(c) over (order by ts) as c2 from tt;
ts= | c | c2
---------------------+---+----
2019-12-31 00:00:00 | 1 |
2020-01-01 = 00:00:00 | 2 | 1
2020-07-02 00:00:00 | 3 | = 2
2020-07-06 00:00:00 | 4 | 3
2020-07-07 00:00:00 | 5 | 4
2020-07-08 = 00:00:00 | 6 | 5
(6 rows)
 
 
I = personally prefer to use window functions due to their simplicity. If = you still want to use recursive query: [3]
 
pgsql-sql=3D# with recursive rc as = (
select * from (select ts,c,null::numeric as c2 = from tt order by ts asc limit 1) k1
union
select * from (select tt.ts,tt.c,rc.c as c2 from tt, lateral = (select * from rc) rc where tt.ts > rc.ts order by tt.ts asc limit 1) = k2
)
select * from = rc;
ts | c | c2
---------------------+---+----
2019-12-31 = 00:00:00 | 1 |
2020-01-01 00:00:00 | 2 | = 1
2020-07-02 00:00:00 | 3 | 2
2020-07-06 00:00:00 | 4 | 3
2020-07-07 = 00:00:00 | 5 | 4
2020-07-08 00:00:00 | 6 | = 5
(6 rows)
 
[1]: https://www.postgresql.org/docs/12/functions-window.html
[2]: https://www.postgresql.org/docs/12/tutorial-window.html
[3]: https://www.postgresql.org/docs/12/queries-with.html
<= div class=3D""> 
Best regards.
Samed YILDIRIM
 
 
 
09.08.2020, 09:29, "ml@ft-c.de" <ml@ft-c.de>:

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

 


= --Apple-Mail=_2AC39A8E-019A-4384-8BFF-C22CA6E6FF66--