Julian Hyde created CALCITE-1570:
------------------------------------
Summary: 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)
