agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Igor Andriychuk <2.andriychuk@gmail.com>
To: ml@ft-c.de
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: recursive sql
Date: Sun, 9 Aug 2020 07:22:38 -0700
Message-ID: <542D840A-878F-445F-9D3A-B093E14EA2A0@gmail.com> (raw)
In-Reply-To: <eaf4082e-79ac-7a5d-bcf8-63ce66087365@ft-c.de>
References: <eaf4082e-79ac-7a5d-bcf8-63ce66087365@ft-c.de>
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
> -- <code> as c2
> from tt -- <- I need tt on this place
>
>
> thank you for help
> Franz
>
>
view thread (16+ messages) latest in thread
Message-ID: <542D840A-878F-445F-9D3A-B093E14EA2A0@gmail.com>
Permalink: ../542D840A-878F-445F-9D3A-B093E14EA2A0@gmail.com/
Also on: postgresql.org/message-id/542D840A-878F-445F-9D3A-B093E14EA2A0@gmail.com
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: 2.andriychuk@gmail.com, ml@ft-c.de, pgsql-sql@lists.postgresql.org
Subject: Re: recursive sql
In-Reply-To: <542D840A-878F-445F-9D3A-B093E14EA2A0@gmail.com>
* 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