Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1570

Add MATCH_RECOGNIZE operator, for event pattern-matching

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Resolved
    • Affects Version/s: None
    • Fix Version/s: 1.13.0
    • Component/s: None
    • Labels:

      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;
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                ransom Zhiqiang He
                Reporter:
                julianhyde Julian Hyde
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: