Re: Row pattern recognition
Tatsuo Ishii <ishii@sraoss.co.jp>
From: Tatsuo Ishii <ishii@sraoss.co.jp>
To: jchampion@timescale.com, er@xs4all.nl, vik@postgresfriends.org
Cc: pgsql-hackers@postgresql.org
Date: 2023-09-12T08:44:57Z
Lists: pgsql-hackers
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Add temporal FOREIGN KEY contraints
- 89f908a6d0ac 18.0 cited
-
Remove obsolete executor cleanup code
- d060e921ea5a 17.0 cited
Attachments
- v6-fix.patch (text/x-patch) patch v6
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