[CALCITE-1570] Add MATCH_RECOGNIZE operator, for event pattern-matching

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Resolved
• Fix Version/s: 1.13.0
• Labels: match
• Assignee: Zhiqiang He
• Reporter: Julian Hyde
• Created: 09/Jan/17 20:55
• Updated: 17/Jul/18 23:04
• Resolved: 20/Jun/17 01:05

Description

Add the MATCH_RECOGNIZE operator, for event pattern-matching. Oracle introduced this in 11i (for tables) and Esper implemented it (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:

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;

Issue Links

• is related to: CALCITE-2418 Remove matchRecognize field of SqlSelect
• is related to: FLINK-3318 Add support for quantifiers to CEP's pattern API
• is related to: FLINK-3319 Add or operator to CEP's pattern API

Sub-Tasks

• Base functions support for MATCH_RECOGNIZE: Closed
• Support MEASURES clause in MATCH_RECOGNIZE: Closed
• AFTER MATCH sub-clause of MATCH_RECOGNIZE clause: Closed
• subset clause syntax support for MATCH_RECOGNIZE: Closed
• Row per match syntax support for MATCH_RECOGNIZE: Closed
• Partition by and order by syntax support for MATCH_RECOGNIZE: Closed
• Classifier and match_number syntax support for MATCH_RECOGNIZE: Closed