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 1k4mDn-0006iI-Aj for pgsql-sql@arkaria.postgresql.org; Sun, 09 Aug 2020 14:22:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k4mDk-0007ZT-JF for pgsql-sql@arkaria.postgresql.org; Sun, 09 Aug 2020 14:22:44 +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 <2.andriychuk@gmail.com>) id 1k4mDk-0007ZM-5M for pgsql-sql@lists.postgresql.org; Sun, 09 Aug 2020 14:22:44 +0000 Received: from mail-pl1-x62e.google.com ([2607:f8b0:4864:20::62e]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from <2.andriychuk@gmail.com>) id 1k4mDh-0003qy-UQ for pgsql-sql@lists.postgresql.org; Sun, 09 Aug 2020 14:22:42 +0000 Received: by mail-pl1-x62e.google.com with SMTP id t11so3500782plr.5 for ; Sun, 09 Aug 2020 07:22:41 -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=/WNRQ3uXQVQo9BRXcjOK8pP50Ixq7CM1JR+WFNr/sWw=; b=ue8H1JV4K/Wct+ctv1EOircTiWsF+B0+mu2U/L/vkULTqDWrtadvUnVSEQe6Q+Z2kI SFsm4J5XeDLouJ8xFz3WRJypMko+b2juBnpEWKCQEEOWv6VsG1gRHZU1vECyMJ1Fk8Ia 1KN3maNNHV0FxUPuB0ExtuEv3+7u5+ndhPYm3x3G+H8n0Npwm0xh44g8Aui3LVJFh8pl Xa30Ln85AMEIrp2dW2RpG4s6LNlHb6KSL9pax2kmewrjpIQpiPy0zxhpko+Rf81+XFv0 z4S7TmUGSzSLogujIxIwn4JheINA4tc2m2ifBplcBTMTPi1LjFS360BHysqXdo+l+KU3 mm7g== 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=/WNRQ3uXQVQo9BRXcjOK8pP50Ixq7CM1JR+WFNr/sWw=; b=Gb7Byf0CJDnDfQkl+7cP0tSDPRcddwBbMVOb3/MBxaQ/rMY0NRMabsVbDxyYGzG0vj SE5YTOG4mYNRsMQsPrFvUk3q3jfKL7vYbpt2WAfNEoGxm4/tGr2MxOz8twNOfVhMjLzR /1WX/A7+Gr/Lb/+//Ev1C9N3E3+Hm/iFWk0kQ11YYISmRVgy6Ms9OgbQORzl9lv9sV/O 5our3YaTIaiqc2SEqRGsYgVMxnJnYpHxw5PQGjkJt/7u/nggHpxJyN2p+kRwOOjI57wE 0mhqVaw7OUK7tiqWXo9aa1MB+mzmal6u0CKQBbw9efko9cPnXfqeTa5jkVsbm7/Xb95G YJdQ== X-Gm-Message-State: AOAM531J/BUoAE9nG/u+eZriGLBPzZASv3HN5w3J6nu2ccskCahuwH62 Sr2H9F2hAxj2HGdTzlf03PoHVTKqvU4= X-Google-Smtp-Source: ABdhPJzn646rUrz7pJ8Xrz5oEP6rFaqMCtbwqylZZw+0JDSL2NRth9QP+zG1Je/AizMllhl26cAJWw== X-Received: by 2002:a17:90a:ea0b:: with SMTP id w11mr20574460pjy.80.1596982960611; Sun, 09 Aug 2020 07:22:40 -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 gm9sm16512852pjb.12.2020.08.09.07.22.39 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sun, 09 Aug 2020 07:22:39 -0700 (PDT) From: Igor Andriychuk <2.andriychuk@gmail.com> Message-Id: <542D840A-878F-445F-9D3A-B093E14EA2A0@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_9A8718FC-1BB8-4608-B891-E0AE76A7DB60" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.1\)) Subject: Re: recursive sql Date: Sun, 9 Aug 2020 07:22:38 -0700 In-Reply-To: Cc: pgsql-sql@lists.postgresql.org To: ml@ft-c.de References: 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=_9A8718FC-1BB8-4608-B891-E0AE76A7DB60 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=us-ascii 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 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 > -- as c2 > from tt -- <- I need tt on this place > > > thank you for help > Franz > > --Apple-Mail=_9A8718FC-1BB8-4608-B891-E0AE76A7DB60 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii I = copied over your typo :-), should be:

with recursive r as(       =        
select ts, c, row_id from rnk where rnk.row_id =3D 1
union 
select rnk.ts, rnk.c*0.33= + r.c*0.67, rnk.row_id
from
r
join
rnk
on
r.row_id =3D 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 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



= --Apple-Mail=_9A8718FC-1BB8-4608-B891-E0AE76A7DB60--