Re: recursive sql - Mailing list pgsql-sql
From | ml@ft-c.de |
---|---|
Subject | Re: recursive sql |
Date | |
Msg-id | 65f26434-b2ed-7fc2-a5ad-e0365e657d5a@ft-c.de Whole thread Raw |
In response to | recursive sql (ml@ft-c.de) |
List | 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@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 >