recursive sql

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
ml
Reply | Threaded
Open this post in threaded view
|

recursive sql

ml
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


Reply | Threaded
Open this post in threaded view
|

AW: recursive sql

Stöcker, Martin

Hi Franz


I think you don't need to use recursivy but window function will do.


Mit freundlichen Grüßen

Martin Stöcker 
-----------------------------------------
ETL Datenservice GmbH
Widdersdorfer Str. 415 | D-50933 Köln
Telefon: +49(0)2219544010
Fax: +49
(0)2219544015
Email: [hidden email]

ETL Datenservice GmbH
Widdersdorfer Str. 415 · 50933 Köln
Geschäftsführer: Dr. Dirk Goldner, ppa Melanie Lillich
Amtsgericht Köln · HRB 75439 · USt.-Id: DE 122 805 685
www.etl-datenservice.de
Email: [hidden email]

Die STB Datenservice ist jetzt ETL Datenservice.
Sie erreichen uns nun unter dem Zusatz @etl-datenservice.de

Von: [hidden email] <[hidden email]>
Gesendet: Sonntag, 9. August 2020 08:28:44
An: [hidden email]
Betreff: recursive sql
 
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


Reply | Threaded
Open this post in threaded view
|

Re: recursive sql

Samed YILDIRIM
In reply to this post by ml
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)
 
 
Best regards.
Samed YILDIRIM
 
 
 
09.08.2020, 09:29, "[hidden email]" <[hidden email]>:

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

 

ml
Reply | Threaded
Open this post in threaded view
|

Re: recursive sql

ml
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, "[hidden email]" <[hidden email]>:
>
>     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
>


Reply | Threaded
Open this post in threaded view
|

Re: recursive sql

Samed YILDIRIM
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, "[hidden email]" <[hidden email]>:

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, "[hidden email]" <[hidden email]>:
 
     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
 


 

Reply | Threaded
Open this post in threaded view
|

Re: recursive sql

Igor Andriychuk
In reply to this post by Samed YILDIRIM
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 <[hidden email]> 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)
 
 
Best regards.
Samed YILDIRIM
 
 
 
09.08.2020, 09:29, "[hidden email]" <[hidden email]>:

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

 


ml
Reply | Threaded
Open this post in threaded view
|

Re: recursive sql

ml
In reply to this post by Samed YILDIRIM
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, "[hidden email]" <[hidden email]>:
>
>     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, "[hidden email] <mailto:[hidden email]>"
>         <[hidden email] <mailto:[hidden email]>>:
>
>               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
>
>


Reply | Threaded
Open this post in threaded view
|

Re: recursive sql

Igor Andriychuk
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, [hidden email] 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, "[hidden email]" <[hidden email]>:
   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, "[hidden email] <[hidden email]>"
       <[hidden email] <[hidden email]>>:
             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

Reply | Threaded
Open this post in threaded view
|

Re: recursive sql

Igor Andriychuk
In reply to this post by ml
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, [hidden email] 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



Reply | Threaded
Open this post in threaded view
|

Re: recursive sql

Franz Timmer
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, [hidden email] <mailto:[hidden email]> 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, [hidden email]


ml
Reply | Threaded
Open this post in threaded view
|

Re: recursive sql

ml
In reply to this post by Igor Andriychuk
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, [hidden email] <mailto:[hidden email]> 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
>>
>>
>