agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Samed YILDIRIM <samed@reddoc.net>
To: ml@ft-c.de <ml@ft-c.de>
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: recursive sql
Date: Sun, 09 Aug 2020 15:08:57 +0300
Message-ID: <187651596974558@mail.yandex.com.tr> (raw)
In-Reply-To: <65f26434-b2ed-7fc2-a5ad-e0365e657d5a@ft-c.de>
References: <eaf4082e-79ac-7a5d-bcf8-63ce66087365@ft-c.de>
<29781596968237@mail.yandex.com.tr>
<65f26434-b2ed-7fc2-a5ad-e0365e657d5a@ft-c.de>
<div>Hi Frank,</div><div> </div><div>It seems I need to read more carefully :)</div><div> </div><div>With window functions;</div><div><div><div>pgsql-sql=# select *,sum(c) over (order by ts) from tt;</div><div>ts | c | sum</div><div>---------------------+---+-----</div><div>2019-12-31 00:00:00 | 1 | 1</div><div>2020-01-01 00:00:00 | 2 | 3</div><div>2020-07-02 00:00:00 | 3 | 6</div><div>2020-07-06 00:00:00 | 4 | 10</div><div>2020-07-07 00:00:00 | 5 | 15</div><div>2020-07-08 00:00:00 | 6 | 21</div><div>(6 rows)</div><div> </div><div><br />With recursive query:</div></div><div><div>pgsql-sql=# with recursive rc as (</div><div>select * from (select ts,c,c as c2 from tt order by ts asc limit 1) sq1</div><div>union</div><div>select * from (select tt.ts,tt.c,tt.c+rc.c2 as c2 from tt, lateral (select * from rc order by ts desc limit 1) rc where tt.ts > rc.ts order by tt.ts asc limit 1) sq2</div><div>)</div><div>select * from rc;</div><div>ts | c | c2</div><div>---------------------+---+----</div><div>2019-12-31 00:00:00 | 1 | 1</div><div>2020-01-01 00:00:00 | 2 | 3</div><div>2020-07-02 00:00:00 | 3 | 6</div><div>2020-07-06 00:00:00 | 4 | 10</div><div>2020-07-07 00:00:00 | 5 | 15</div><div>2020-07-08 00:00:00 | 6 | 21</div><div>(6 rows)</div></div></div><div> </div><div>Best regards.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>09.08.2020, 14:57, "ml@ft-c.de" <ml@ft-c.de>:</div><blockquote><p>Hallo,<br /><br />with the window function lag there is a shift of one or more rows. Every<br />row connects to the previous row := lag(column,1).<br /><br />What I am looking for:<br />ts c c2<br />.. 1 1 -- or null in the first row<br />.. 2 3 -- it is the result of 1 + 2<br />.. 3 6 -- it is the result of 3 + 3<br />.. 4 10 -- it is the result of 6 + 4<br /><br /><br />Franz<br /><br />On 8/9/20 12:38 PM, Samed YILDIRIM wrote:</p><blockquote> Hi Franz,<br /> Simply you can use window functions[1][2].<br /> pgsql-sql=# select *, lag(c) over (order by ts) as c2 from tt;<br /> ts | c | c2<br /> ---------------------+---+----<br /> 2019-12-31 00:00:00 | 1 |<br /> 2020-01-01 00:00:00 | 2 | 1<br /> 2020-07-02 00:00:00 | 3 | 2<br /> 2020-07-06 00:00:00 | 4 | 3<br /> 2020-07-07 00:00:00 | 5 | 4<br /> 2020-07-08 00:00:00 | 6 | 5<br /> (6 rows)<br /> I personally prefer to use window functions due to their simplicity. If<br /> you still want to use recursive query: [3]<br /> pgsql-sql=# with recursive rc as (<br /> select * from (select ts,c,null::numeric as c2 from tt order by ts asc<br /> limit 1) k1<br /> union<br /> select * from (select tt.ts,tt.c,rc.c as c2 from tt, lateral (select *<br /> from rc) rc where tt.ts > rc.ts order by tt.ts asc limit 1) k2<br /> )<br /> select * from rc;<br /> ts | c | c2<br /> ---------------------+---+----<br /> 2019-12-31 00:00:00 | 1 |<br /> 2020-01-01 00:00:00 | 2 | 1<br /> 2020-07-02 00:00:00 | 3 | 2<br /> 2020-07-06 00:00:00 | 4 | 3<br /> 2020-07-07 00:00:00 | 5 | 4<br /> 2020-07-08 00:00:00 | 6 | 5<br /> (6 rows)<br /> [1]: <a href="https://www.postgresql.org/docs/12/functions-window.html">https://www.postgresql.org/docs/12/... /> [2]: <a href="https://www.postgresql.org/docs/12/tutorial-window.html">https://www.postgresql.org/docs/12/t... /> [3]: <a href="https://www.postgresql.org/docs/12/queries-with.html">https://www.postgresql.org/docs/12/quer... /> Best regards.<br /> Samed YILDIRIM<br /> 09.08.2020, 09:29, "<a href="mailto:ml@ft-c.de">ml@ft-c.de</a>" <<a href="mailto:ml@ft-c.de">ml@ft-c.de</a>>:<br /> <br /> Hello,<br /> <br /> the table<br /> create table tt (<br /> ts timestamp,<br /> c numeric) ;<br /> <br /> insert into tt values<br /> ('2019-12-31',1), ('2020-01-01',2),<br /> ('2020-07-02',3), ('2020-07-06',4),<br /> ('2020-07-07',5), ('2020-07-08',6);<br /> <br /> My question: It is possible to get an<br /> additional column (named c2)<br /> with<br /> ( c from current row ) + ( c2 from the previous row ) as c2<br /> <br /> the result:<br /> ts c c2<br /> .. 1 1 -- or null in the first row<br /> .. 2 3<br /> .. 3 6<br /> .. 4 10<br /> ...<br /> <br /> with recursive ema as ()<br /> select ts, c,<br /> -- many many computed_rows<br /> -- <code> as c2<br /> from tt -- <- I need tt on this place<br /> <br /> <br /> thank you for help<br /> Franz<br /> </blockquote><p><br /> </p></blockquote>
view thread (16+ messages) latest in thread
Message-ID: <187651596974558@mail.yandex.com.tr>
Permalink: ../187651596974558@mail.yandex.com.tr/
Also on: postgresql.org/message-id/187651596974558@mail.yandex.com.tr
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: samed@reddoc.net, ml@ft-c.de, pgsql-sql@lists.postgresql.org
Subject: Re: recursive sql
In-Reply-To: <187651596974558@mail.yandex.com.tr>
* 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