[
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 [[email protected]], [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)
