agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Igor Andriychuk <2.andriychuk@gmail.com>
To: ml@ft-c.de
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: recursive sql
Date: Sun, 9 Aug 2020 07:10:43 -0700
Message-ID: <2F5CAE4D-76DA-44D8-AFCA-CABF00DA9422@gmail.com> (raw)
In-Reply-To: <9578e177-9245-286b-3c7a-8e661f75789a@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>
<187651596974558@mail.yandex.com.tr>
<9578e177-9245-286b-3c7a-8e661f75789a@ft-c.de>
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
view thread (16+ messages) latest in thread
Message-ID: <2F5CAE4D-76DA-44D8-AFCA-CABF00DA9422@gmail.com>
Permalink: ../2F5CAE4D-76DA-44D8-AFCA-CABF00DA9422@gmail.com/
Also on: postgresql.org/message-id/2F5CAE4D-76DA-44D8-AFCA-CABF00DA9422@gmail.com
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: 2.andriychuk@gmail.com, ml@ft-c.de, pgsql-sql@lists.postgresql.org
Subject: Re: recursive sql
In-Reply-To: <2F5CAE4D-76DA-44D8-AFCA-CABF00DA9422@gmail.com>
* 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