Re: Row pattern recognition

From: Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp>
To: jchampion(at)timescale(dot)com, er(at)xs4all(dot)nl, vik(at)postgresfriends(dot)org
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Row pattern recognition
Date: 2023-09-12 08:44:57
Message-ID: 20230912.174457.1842965393591953201.t-ishii@sranhm.sra.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Regarding v6 patch:

> SELECT company, tdate, price,
> first_value(price) OVER w,
> last_value(price) OVER w,
> max(price) OVER w,
> min(price) OVER w,
> sum(price) OVER w,
> avg(price) OVER w,
> count(price) OVER w
> FROM stock
> WINDOW w AS (
> PARTITION BY company
> ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
> AFTER MATCH SKIP PAST LAST ROW
> INITIAL
> PATTERN (START UP+ DOWN+)
> DEFINE
> START AS TRUE,
> UP AS price > PREV(price),
> DOWN AS price < PREV(price)
> );
> company | tdate | price | first_value | last_value | max | min | sum | avg | count
> ----------+------------+-------+-------------+------------+------+-----+------+-----------------------+-------
> company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4
> company1 | 07-02-2023 | 200 | | | | | | |
> company1 | 07-03-2023 | 150 | | | | | | |
> company1 | 07-04-2023 | 140 | | | | | | |
> company1 | 07-05-2023 | 150 | | | | | | |
> company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4
> company1 | 07-07-2023 | 110 | | | | | | |
> company1 | 07-08-2023 | 130 | | | | | | |
> company1 | 07-09-2023 | 120 | | | | | | |
> company1 | 07-10-2023 | 130 | | | | | | |
> company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4
> company2 | 07-02-2023 | 2000 | | | | | | |
> company2 | 07-03-2023 | 1500 | | | | | | |
> company2 | 07-04-2023 | 1400 | | | | | | |
> company2 | 07-05-2023 | 1500 | | | | | | |
> company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4
> company2 | 07-07-2023 | 1100 | | | | | | |
> company2 | 07-08-2023 | 1300 | | | | | | |
> company2 | 07-09-2023 | 1200 | | | | | | |
> company2 | 07-10-2023 | 1300 | | | | | | |
> (20 rows)

count column for unmatched rows should have been 0, rather than
NULL. i.e.

company | tdate | price | first_value | last_value | max | min | sum | avg | count
----------+------------+-------+-------------+------------+------+-----+------+-----------------------+-------
company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4
company1 | 07-02-2023 | 200 | | | | | | |
company1 | 07-03-2023 | 150 | | | | | | |
company1 | 07-04-2023 | 140 | | | | | | |
company1 | 07-05-2023 | 150 | | | | | | | 0
company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4
company1 | 07-07-2023 | 110 | | | | | | |
company1 | 07-08-2023 | 130 | | | | | | |
company1 | 07-09-2023 | 120 | | | | | | |
company1 | 07-10-2023 | 130 | | | | | | | 0
company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4
company2 | 07-02-2023 | 2000 | | | | | | |
company2 | 07-03-2023 | 1500 | | | | | | |
company2 | 07-04-2023 | 1400 | | | | | | |
company2 | 07-05-2023 | 1500 | | | | | | | 0
company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4
company2 | 07-07-2023 | 1100 | | | | | | |
company2 | 07-08-2023 | 1300 | | | | | | |
company2 | 07-09-2023 | 1200 | | | | | | |
company2 | 07-10-2023 | 1300 | | | | | | | 0
(20 rows)

Attached is the fix against v6 patch. I will include this in upcoming v7 patch.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Attachment Content-Type Size
v6-fix.patch text/x-patch 4.1 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message jian he 2023-09-12 08:45:29 Re: Cleaning up array_in()
Previous Message Hayato Kuroda (Fujitsu) 2023-09-12 08:10:13 RE: [PoC] pg_upgrade: allow to upgrade publisher node