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

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

    [ https://issues.apache.org/jira/browse/CALCITE-1570?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15826484#comment-15826484 ] 

Julian Hyde commented on CALCITE-1570:
--------------------------------------

[~fhueske] has been building CEP functionality into Apache Flink and has talked about extensions to SQL for matching patterns. Here is an example from his presentation with [~trohrmann@apache.org], [Streaming Analytics & CEP: Two sides of the same coin?|http://www.slideshare.net/tillrohrmann/streaming-analytics-cep-two-sides-of-the-same-coin]:

{code}
SELECT
  TUMBLE_START(tStamp, INTERVAL '1' DAY) as day,
  AVG(duration) as avgDuration
FROM (
  // CEP pattern
  SELECT (b.tStamp ­ a.tStamp) as duration, b.tStamp as tStamp
    FROM inputs
    PATTERN
      a FOLLOW BY b PARTITION BY orderId ORDER BY tStamp 
      WITHIN INTERVAL '1’ HOUR
WHERE
   a.status = ‘received’ AND b.status = ‘shipped’
  )
GROUP BY
  TUMBLE(tStamp, INTERVAL '1’ DAY){code}


> 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)