[jira] [Commented] (CALCITE-1911) Support within clause in MATCH_RECOGNIZE

[Julian Hyde (JIRA)]

    [ 
https://issues.apache.org/jira/browse/CALCITE-1911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16108279#comment-16108279
 ] 

Julian Hyde commented on CALCITE-1911:
--------------------------------------

Here's a possible solution. If people want to use WITHIN, they have to define 
an ordering. So we add an optional {{ORDER BY}} sub-clause to 
{{MATCH_RECOGNIZE}}:

{code}
SELECT T.Ac2, T.Bc2, T.Cc2 
    FROM S
    MATCH_RECOGNIZE(
        ORDER BY rowtime
        MEASURES A.c2 as Ac2, B.c2 as Bc2, C.c2 as Cc2
        PATTERN (A (B+ | C)) WITHIN INTERVAL '3' SECONDS
        DEFINE 
            A as A.c1=10 or A.c1=25, 
            B as B.c1=20 or B.c1=15 or B.c1=25, 
            C as C.c1=15
    ) as T
{code}

This is analogous to how you have to specify {{ORDER BY}} inside an {{OVER}} 
clause if you want to use use {{RANGE}}, e.g.:

{code}
SELECT ticker, COUNT(*) OVER (
    PARTITION BY ticker
    ORDER BY rowtime
    RANGE INTERVAL '10' SECONDS PRECEDING)
FROM Trades
{code}

> Support within clause in MATCH_RECOGNIZE
> ----------------------------------------
>
>                 Key: CALCITE-1911
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1911
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Dian Fu
>            Assignee: Julian Hyde
>              Labels: match
>
> Window is an important feature for pattern detection, it defines the time 
> duration for the events to match a pattern. Here is an example from 
> [doc|https://docs.oracle.com/middleware/1213/eventprocessing/cql-reference/GUID-34D4968E-C55A-4BC7-B1CE-C84B202217BD.htm#CQLLR2119]:
> {code}
> SELECT T.Ac2, T.Bc2, T.Cc2 
>     FROM S
>     MATCH_RECOGNIZE(
>         MEASURES A.c2 as Ac2, B.c2 as Bc2, C.c2 as Cc2
>         PATTERN (A (B+ | C)) within 3000 milliseconds 
>         DEFINE 
>             A as A.c1=10 or A.c1=25, 
>             B as B.c1=20 or B.c1=15 or B.c1=25, 
>             C as C.c1=15
>     ) as T
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)