Reply-To: ml@ft-c.de
Subject: Re: recursive sql
References: <eaf4082e-79ac-7a5d-bcf8-63ce66087365@ft-c.de>
 <29781596968237@mail.yandex.com.tr>
To: pgsql-sql@lists.postgresql.org
From: ml@ft-c.de
Message-ID: <65f26434-b2ed-7fc2-a5ad-e0365e657d5a@ft-c.de>
Date: Sun, 9 Aug 2020 13:57:43 +0200
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: <29781596968237@mail.yandex.com.tr>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: de-DE
Content-Transfer-Encoding: 8bit
Precedence: bulk

Hallo,

with the window function lag there is a shift of one or more rows. Every 
row connects to the previous row := lag(column,1).

What I am looking for:
ts  c  c2
..  1  1  -- or null in the first row
..  2  3  -- it is the result of 1 + 2
..  3  6  -- it is the result of 3 + 3
..  4  10 -- it is the result of 6 + 4


Franz

On 8/9/20 12:38 PM, Samed YILDIRIM 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
> [2]: https://www.postgresql.org/docs/12/tutorial-window.html
> [3]: 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
>