dawidwys commented on a change in pull request #7177: [FLINK-7599] [table]
Support for aggregates in MATCH_RECOGNIZE
URL: https://github.com/apache/flink/pull/7177#discussion_r241977805
##########
File path: docs/dev/table/streaming/match_recognize.md
##########
@@ -211,6 +211,65 @@ If a condition is not defined for a pattern variable, a
default condition will b
For a more detailed explanation about expressions that can be used in those
clauses, please have a look at the [event stream
navigation](#pattern-navigation) section.
+### Aggregations
+
+Aggregations can be used in `DEFINE` and `MEASURES` clauses. Both
[built-in]({{ site.baseurl }}/dev/table/sql.html#built-in-functions) and custom
[user defined]({{ site.baseurl }}/dev/table/udfs.html) functions are supported.
+
+Aggregate functions are applied to each subset of rows mapped to a match. In
order to understand how those subsets are evaluated have a look at the [event
stream navigation](#pattern-navigation) section.
+
+The task of the following example is to find the longest period of time for
which the average price of a ticker did not go below certain threshold. It
shows how expressible `MATCH_RECOGNIZE` can become with aggregations.
+This task can be performed with the following query:
+
+{% highlight sql %}
+SELECT *
+FROM Ticker
+MATCH_RECOGNIZE (
+ PARTITION BY symbol
+ ORDER BY rowtime
+ MEASURES
+ FIRST(A.rowtime) AS start_tstamp,
+ LAST(A.rowtime) AS end_tstamp,
+ AVG(A.price) AS avgPrice
+ ONE ROW PER MATCH
+ AFTER MATCH SKIP TO FIRST B
+ PATTERN (A+ B)
+ DEFINE
+ A AS AVG(A.price) < 15
+ ) MR;
Review comment:
It is optional, but it is needed if we want to e.g. join the result of MR
with other table. For example in a query:
SELECT MR.aid as mId, T.id as tId
FROM MyTable
MATCH_RECOGNIZE (
ORDER BY proctime
MEASURES
A.id AS aid,
A.name as aName
PATTERN (A)
DEFINE
A as A.id > 0
) MR, MyTable T
WHERE MR.aName = T.name
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
With regards,
Apache Git Services
