agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Igor Andriychuk <2.andriychuk@gmail.com>
To: Samed YILDIRIM <samed@reddoc.net>
Cc: ml@ft-c.de <ml@ft-c.de>
Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: recursive sql
Date: Sun, 9 Aug 2020 05:18:03 -0700
Message-ID: <33E15332-5F19-4D53-83BE-33254C869BC2@gmail.com> (raw)
In-Reply-To: <29781596968237@mail.yandex.com.tr>
References: <eaf4082e-79ac-7a5d-bcf8-63ce66087365@ft-c.de>
<29781596968237@mail.yandex.com.tr>
Hi Franz,
It looks like you are trying to solve a comulative sum. You don’t 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=# 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=# 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 <https://www.postgresql.org/docs/12/functions-window.html;
> [2]: https://www.postgresql.org/docs/12/tutorial-window.html <https://www.postgresql.org/docs/12/tutorial-window.html;
> [3]: https://www.postgresql.org/docs/12/queries-with.html <https://www.postgresql.org/docs/12/queries-with.html;
>
> 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
>
>
>
view thread (16+ messages) latest in thread
Message-ID: <33E15332-5F19-4D53-83BE-33254C869BC2@gmail.com>
Permalink: ../33E15332-5F19-4D53-83BE-33254C869BC2@gmail.com/
Also on: postgresql.org/message-id/33E15332-5F19-4D53-83BE-33254C869BC2@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, samed@reddoc.net, ml@ft-c.de, pgsql-sql@lists.postgresql.org
Subject: Re: recursive sql
In-Reply-To: <33E15332-5F19-4D53-83BE-33254C869BC2@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