Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k4m2G-0006Ii-D6 for pgsql-sql@arkaria.postgresql.org; Sun, 09 Aug 2020 14:10:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k4m2E-0005pO-Im for pgsql-sql@arkaria.postgresql.org; Sun, 09 Aug 2020 14:10:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from <2.andriychuk@gmail.com>) id 1k4m2D-0005pG-Ro for pgsql-sql@lists.postgresql.org; Sun, 09 Aug 2020 14:10:50 +0000 Received: from mail-pg1-x52d.google.com ([2607:f8b0:4864:20::52d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from <2.andriychuk@gmail.com>) id 1k4m2A-0003lB-MW for pgsql-sql@lists.postgresql.org; Sun, 09 Aug 2020 14:10:48 +0000 Received: by mail-pg1-x52d.google.com with SMTP id 128so3492081pgd.5 for ; Sun, 09 Aug 2020 07:10:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=sn6geziXHEeWszNE9lF5tm+FxZ5HxfYfFSc3gx3/wKo=; b=iTMVJswFaY08iq/uugWMd5J8xK0YDc6/17Q9t/2tQnDXis8Wy/j53Nj+eFdMCL8LFu sadlKmvhbzyxcVgNM+DOPs9NG7NKZc4nqIaaFsJU1VzFKb6qrsZL16ksO/9/bSoM0bdz j0HoBb4muok/+ql9mT/MCZFCnTrMEGSp1bziKq1jO+i0tvM1FQa8T43UUO27QXWWsZ5e Cb2BNg+56GrhUaq9miEAL3iBvNHdB4NOXf7zSsYejmH+7+BMj4mrsIobSGOQBZYrFHQj iRRDxVuo2hhn9zmHlxJhaOsu/o1ZUvl2tZ4zuMoaaHKlJh40nZ2u8X7y/H1MGFQNHkUu MplA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=sn6geziXHEeWszNE9lF5tm+FxZ5HxfYfFSc3gx3/wKo=; b=iPGYBY4HXAavtmXjIJi3MxjrYCAKQVFjkF58/7dcutGGbALyl5OUvSmFPOFErSOLy2 7kWkfjasoy0MVUCpGrvc/2r4QcBlb8kxXq6eTwckaotB9YRVnbMmFEMzN0uwtZNjvvOC VQgnXHX34AmSZyd8zsXZW6n2+tn8u40IY4vDSDbg/yum+o3mTjNZv8zh5cGWSCE5BP+4 9vNdpOgCACxpthCsah5x94CAlZQcomQfN7cG4CThlKdiSDPtMHkdw7vWiFRM3GEBH5WV gl5jg5NcLATgMG3aXyA4XH7sstH9hXz5YxpXkHltdHKDKnnoafPz3W3H7ebZCtRaJfiP kmgQ== X-Gm-Message-State: AOAM531g2/Gg+QV+Q3N0pNVTa17JKY3lvN86rLONFKclwlo+8LYcwpsN 2JtUYT0/WyY4Tx9khWUGbAw= X-Google-Smtp-Source: ABdhPJwiCxh81UVDNb+aIaBcKgcXyQBCRml5TJ6AMrKLgBEFBx2FP/M2XTtOP2T02RfFMmCzuqNvkg== X-Received: by 2002:a63:a53:: with SMTP id z19mr918613pgk.67.1596982244831; Sun, 09 Aug 2020 07:10:44 -0700 (PDT) Received: from [192.168.1.19] (198-27-163-68.fiber.dynamic.sonic.net. [198.27.163.68]) by smtp.gmail.com with ESMTPSA id b8sm19492658pfr.57.2020.08.09.07.10.43 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sun, 09 Aug 2020 07:10:44 -0700 (PDT) From: Igor Andriychuk <2.andriychuk@gmail.com> Message-Id: <2F5CAE4D-76DA-44D8-AFCA-CABF00DA9422@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_FC248F49-695B-4A0D-9E6E-96CD7C83B78A" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.1\)) Subject: Re: recursive sql Date: Sun, 9 Aug 2020 07:10:43 -0700 In-Reply-To: <9578e177-9245-286b-3c7a-8e661f75789a@ft-c.de> Cc: pgsql-sql@lists.postgresql.org To: ml@ft-c.de References: <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> X-Mailer: Apple Mail (2.3608.120.23.2.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_FC248F49-695B-4A0D-9E6E-96CD7C83B78A Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Oh, yes, in this case you need a recursion. This is something that came = on my mind in short observation: with recursive r as( =20 select ts, c, row_id from rnk where rnk.row_id =3D 1 union=20 select rnk.ts, rnk.c*0.33 + r.c*0.76, rnk.row_id from r join rnk on r.row_id =3D 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: >=20 > Hello, >=20 > sorry for my short explanation. It was not enough to understand the my = task/target. >=20 > These are the basic computation for an exponential moving average = (ema) > an statistic indicator for trading data. >=20 > 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. >=20 > For the current day (period) the formula is >=20 > EMA =3D Close(t) * SF + ( (1-SF) * EMA(t-1) ) >=20 > where Smoothing Factor SF =3D 2 / (n+1) >=20 > The best way is, to explain it with an example: > day close SF close 1-SF EMA(t-1) =3D part_of_result > 1 105,5 > 2 104 0.33 * 104 + 0.76 * 105,5 =3D 105.005 > 3 103.5 0.33 * 103 + 0.76 * 105.005 =3D 104.508 > 4 102 0.33 * 102 + 0.76 * 104.508 =3D 103.680 > 5 101 0.33 * 101 + 0.76 * 103.680 =3D 102.795 > 6 100 0.33 * 100 + 0.76 * 102.795 =3D 101.872 >=20 > 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 >=20 > I need the close value of the current day and > the the close value of the previous day. But before, it must be = calculated. >=20 > I believe, the best way is, to do it with > "with recursive" >=20 > Franz >=20 >=20 > 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=3D# 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=3D# 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" : >> Hallo, >> with the window function lag there is a shift of one or more rows. = Every >> row connects to the previous row :=3D 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=3D# 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=3D# 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 = >" >> >>: >> 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 >> -- as c2 >> from tt -- <- I need tt on this place >> thank you for help >> Franz --Apple-Mail=_FC248F49-695B-4A0D-9E6E-96CD7C83B78A Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii 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 =3D 1
union 
select rnk.ts, rnk.c*0.33= + r.c*0.76, rnk.row_id
from
r
join
rnk
on
r.row_id =3D 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 =3D Close(t) * SF  + ( (1-SF) * EMA(t-1) )

where Smoothing Factor SF =3D 2 = / (n+1)

The best way = is, to explain it with an example:
day close    SF    close =  1-SF  EMA(t-1)  =3D part_of_result
1   105,5
2   104 =     0.33 * 104 + 0.76 * 105,5    =3D = 105.005
3 =   103.5   0.33 * 103 + 0.76 * 105.005  =3D = 104.508
4 =   102     0.33 * 102 + 0.76 * 104.508 =  =3D 103.680
5   101     0.33 * 101 + 0.76 * = 103.680  =3D 102.795
6   100     0.33 * 100 + 0.76 * = 102.795  =3D 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=3D# 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=3D# 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 :=3D 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= =3D# select *, lag(c) over (order by ts) as c2 from tt;
         ts | c = | c2
         ---------= ------------+---+----
         2019-12-3= 1 00:00:00 | 1 |
         2020-01-0= 1 00:00:00 | 2 | 1
         2020-07-0= 2 00:00:00 | 3 | 2
         2020-07-0= 6 00:00:00 | 4 | 3
         2020-07-0= 7 00:00:00 | 5 | 4
         2020-07-0= 8 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= =3D# 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-3= 1 00:00:00 | 1 |
         2020-01-0= 1 00:00:00 | 2 | 1
         2020-07-0= 2 00:00:00 | 3 | 2
         2020-07-0= 6 00:00:00 | 4 | 3
         2020-07-0= 7 00:00:00 | 5 | 4
         2020-07-0= 8 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.202= 0, 09:29, "ml@ft-c.de <mailto:ml@ft-c.de>"
       <ml@ft-c.de <mailto:ml@ft-c.de>>:
          &nb= sp;  Hello,
          &nb= sp;  the table
          &nb= sp;  create table tt (
          &nb= sp;      ts timestamp,
          &nb= sp;      c numeric) ;
          &nb= sp;  insert into tt values
          &nb= sp;     ('2019-12-31',1), ('2020-01-01',2),
          &nb= sp;     ('2020-07-02',3), ('2020-07-06',4),
          &nb= sp;     ('2020-07-07',5), ('2020-07-08',6);
          &nb= sp;  My question: It is possible to get an
          &nb= sp;      additional column (named c2)
          &nb= sp;      with
          &nb= sp;      ( c from current row ) + ( c2 = from the previous row )
       as c2
          &nb= sp;  the result:
          &nb= sp;  ts c c2
          &nb= sp;  .. 1 1 -- or null in the first row
          &nb= sp;  .. 2 3
          &nb= sp;  .. 3 6
          &nb= sp;  .. 4 10
          &nb= sp;  ...
          &nb= sp;  with recursive ema as ()
          &nb= sp;  select ts, c,
          &nb= sp;      -- many many computed_rows
          &nb= sp;      -- <code> as c2
          &nb= sp;  from tt -- <- I need tt on this place
          &nb= sp;  thank you for help
          &nb= sp;  Franz

= --Apple-Mail=_FC248F49-695B-4A0D-9E6E-96CD7C83B78A--