Re: Row pattern recognition

>> What about leaving this (reevaluation) for now? Because:
>>
>> 1) we don't have CLASSIFIER
>> 2) we don't allow to give CLASSIFIER to PREV as its arggument
>>
>> so I think we don't need to worry about this for now.
> 
> Sure. I'm all for deferring features to make it easier to iterate; I
> just want to make sure the architecture doesn't hit a dead end. Or at
> least, not without being aware of it.

Ok, let's defer this issue. Currently the patch already exceeds 3k
lines. I am afraid too big patch cannot be reviewed by anyone, which
means it will never be committed.

> Also: is CLASSIFIER the only way to run into this issue?

Good question. I would like to know.

>> What if we don't follow the standard, instead we follow POSIX EREs?  I
>> think this is better for users unless RPR's REs has significant merit
>> for users.
> 
> Piggybacking off of what Vik wrote upthread, I think we would not be
> doing ourselves any favors by introducing a non-compliant
> implementation that performs worse than a traditional NFA. Those would
> be some awful bug reports.

What I am not sure about is, you and Vik mentioned that the
traditional NFA is superior that POSIX NFA in terms of performance.
But how "lexicographic ordering" is related to performance?

>> I am not sure if we need to worry about this because of the reason I
>> mentioned above.
> 
> Even if we adopted POSIX NFA semantics, we'd still have to implement
> our own parser for the PATTERN part of the query. I don't think
> there's a good way for us to reuse the parser in src/backend/regex.

Ok.

>> > Does that seem like a workable approach? (Worst-case, my code is just
>> > horrible, and we throw it in the trash.)
>>
>> Yes, it seems workable. I think for the first cut of RPR needs at
>> least the +quantifier with reasonable performance. The current naive
>> implementation seems to have issue because of exhaustive search.
> 
> +1

BTW, attched is the v6 patch. The differences from v5 include:

- Now aggregates can be used with RPR. Below is an example from the
  regression test cases, which is added by v6 patch.

- Fix assersion error pointed out by Erik.

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)

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