flink-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From GitBox <...@apache.org>
Subject [GitHub] twalthr commented on a change in pull request #7177: [FLINK-7599] [table] Support for aggregates in MATCH_RECOGNIZE
Date Fri, 14 Dec 2018 12:46:17 GMT
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_r241742837
 
 

 ##########
 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;
+{% 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 the pattern variable `A` as long as the average
price of them 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`. Thus
the results for said query will be:
+
+{% highlight text %}
+ symbol       start_tstamp       end_tstamp          avgPrice
+=========  ==================  ==================  ============
+ACME       01-APR-11 10:00:00  01-APR-11 10:00:03     14.5
+ACME       01-APR-11 10:00:04  01-APR-11 10:00:09     13.5
+{% endhighlight %}
+
+<span class="label label-info">Note</span> Aggregations can be applied to expressions,
but only if they reference a single pattern variable. Thus `SUM(A.price * A.tax)` is a valid
one, but `AVG(A.price * B.tax)` is not.
+
+<span class="label label-danger">Attention</span> `DISTINCT` aggregations are
not supported.
 
 Review comment:
   Can you open a follow up issue for this if it does not exist yet?

----------------------------------------------------------------
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:
users@infra.apache.org


With regards,
Apache Git Services

Mime
View raw message