agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedRecursive SQL
16+ messages / 10 participants
[nested] [flat]
* Recursive SQL
@ 2000-04-19 12:27 Andy Turk <andy_turk@hotmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Andy Turk @ 2000-04-19 12:27 UTC (permalink / raw)
To: pgsql-sql
I was reading Graeme Birchall's SQL Cookbook at
http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM
and came across an *amazing* technique called recursive SQL.
It's a way to traverse tree-like structures with a single SQL statement.
Bizarre stuff--I didn't think this was possible.
Anyway, the technique depends upon being able to create a temporary table
where some of the rows are SELECTed from that very table during its
creation. Essentially, you fill the table with some starting conditions and
then use a UNION ALL to keep adding in the new data after each recursive
pass. Take a look at page 140 in Graeme's book for more info.
I tried this in Postgresql without success. I get syntax errors trying to
create the temporary table. Here's some code derived from Graeme's cookbook:
create table hierarchy (
pkey char(3) not null,
ckey char(3) not null,
num int4,
primary key(pkey, ckey));
copy hierarchy from stdin;
AAA BBB 1
AAA CCC 5
AAA DDD 20
CCC EEE 33
DDD EEE 44
DDD FFF 5
FFF GGG 5
\.
Here's my attempt to write recursive SQL code to find the children of 'AAA':
create temporary table parent (pkey, ckey) as
select pkey, ckey from hierarchy where pkey = 'AAA'
union all
select c.pkey, c.ckey from hierarchy c, parent p
where p.ckey = c.ckey;
select pkey, ckey from parent;
It appears that Postgresql doesn't like a union inside the create statement.
Beyond that, I'm wondering if this technique would even work in Postgresql
if it wasn't designed to handle recursive SQL.
Any thoughts?
Andy Turk
andy_turk@hotmail.com
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Recursive SQL
@ 2000-04-20 03:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andy Turk <andy_turk@hotmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2000-04-20 03:47 UTC (permalink / raw)
To: andy_turk@hotmail.com; +Cc: pgsql-sql
"Andy Turk" <andy_turk@hotmail.com> writes:
> I was reading Graeme Birchall's SQL Cookbook at
> http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM
> and came across an *amazing* technique called recursive SQL.
Interesting, but I think Birchall has confused some very peculiar
(and incorrect) implementation-specific behavior of DB2 with SQL.
This is not SQL.
Leaving aside a minor quibble about whether the WITH syntax he shows
is valid (it's surely not SQL92, although it might be SQL3 if SQL3 ever
becomes a standard), the really fundamental problem is that you cannot
have a SELECT query that inspects its own output. He claims that in
SELECT foo UNION SELECT bar, the "bar" select will somehow see the
output of the "foo" select --- and not only that, but will be
recursively invoked to see its *own* outputs. I do not believe that
any such interpretation can be extracted from the SQL standard.
If SQL worked that way, then simple commands like
UPDATE foo SET x = 42 WHERE y = 44
would be infinite loops, because they'd see the new tuples produced
by their own action and try to update those, leading to more new
tuples, etc etc.
He's built a large intellectual edifice on a DB2 bug.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* RE: Recursive SQL
@ 2000-04-20 23:26 Michael S. Kelly <michaelk@axian.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 16+ messages in thread
From: Michael S. Kelly @ 2000-04-20 23:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; andy_turk@hotmail.com; +Cc: pgsql-sql
I have not looked closely at Graeme Birchall's DB2 SQL Cookbook, but Joe
Celko has a good section in "SQL for Smarties" on representing trees in
relational databases and traversing those trees using standard SQL. He also
discusses some of the extensions various vendors have added to make
traversing trees (w/o temporary tables) simpler.
-=michael=-
*****************************************************
* Michael S. Kelly
* 4800 SW Griffith Dr., Ste. 202
* Beaverton, OR 97005 USA
* voice: (503)644-6106 x122 fax: (503)643-8425
* <michaelk@axian.com>
* http://www.axian.com/
*****************************************************
* Axian: Software Consulting and Training
*****************************************************
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, April 19, 2000 8:47 PM
To: andy_turk@hotmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Recursive SQL
"Andy Turk" <andy_turk@hotmail.com> writes:
> I was reading Graeme Birchall's SQL Cookbook at
> http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM
> and came across an *amazing* technique called recursive SQL.
Interesting, but I think Birchall has confused some very peculiar
(and incorrect) implementation-specific behavior of DB2 with SQL.
This is not SQL.
Leaving aside a minor quibble about whether the WITH syntax he shows
is valid (it's surely not SQL92, although it might be SQL3 if SQL3 ever
becomes a standard), the really fundamental problem is that you cannot
have a SELECT query that inspects its own output. He claims that in
SELECT foo UNION SELECT bar, the "bar" select will somehow see the
output of the "foo" select --- and not only that, but will be
recursively invoked to see its *own* outputs. I do not believe that
any such interpretation can be extracted from the SQL standard.
If SQL worked that way, then simple commands like
UPDATE foo SET x = 42 WHERE y = 44
would be infinite loops, because they'd see the new tuples produced
by their own action and try to update those, leading to more new
tuples, etc etc.
He's built a large intellectual edifice on a DB2 bug.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Recursive SQL
@ 2000-08-04 03:13 database@gurubase.com
0 siblings, 0 replies; 16+ messages in thread
From: database@gurubase.com @ 2000-08-04 03:13 UTC (permalink / raw)
To: pgsql general <pgsql-general@postgreSQL.org>; pgsql-hackers <pgsql-hackers@postgreSQL.org>; pgsql-sql
Dear all,
Is postgresql supporting recursive sql? If no, how can my sql to change for
recursive function ( on 4 or 5 levels are needed ) ?
Many thanks!
Best regards,
Boris
^ permalink raw reply [nested|flat] 16+ messages in thread
* recursive sql
@ 2003-09-05 17:41 floyds@4peakstech.com
0 siblings, 1 reply; 16+ messages in thread
From: floyds@4peakstech.com @ 2003-09-05 17:41 UTC (permalink / raw)
To: pgsql-sql; +Cc: Gregory S. Dodson <greg@truckmaster.com>; Josh Wardle <josh@truckmaster.com>
can anyone recommend a good reference source for doing recursive sql on
postgresql? i want to do something similar to a BOM expansion. (i.e. i need
to traverse a self-referencing table that stores a tree structure and answer
a question like "Get me A and all of A's descendents")
Regards,
Floyd Shackelford
4 Peaks Technology Group, Inc.
VOICE: 334.735.9428
FAX: 702.995.6462
EMAIL: FloydS@4PeaksTech.com
ICQ #: 161371538
PGP Key ID: 0x2E84F2F2
PGP Fone at private.fwshackelford.com on request
Shackelford Motto: ACTA NON VERBA - Actions, not words
Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our
Rights
The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf
"We have allowed our constitutional republic to deteriorate into a virtually
unchecked direct democracy. Today's political process is nothing more than a
street fight between various groups seeking to vote themselves other
people's money. Individual voters tend to support the candidate that
promises them the most federal loot in whatever form, rather than the
candidate who will uphold the rule of law." --Rep. Ron Paul
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: recursive sql
@ 2003-09-06 05:05 sad <sad@bankir.ru>
parent: floyds@4peakstech.com
0 siblings, 0 replies; 16+ messages in thread
From: sad @ 2003-09-06 05:05 UTC (permalink / raw)
To: pgsql-sql
Good day
On Friday 05 September 2003 21:41, you wrote:
> can anyone recommend a good reference source for doing recursive sql on
> postgresql? i want to do something similar to a BOM expansion. (i.e. i need
> to traverse a self-referencing table that stores a tree structure and
> answer a question like "Get me A and all of A's descendents")
"recursive queries" are much slower than queries to a nested-tree.
please find something readable on subject "nested-tree" or ask me to
send you this. You'll see that the maintaining of a nested-tree is
covered by its good profit.
^ permalink raw reply [nested|flat] 16+ messages in thread
* recursive sql
@ 2020-08-09 06:28 ml@ft-c.de
0 siblings, 2 replies; 16+ messages in thread
From: ml@ft-c.de @ 2020-08-09 06:28 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: recursive sql
@ 2020-08-09 10:38 Samed YILDIRIM <samed@reddoc.net>
parent: ml@ft-c.de
1 sibling, 2 replies; 16+ messages in thread
From: Samed YILDIRIM @ 2020-08-09 10:38 UTC (permalink / raw)
To: ml@ft-c.de <ml@ft-c.de>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
<div>Hi Franz,</div><div> </div><div>Simply you can use window functions[1][2].</div><div> </div><div><div>pgsql-sql=# select *, lag(c) over (order by ts) as c2 from tt;</div><div>ts | c | c2</div><div>---------------------+---+----</div><div>2019-12-31 00:00:00 | 1 |</div><div>2020-01-01 00:00:00 | 2 | 1</div><div>2020-07-02 00:00:00 | 3 | 2</div><div>2020-07-06 00:00:00 | 4 | 3</div><div>2020-07-07 00:00:00 | 5 | 4</div><div>2020-07-08 00:00:00 | 6 | 5</div><div>(6 rows)</div><div> </div><div> </div><div>I personally prefer to use window functions due to their simplicity. If you still want to use recursive query: [3]</div><div> </div><div><div><div><div>pgsql-sql=# with recursive rc as (</div><div>select * from (select ts,c,null::numeric as c2 from tt order by ts asc limit 1) k1</div><div>union</div><div>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</div><div>)</div><div>select * from rc;</div><div>ts | c | c2</div><div>---------------------+---+----</div><div>2019-12-31 00:00:00 | 1 |</div><div>2020-01-01 00:00:00 | 2 | 1</div><div>2020-07-02 00:00:00 | 3 | 2</div><div>2020-07-06 00:00:00 | 4 | 3</div><div>2020-07-07 00:00:00 | 5 | 4</div><div>2020-07-08 00:00:00 | 6 | 5</div><div>(6 rows)</div></div></div></div><div> </div><div>[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... regards.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>09.08.2020, 09:29, "ml@ft-c.de" <ml@ft-c.de>:</div><blockquote><p>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 /><br /> </p></blockquote>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: recursive sql
@ 2020-08-09 11:57 ml@ft-c.de
parent: Samed YILDIRIM <samed@reddoc.net>
1 sibling, 1 reply; 16+ messages in thread
From: ml@ft-c.de @ 2020-08-09 11:57 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: recursive sql
@ 2020-08-09 12:08 Samed YILDIRIM <samed@reddoc.net>
parent: ml@ft-c.de
0 siblings, 1 reply; 16+ messages in thread
From: Samed YILDIRIM @ 2020-08-09 12:08 UTC (permalink / raw)
To: ml@ft-c.de <ml@ft-c.de>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
<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>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: recursive sql
@ 2020-08-09 12:18 Igor Andriychuk <2.andriychuk@gmail.com>
parent: Samed YILDIRIM <samed@reddoc.net>
1 sibling, 0 replies; 16+ messages in thread
From: Igor Andriychuk @ 2020-08-09 12:18 UTC (permalink / raw)
To: Samed YILDIRIM <samed@reddoc.net>; +Cc: ml@ft-c.de <ml@ft-c.de>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: recursive sql
@ 2020-08-09 13:25 ml@ft-c.de
parent: Samed YILDIRIM <samed@reddoc.net>
0 siblings, 1 reply; 16+ messages in thread
From: ml@ft-c.de @ 2020-08-09 13:25 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Hello,
sorry for my short explanation. It was not enough to understand the my
task/target.
These are the basic computation for an exponential moving average (ema)
an statistic indicator for trading data.
The components of trading data are
timestamp, High, Low, Open and Close value
For this indicator I need the timestamp and the close value, not more.
For the current day (period) the formula is
EMA = Close(t) * SF + ( (1-SF) * EMA(t-1) )
where Smoothing Factor SF = 2 / (n+1)
The best way is, to explain it with an example:
day close SF close 1-SF EMA(t-1) = part_of_result
1 105,5
2 104 0.33 * 104 + 0.76 * 105,5 = 105.005
3 103.5 0.33 * 103 + 0.76 * 105.005 = 104.508
4 102 0.33 * 102 + 0.76 * 104.508 = 103.680
5 101 0.33 * 101 + 0.76 * 103.680 = 102.795
6 100 0.33 * 100 + 0.76 * 102.795 = 101.872
0.33 and 0.67 are the SF
You see, the result of one line is a component of the next line.
The result for day 6 is 101.872
I need the close value of the current day and
the the close value of the previous day. But before, it must be calculated.
I believe, the best way is, to do it with
"with recursive"
Franz
On 8/9/20 2:08 PM, Samed YILDIRIM wrote:
> Hi Frank,
> It seems I need to read more carefully :)
> With window functions;
> pgsql-sql=# select *,sum(c) over (order by ts) from tt;
> ts | c | sum
> ---------------------+---+-----
> 2019-12-31 00:00:00 | 1 | 1
> 2020-01-01 00:00:00 | 2 | 3
> 2020-07-02 00:00:00 | 3 | 6
> 2020-07-06 00:00:00 | 4 | 10
> 2020-07-07 00:00:00 | 5 | 15
> 2020-07-08 00:00:00 | 6 | 21
> (6 rows)
>
> With recursive query:
> pgsql-sql=# with recursive rc as (
> select * from (select ts,c,c as c2 from tt order by ts asc limit 1) sq1
> union
> 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
> )
> select * from rc;
> ts | c | c2
> ---------------------+---+----
> 2019-12-31 00:00:00 | 1 | 1
> 2020-01-01 00:00:00 | 2 | 3
> 2020-07-02 00:00:00 | 3 | 6
> 2020-07-06 00:00:00 | 4 | 10
> 2020-07-07 00:00:00 | 5 | 15
> 2020-07-08 00:00:00 | 6 | 21
> (6 rows)
> Best regards.
> Samed YILDIRIM
> 09.08.2020, 14:57, "ml@ft-c.de" <ml@ft-c.de>:
>
> 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 <mailto:ml@ft-c.de>"
> <ml@ft-c.de <mailto: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
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: recursive sql
@ 2020-08-09 14:10 Igor Andriychuk <2.andriychuk@gmail.com>
parent: ml@ft-c.de
0 siblings, 0 replies; 16+ messages in thread
From: Igor Andriychuk @ 2020-08-09 14:10 UTC (permalink / raw)
To: ml@ft-c.de; +Cc: pgsql-sql@lists.postgresql.org
Oh, yes, in this case you need a recursion. This is something that came on my mind in short observation:
with recursive r as(
select ts, c, row_id from rnk where rnk.row_id = 1
union
select rnk.ts, rnk.c*0.33 + r.c*0.76, rnk.row_id
from
r
join
rnk
on
r.row_id = rnk.row_id - 1
),
rnk as(
select *, row_number() over(order by ts) row_id from tt
)
select ts, c from r order by ts;
Tested it :-)
> On Aug 9, 2020, at 6:25 AM, ml@ft-c.de wrote:
>
> Hello,
>
> sorry for my short explanation. It was not enough to understand the my task/target.
>
> These are the basic computation for an exponential moving average (ema)
> an statistic indicator for trading data.
>
> The components of trading data are
> timestamp, High, Low, Open and Close value
> For this indicator I need the timestamp and the close value, not more.
>
> For the current day (period) the formula is
>
> EMA = Close(t) * SF + ( (1-SF) * EMA(t-1) )
>
> where Smoothing Factor SF = 2 / (n+1)
>
> The best way is, to explain it with an example:
> day close SF close 1-SF EMA(t-1) = part_of_result
> 1 105,5
> 2 104 0.33 * 104 + 0.76 * 105,5 = 105.005
> 3 103.5 0.33 * 103 + 0.76 * 105.005 = 104.508
> 4 102 0.33 * 102 + 0.76 * 104.508 = 103.680
> 5 101 0.33 * 101 + 0.76 * 103.680 = 102.795
> 6 100 0.33 * 100 + 0.76 * 102.795 = 101.872
>
> 0.33 and 0.67 are the SF
> You see, the result of one line is a component of the next line.
> The result for day 6 is 101.872
>
> I need the close value of the current day and
> the the close value of the previous day. But before, it must be calculated.
>
> I believe, the best way is, to do it with
> "with recursive"
>
> Franz
>
>
> On 8/9/20 2:08 PM, Samed YILDIRIM wrote:
>> Hi Frank,
>> It seems I need to read more carefully :)
>> With window functions;
>> pgsql-sql=# select *,sum(c) over (order by ts) from tt;
>> ts | c | sum
>> ---------------------+---+-----
>> 2019-12-31 00:00:00 | 1 | 1
>> 2020-01-01 00:00:00 | 2 | 3
>> 2020-07-02 00:00:00 | 3 | 6
>> 2020-07-06 00:00:00 | 4 | 10
>> 2020-07-07 00:00:00 | 5 | 15
>> 2020-07-08 00:00:00 | 6 | 21
>> (6 rows)
>> With recursive query:
>> pgsql-sql=# with recursive rc as (
>> select * from (select ts,c,c as c2 from tt order by ts asc limit 1) sq1
>> union
>> 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
>> )
>> select * from rc;
>> ts | c | c2
>> ---------------------+---+----
>> 2019-12-31 00:00:00 | 1 | 1
>> 2020-01-01 00:00:00 | 2 | 3
>> 2020-07-02 00:00:00 | 3 | 6
>> 2020-07-06 00:00:00 | 4 | 10
>> 2020-07-07 00:00:00 | 5 | 15
>> 2020-07-08 00:00:00 | 6 | 21
>> (6 rows)
>> Best regards.
>> Samed YILDIRIM
>> 09.08.2020, 14:57, "ml@ft-c.de" <ml@ft-c.de>:
>> 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 <mailto:ml@ft-c.de> <mailto:ml@ft-c.de <mailto:ml@ft-c.de>>"
>> <ml@ft-c.de <mailto:ml@ft-c.de> <mailto:ml@ft-c.de <mailto: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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: recursive sql
@ 2020-08-09 14:22 Igor Andriychuk <2.andriychuk@gmail.com>
parent: ml@ft-c.de
1 sibling, 2 replies; 16+ messages in thread
From: Igor Andriychuk @ 2020-08-09 14:22 UTC (permalink / raw)
To: ml@ft-c.de; +Cc: pgsql-sql@lists.postgresql.org
I copied over your typo :-), should be:
with recursive r as(
select ts, c, row_id from rnk where rnk.row_id = 1
union
select rnk.ts, rnk.c*0.33 + r.c*0.67, rnk.row_id
from
r
join
rnk
on
r.row_id = rnk.row_id - 1
),
rnk as(
select *, row_number() over(order by ts) row_id from tt
)
select ts, c from r order by ts;
> On Aug 8, 2020, at 11:28 PM, ml@ft-c.de wrote:
>
> 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
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: recursive sql
@ 2020-08-09 14:54 Franz Timmer <ftimmer@ft-c.de>
parent: Igor Andriychuk <2.andriychuk@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: Franz Timmer @ 2020-08-09 14:54 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Hello,
It works - the result ist correct
Thank you,
Franz
On 8/9/20 4:22 PM, Igor Andriychuk wrote:
> I copied over your typo :-), should be:
>
> *with* *recursive* r *as*(
> *select*ts, c, row_id*from*rnk*where*rnk.row_id= 1
> *union*
> *select*rnk.ts, rnk.c*0.33+ r.c**0.67*, rnk.row_id
> *from*
> r
> *join*
> rnk
> *on*
> r.row_id= rnk.row_id- 1
> ),
> rnk*as*(
> *select**, *row_number*() *over*(*order**by*ts) row_id*from*tt
> )
> *select*ts, c*from*r*order**by*ts;
>
>> On Aug 8, 2020, at 11:28 PM, ml@ft-c.de <mailto:ml@ft-c.de> wrote:
>>
>> 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
>>
>>
>
--
Dipl.Volkswirt Franz Timmer
Ahrweilerstr. 24, 14197 Berlin
0160-2813574, ftimmer@ft-c.de
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: recursive sql
@ 2020-08-09 15:04 ml@ft-c.de
parent: Igor Andriychuk <2.andriychuk@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: ml@ft-c.de @ 2020-08-09 15:04 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Hello,
It works - the result ist correct
Thank you,
Franz
On 8/9/20 4:22 PM, Igor Andriychuk wrote:
> I copied over your typo :-), should be:
>
> *with* *recursive* r *as*(
> *select*ts, c, row_id*from*rnk*where*rnk.row_id= 1
> *union*
> *select*rnk.ts, rnk.c*0.33+ r.c**0.67*, rnk.row_id
> *from*
> r
> *join*
> rnk
> *on*
> r.row_id= rnk.row_id- 1
> ),
> rnk*as*(
> *select**, *row_number*() *over*(*order**by*ts) row_id*from*tt
> )
> *select*ts, c*from*r*order**by*ts;
>
>> On Aug 8, 2020, at 11:28 PM, ml@ft-c.de <mailto:ml@ft-c.de> wrote:
>>
>> 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
>>
>>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2020-08-09 15:04 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-19 12:27 Recursive SQL Andy Turk <andy_turk@hotmail.com>
2000-04-20 03:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-04-20 23:26 ` Michael S. Kelly <michaelk@axian.com>
2000-08-04 03:13 Recursive SQL database@gurubase.com
2003-09-05 17:41 recursive sql floyds@4peakstech.com
2003-09-06 05:05 ` Re: recursive sql sad <sad@bankir.ru>
2020-08-09 06:28 recursive sql ml@ft-c.de
2020-08-09 10:38 ` Re: recursive sql Samed YILDIRIM <samed@reddoc.net>
2020-08-09 11:57 ` Re: recursive sql ml@ft-c.de
2020-08-09 12:08 ` Re: recursive sql Samed YILDIRIM <samed@reddoc.net>
2020-08-09 13:25 ` Re: recursive sql ml@ft-c.de
2020-08-09 14:10 ` Re: recursive sql Igor Andriychuk <2.andriychuk@gmail.com>
2020-08-09 12:18 ` Re: recursive sql Igor Andriychuk <2.andriychuk@gmail.com>
2020-08-09 14:22 ` Re: recursive sql Igor Andriychuk <2.andriychuk@gmail.com>
2020-08-09 14:54 ` Re: recursive sql Franz Timmer <ftimmer@ft-c.de>
2020-08-09 15:04 ` Re: recursive sql ml@ft-c.de
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox