Re: Row pattern recognition

> Okay, I see the problem now, and why you need the rpr() function.
> 
> You are doing this as something that happens over a window frame, but
> it is actually something that *reduces* the window frame.  The pattern
> matching needs to be done when the frame is calculated and not when
> any particular function is applied over it.

Yes. (I think the standard calls the window frame as "full window
frame" in context of RPR to make a contrast with the subset of the
frame rows restricted by RPR. The paper I refered to as [2] claims
that the latter window frame is called "reduced window frame" in the
standard but I wasn't able to find the term in the standard.)

I wanted to demonstate that pattern matching logic is basically
correct in the PoC patch. Now what I need to do is, move the row
pattern matching logic to somewhere inside nodeWindowAgg so that
"restricted window frame" can be applied to all window functions and
window aggregates. Currently I am looking into update_frameheadpos()
and update_frametailpos() which calculate the frame head and tail
against current row. What do you think?

> This query (with all the defaults made explicit):
> 
> SELECT s.company, s.tdate, s.price,
>        FIRST_VALUE(s.tdate) OVER w,
>        LAST_VALUE(s.tdate) OVER w,
>        lowest OVER w
> FROM stock AS s
> WINDOW w AS (
>   PARTITION BY s.company
>   ORDER BY s.tdate
>   ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
>   EXCLUDE NO OTHERS
>   MEASURES
>     LAST(DOWN) AS lowest
>   AFTER MATCH SKIP PAST LAST ROW
>   INITIAL PATTERN (START DOWN+ UP+)
>   DEFINE
>     START AS TRUE,
>     UP AS price > PREV(price),
>     DOWN AS price < PREV(price)
> );
> 
> Should produce this result:

[snip]

Thanks for the examples. I agree with the expected query results.

>>>> o SUBSET is not supported
>>>
>>> Is this because you haven't done it yet, or because you ran into
>>> problems trying to do it?
>> Because it seems SUBSET is not useful without MEASURES support. Thus
>> my plan is, firstly implement MEASURES, then SUBSET. What do you
>> think?
> 
> 
> SUBSET elements can be used in DEFINE clauses, but I do not think this
> is important compared to other features.

Ok.

>>> I have not looked at the patch yet, but is the reason for doing R020
>>> before R010 because you haven't done the MEASURES clause yet?
>> One of the reasons is, implementing MATCH_RECOGNIZE (R010) looked
>> harder for me because modifying main SELECT clause could be a hard
>> work. Another reason is, I had no idea how to implement PREV/NEXT in
>> other than in WINDOW clause. Other people might feel differently
>> though.
> 
> 
> I think we could do this with a single tuplesort if we use
> backtracking (which might be really slow for some patterns).  I have
> not looked into it in any detail.
> 
> We would need to be able to remove tuples from the end (even if only
> logically), and be able to update tuples inside the store.  Both of
> those needs come from backtracking and possibly changing the
> classifier.
> 
> Without backtracking, I don't see how we could do it without have a
> separate tuplestore for every current possible match.

Maybe an insane idea but what about rewriting MATCH_RECOGNIZE clause
into Window clause with RPR?

> I looked at your v2 patches a little bit and the only comment that I
> currently have on the code is you spelled PERMUTE as
> PREMUTE. Everything else is hopefully explained above.

Thanks. Will fix.

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