Re: Row pattern recognition - Mailing list pgsql-hackers
| From | Tatsuo Ishii |
|---|---|
| Subject | Re: Row pattern recognition |
| Date | |
| Msg-id | 20240426.150932.865564699251799269.t-ishii@sranhm.sra.co.jp Whole thread Raw |
| In response to | Re: Row pattern recognition (Jacob Champion <jacob.champion@enterprisedb.com>) |
| Responses |
Re: Row pattern recognition
|
| List | pgsql-hackers |
> On Tue, Apr 23, 2024 at 8:13 PM Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
>> SELECT v.a, count(*) OVER w
>> FROM (VALUES ('A'),('B'),('B'),('C')) AS v (a)
>> WINDOW w AS (
>> ORDER BY v.a
>> ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
>> PATTERN (B+)
>> DEFINE B AS a = 'B'
>> )
>> a | count
>> ---+-------
>> A | 0
>> B | 2
>> B |
>> C | 0
>> (4 rows)
>>
>> Here row 3 is skipped because the pattern B matches row 2 and 3. In
>> this case I think cont(*) should return 0 rathern than NULL for row 3.
>
> I think returning zero would match Vik's explanation upthread [1],
> yes. Unfortunately I don't have a spec handy to double-check for
> myself right now.
Ok. I believe you and Vik are correct.
I am modifying the patch in this direction.
Attached is the regression diff after modifying the patch.
Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
diff -U3 /usr/local/src/pgsql/current/postgresql/src/test/regress/expected/rpr.out
/usr/local/src/pgsql/current/postgresql/src/test/regress/results/rpr.out
--- /usr/local/src/pgsql/current/postgresql/src/test/regress/expected/rpr.out 2024-04-24 11:30:27.710523139 +0900
+++ /usr/local/src/pgsql/current/postgresql/src/test/regress/results/rpr.out 2024-04-26 14:39:03.543759205 +0900
@@ -181,8 +181,8 @@
company1 | 07-01-2023 | 100 | 0
company1 | 07-02-2023 | 200 | 0
company1 | 07-03-2023 | 150 | 3
- company1 | 07-04-2023 | 140 |
- company1 | 07-05-2023 | 150 |
+ company1 | 07-04-2023 | 140 | 0
+ company1 | 07-05-2023 | 150 | 0
company1 | 07-06-2023 | 90 | 0
company1 | 07-07-2023 | 110 | 0
company1 | 07-08-2023 | 130 | 0
@@ -556,24 +556,24 @@
company | tdate | price | first_value | last_value | count
----------+------------+-------+-------------+------------+-------
company1 | 07-01-2023 | 100 | 07-01-2023 | 07-03-2023 | 3
- company1 | 07-02-2023 | 200 | | |
- company1 | 07-03-2023 | 150 | | |
+ company1 | 07-02-2023 | 200 | | | 0
+ company1 | 07-03-2023 | 150 | | | 0
company1 | 07-04-2023 | 140 | 07-04-2023 | 07-06-2023 | 3
- company1 | 07-05-2023 | 150 | | |
- company1 | 07-06-2023 | 90 | | |
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
company1 | 07-07-2023 | 110 | 07-07-2023 | 07-09-2023 | 3
- company1 | 07-08-2023 | 130 | | |
- company1 | 07-09-2023 | 120 | | |
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
company1 | 07-10-2023 | 130 | | | 0
company2 | 07-01-2023 | 50 | 07-01-2023 | 07-03-2023 | 3
- company2 | 07-02-2023 | 2000 | | |
- company2 | 07-03-2023 | 1500 | | |
+ company2 | 07-02-2023 | 2000 | | | 0
+ company2 | 07-03-2023 | 1500 | | | 0
company2 | 07-04-2023 | 1400 | 07-04-2023 | 07-06-2023 | 3
- company2 | 07-05-2023 | 1500 | | |
- company2 | 07-06-2023 | 60 | | |
+ company2 | 07-05-2023 | 1500 | | | 0
+ company2 | 07-06-2023 | 60 | | | 0
company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-09-2023 | 3
- company2 | 07-08-2023 | 1300 | | |
- company2 | 07-09-2023 | 1200 | | |
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
company2 | 07-10-2023 | 1300 | | | 0
(20 rows)
@@ -604,24 +604,24 @@
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-02-2023 | 200 | | | | | | | 0
+ company1 | 07-03-2023 | 150 | | | | | | | 0
+ company1 | 07-04-2023 | 140 | | | | | | | 0
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-07-2023 | 110 | | | | | | | 0
+ company1 | 07-08-2023 | 130 | | | | | | | 0
+ company1 | 07-09-2023 | 120 | | | | | | | 0
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-02-2023 | 2000 | | | | | | | 0
+ company2 | 07-03-2023 | 1500 | | | | | | | 0
+ company2 | 07-04-2023 | 1400 | | | | | | | 0
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-07-2023 | 1100 | | | | | | | 0
+ company2 | 07-08-2023 | 1300 | | | | | | | 0
+ company2 | 07-09-2023 | 1200 | | | | | | | 0
company2 | 07-10-2023 | 1300 | | | | | | | 0
(20 rows)
@@ -732,16 +732,16 @@
tdate | price | first_value | count
------------+-------+-------------+-------
07-01-2023 | 100 | 07-01-2023 | 4
- 07-02-2023 | 200 | |
- 07-03-2023 | 150 | |
- 07-04-2023 | 140 | |
+ 07-02-2023 | 200 | | 0
+ 07-03-2023 | 150 | | 0
+ 07-04-2023 | 140 | | 0
07-05-2023 | 150 | | 0
07-06-2023 | 90 | | 0
07-07-2023 | 110 | | 0
07-01-2023 | 50 | 07-01-2023 | 4
- 07-02-2023 | 2000 | |
- 07-03-2023 | 1500 | |
- 07-04-2023 | 1400 | |
+ 07-02-2023 | 2000 | | 0
+ 07-03-2023 | 1500 | | 0
+ 07-04-2023 | 1400 | | 0
07-05-2023 | 1500 | | 0
07-06-2023 | 60 | | 0
07-07-2023 | 1100 | | 0
pgsql-hackers by date: