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

Add MATCH_RECOGNIZE operator, for event pattern-matching

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Resolved
    • None
    • 1.13.0
    • None

    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

          There are no Sub-Tasks for this issue.

          Activity

            People

              ransom Zhiqiang He
              julianhyde Julian Hyde
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: