[jira] [Commented] (CALCITE-1911) Support WITHIN clause in MATCH_RECOGNIZE
[
https://issues.apache.org/jira/browse/CALCITE-1911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16140589#comment-16140589
]
Julian Hyde commented on CALCITE-1911:
--
Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/dfe251d7; thanks
for the PR, [~dian.fu]! (You'll notice that I simplified the logic to convert
an interval RexLiteral to a SqlLiteral, and also changed the order of the
rowType parameter in the Match constructor.)
> 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)
[jira] [Commented] (CALCITE-1911) Support WITHIN clause in MATCH_RECOGNIZE
[
https://issues.apache.org/jira/browse/CALCITE-1911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16137885#comment-16137885
]
Julian Hyde commented on CALCITE-1911:
--
Reviewing/testing now.
> 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)
[jira] [Commented] (CALCITE-1911) Support WITHIN clause in MATCH_RECOGNIZE
[
https://issues.apache.org/jira/browse/CALCITE-1911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16137738#comment-16137738
]
Dian Fu commented on CALCITE-1911:
--
Hi [~julianhyde], I have addressed your comments in the latest PR and could you
help to take a look? Very appreciated.
> 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)
[jira] [Commented] (CALCITE-1911) Support WITHIN clause in MATCH_RECOGNIZE
[
https://issues.apache.org/jira/browse/CALCITE-1911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16125149#comment-16125149
]
Dian Fu commented on CALCITE-1911:
--
Make sense. Have updated the tests for INTERVAL.
> 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)
[jira] [Commented] (CALCITE-1911) Support WITHIN clause in MATCH_RECOGNIZE
[
https://issues.apache.org/jira/browse/CALCITE-1911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16123502#comment-16123502
]
Julian Hyde commented on CALCITE-1911:
--
You've addressed almost everything. I was pleased to see negative tests for
ORDER BY.
Can you make the unit tests for intervals much smaller? The only difference
between testMatchRecognizeWithin6 and testMatchRecognizeWithin7 is the kind of
interval. That's the purpose of those tests, after all. You know that, I know
that, but whoever maintains this code in the future won't be able to see the
wood for the trees. Don't write a 50 line test when a 1 line test will do. As I
said earlier, you can test using a 1-line VALUES statement.
> 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)
[jira] [Commented] (CALCITE-1911) Support WITHIN clause in MATCH_RECOGNIZE
[
https://issues.apache.org/jira/browse/CALCITE-1911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16123278#comment-16123278
]
Dian Fu commented on CALCITE-1911:
--
Have updated the PR according to review comments.
Have also added the following method in {{LogicalMatch}}. Although this is not
related to this JIRA, but the change is very small and so just fix it here.
{quote}
@Override public RelNode accept(RelShuttle shuttle) {
return shuttle.visit(this);
}
{quote}
> 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)
[jira] [Commented] (CALCITE-1911) Support WITHIN clause in MATCH_RECOGNIZE
[
https://issues.apache.org/jira/browse/CALCITE-1911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16119167#comment-16119167
]
Julian Hyde commented on CALCITE-1911:
--
I think it's just a matter of generating the interval string when you create a
SqlIntervalLiteral. I think you can write a for loop from the start time unit
to the end time unit, and putting TimeUnit.separator between them.
Can you add a unit test to RelToSqlConverterTest that checks that RexLiteral to
SqlIntervalLiteral conversion works for "VALUES INTERVAL '1-2' YEAR TO MONTH"
and a few other kinds of intervals.
> 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)
[jira] [Commented] (CALCITE-1911) Support WITHIN clause in MATCH_RECOGNIZE
[
https://issues.apache.org/jira/browse/CALCITE-1911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16118240#comment-16118240
]
Dian Fu commented on CALCITE-1911:
--
[~julianhyde], [~ransom] Thanks a lot for your review.
{quote}
I am surprised that you needed to add so much code for intervals in
SqlParserUtil, and indeed in reference.md. Were you not able to re-use existing
interval literal support?
{quote}
I have tried to reuse existing code, but didn't found any existing code to
transform from {{RexLiteral}} of type {{INTERVAL_YEAR_MONTH}} or
{{INTERVAL_DAY_TIME}} to {{SqlIntervalLiteral}}. I will investigate how this is
done for the existing literal support.
> 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)
[jira] [Commented] (CALCITE-1911) Support WITHIN clause in MATCH_RECOGNIZE
[
https://issues.apache.org/jira/browse/CALCITE-1911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16118070#comment-16118070
]
Zhiqiang He commented on CALCITE-1911:
--
SqlValidatorImpl.java shoud add check orderBy clause when within is used.
we need a event time or process time to define the range of MATCH_RECOGNIZE. l
just like a stream window.
> 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)
[jira] [Commented] (CALCITE-1911) Support within clause in MATCH_RECOGNIZE
[
https://issues.apache.org/jira/browse/CALCITE-1911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16117548#comment-16117548
]
Julian Hyde commented on CALCITE-1911:
--
A few review comments:
* Add comments in Match etc. that interval can be null. Not-null is usually
assumed.
* Interval 0 is ok, right? So the message should say "Interval must be
non-negative".
* I don't think you need SqlIntervalQualifier.CONV.
org.apache.calcite.avatica.util.TimeUnit.multiplier is probably sufficient.
* In Match.explainTerms, if should ignore null interval, not print "interval:
null".
* I am surprised that you needed to add so much code for intervals in
SqlParserUtil, and indeed in reference.md. Were you not able to re-use existing
interval literal support?
* As I said earlier, we need a way to specify ordering. Maybe there is a
rowtime column on streams. But this clause also needs to work on tables, which
definitely don't have a rowtime column.
> 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)
[jira] [Commented] (CALCITE-1911) Support within clause in MATCH_RECOGNIZE
[
https://issues.apache.org/jira/browse/CALCITE-1911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16116608#comment-16116608
]
Dian Fu commented on CALCITE-1911:
--
[~julianhyde] [~ransom], Have create a PR. Could you help to review? Thanks a
lot.
https://github.com/apache/calcite/pull/509
> 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)
[jira] [Commented] (CALCITE-1911) Support within clause in MATCH_RECOGNIZE
[
https://issues.apache.org/jira/browse/CALCITE-1911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16108289#comment-16108289
]
Dian Fu commented on CALCITE-1911:
--
Right, I think the {{rowtime}} column can be seen as the timestamp column.
> 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)
[jira] [Commented] (CALCITE-1911) Support within clause in MATCH_RECOGNIZE
[
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)
[jira] [Commented] (CALCITE-1911) Support within clause in MATCH_RECOGNIZE
[
https://issues.apache.org/jira/browse/CALCITE-1911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16108257#comment-16108257
]
Dian Fu commented on CALCITE-1911:
--
Yes, this is not in Oracle 12 and also not in the SQL standard, but I think
this feature is very useful as window is very important for pattern detection.
{quote}
It assumes that there is a system column that defines the timestamp. Only
possible in streaming queries, and even then, probably not well defined.
{quote}
Yes, that's right. For cases where there is no timestamp column, users don't
need to define this clause and so there is no side effect.
{quote}
We should use the SQL standard notation for intervals: WITHIN INTERVAL '3'
SECONDS rather than within 3000 milliseconds
{quote}
Make sense to me.
> 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)
[jira] [Commented] (CALCITE-1911) Support within clause in MATCH_RECOGNIZE
[
https://issues.apache.org/jira/browse/CALCITE-1911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16107993#comment-16107993
]
Julian Hyde commented on CALCITE-1911:
--
To be clear, this is not in Oracle 12, and it is not in the SQL standard. This
is in Oracle fusion middleware's CQL. So, I am not yet convinced we should add
this in the current form.
A couple of immediate problems:
* It assumes that there is a system column that defines the timestamp. Only
possible in streaming queries, and even then, probably not well defined.
* We should use the SQL standard notation for intervals: {{WITHIN INTERVAL '3'
SECONDS}} rather than {{within 3000 milliseconds}}
Cc [~zhiqiang,he]
> 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)
