[jira] [Created] (CALCITE-1570) Add MATCH_RECOGNIZE operator, for event pattern-matching

["Julian Hyde (JIRA)" <ji...@apache.org>]

Julian Hyde created CALCITE-1570:
------------------------------------

             Summary: 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: Julian Hyde


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.4#6332)