[
https://issues.apache.org/jira/browse/CALCITE-1570?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15871276#comment-15871276
]
Julian Hyde commented on CALCITE-1570:
--------------------------------------
Rather than slicing horizontally (into tasks each in a different area of the
code), let's slice vertically and do a spike for the stupidest, simplest
example. I propose this example from
[http://sqltech.cl/doc/oas11gR1/doc.1111/e12048/pattern_recog.htm|here]:
{code}
select its.itemId
from tkrfid_ItemTempStream MATCH_RECOGNIZE (
PARTITION BY itemId
MEASURES A.itemId as itemId
PATTERN (A B* C)
DEFINE
A AS (A.temp >= 25),
B AS ((B.temp >= 25) and (to_timestamp(B.element_time) -
to_timestamp(A.element_time) < INTERVAL "0 00:00:05.00" DAY TO SECOND)),
C AS (to_timestamp(C.element_time) - to_timestamp(A.element_time)
>= INTERVAL "0 00:00:05.00" DAY TO SECOND)
) as its
{code}
But let's make it even dumber if we can. There's a lot that we don't know we
don't know, and we will only find it when we go end-to-end. It don't want you
to build a lot of SQL grammar (and other stuff) and have to tear it down.
Also let's stick to standard SQL lexical norms, like single-quotes around
intervals, double-quotes to quote identifiers. In other words, use what is
already defined in Parser.jj, such as SimpleIdentifier() and IntervalLiteral().
And let's write this only in terms of tables, not streams, at first. It will be
much easier to test that way.
> Add MATCH_RECOGNIZE operator, for event pattern-matching
> --------------------------------------------------------
>
> Key: CALCITE-1570
> URL: https://issues.apache.org/jira/browse/CALCITE-1570
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Zhiqiang He
>
> Add the MATCH_RECOGNIZE operator, for event pattern-matching. Oracle
> [introduced this in
> 11i|https://oracle-base.com/articles/12c/pattern-matching-in-oracle-database-12cr1]
> (for tables) and Esper [implemented
> it|http://www.espertech.com/esper/release-5.1.0/esper-reference/html/match-recognize.html]
> (for streams of events).
> It would be most useful for streaming SQL but it makes sense in non-streaming
> SQL too (and of course it's good to be able to run streaming queries on
> historic data).
> Here is an example from
> [oracle-base|https://oracle-base.com/articles/12c/pattern-matching-in-oracle-database-12cr1]:
> {code}
> SELECT *
> FROM sales_history MATCH_RECOGNIZE (
> PARTITION BY product
> ORDER BY tstamp
> MEASURES STRT.tstamp AS start_tstamp,
> FINAL LAST(UP.tstamp) AS peak_tstamp,
> MATCH_NUMBER() AS mno,
> CLASSIFIER() AS cls
> ALL ROWS PER MATCH
> AFTER MATCH SKIP TO LAST DOWN
> PATTERN (STRT UP+ DOWN{1} UP+)
> DEFINE
> UP AS UP.units_sold > PREV(UP.units_sold),
> DOWN AS DOWN.units_sold < PREV(DOWN.units_sold)
> ) MR
> ORDER BY MR.product, MR.tstamp;
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
