Re: recursive sql - Mailing list pgsql-sql
From | Igor Andriychuk |
---|---|
Subject | Re: recursive sql |
Date | |
Msg-id | 2F5CAE4D-76DA-44D8-AFCA-CABF00DA9422@gmail.com Whole thread Raw |
In response to | Re: recursive sql (ml@ft-c.de) |
List | pgsql-sql |
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>"
<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