From: | ml(at)ft-c(dot)de |
---|---|
To: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Re: recursive sql |
Date: | 2020-08-09 11:57:43 |
Message-ID: | 65f26434-b2ed-7fc2-a5ad-e0365e657d5a@ft-c.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)ft-c(dot)de" <ml(at)ft-c(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Samed YILDIRIM | 2020-08-09 12:08:57 | Re: recursive sql |
Previous Message | Samed YILDIRIM | 2020-08-09 10:38:22 | Re: recursive sql |