twalthr 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_r237491268
##########
File path: docs/dev/table/streaming/match_recognize.md
##########
@@ -211,6 +211,66 @@ 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.
+### Scalar & Aggregate functions
+
+One can use scalar and aggregate functions in those clauses, both
[built-in]({{ site.baseurl }}/dev/table/sql.html#built-in-functions) as well as
provide [user defined]({{ site.baseurl }}/dev/table/udfs.html) functions.
+
+Aggregate functions are applied to subset of rows mapped to a match. To
understand how those subsets are evaluated have a look at the [event stream
navigation](#pattern-navigation) section.
+
+With a task to find the longest period of time for which the average price of
a ticker did not go below certain threshold, one can see 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;
+{% endhighlight %}
+
+Given this query and following input values:
+
+{% highlight text %}
+symbol rowtime price tax
+====== ==================== ======= =======
+'ACME' '01-Apr-11 10:00:00' 12 1
+'ACME' '01-Apr-11 10:00:01' 17 2
+'ACME' '01-Apr-11 10:00:02' 13 1
+'ACME' '01-Apr-11 10:00:03' 16 3
+'ACME' '01-Apr-11 10:00:04' 25 2
+'ACME' '01-Apr-11 10:00:05' 2 1
+'ACME' '01-Apr-11 10:00:06' 4 1
+'ACME' '01-Apr-11 10:00:07' 10 2
+'ACME' '01-Apr-11 10:00:08' 15 2
+'ACME' '01-Apr-11 10:00:09' 25 2
+'ACME' '01-Apr-11 10:00:10' 30 1
+{% endhighlight %}
+
+The query will accumulate events as part of `A` pattern variable as long as
the average price of them does not exceed 15. Which will happen at `01-Apr-11
10:00:04`. The next such period that starts then will
Review comment:
```
The query will accumulate events as part of pattern variable `A` as long as
the average price of those events does not exceed `15`. For example, such a
limit exceeding happens at `01-Apr-11 10:00:04`. The following period exceeds
the average price of `15` again at `01-Apr-11 10:00:10`.
```
----------------------------------------------------------------
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
