You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Feinauer (Jira)" <ji...@apache.org> on 2019/08/26 12:03:00 UTC
[jira] [Created] (CALCITE-3294) Implement FINAL Clause for
MATCH_RECOGNIZE
Julian Feinauer created CALCITE-3294:
----------------------------------------
Summary: Implement FINAL Clause for MATCH_RECOGNIZE
Key: CALCITE-3294
URL: https://issues.apache.org/jira/browse/CALCITE-3294
Project: Calcite
Issue Type: Improvement
Reporter: Julian Feinauer
With CALCITE-1935 the initial support for the MATCH_RECOGNIZE clause was introduced. But it is still lacking several features.
One of them is the FINAL Clause which forces the `MEASURE` to act global on all Tuples which were matched by the pattern.
See https://oracle-base.com/articles/12c/pattern-matching-in-oracle-database-12cr1
An example query would be:
{code}
SELECT *
FROM sales_history MATCH_RECOGNIZE (
ORDER BY tstamp
MEASURES
LAST(A.tstamp) AS ts_prev,
FINAL LAST(A.tstamp) AS ts_last
ALL ROWS PER MATCH
PATTERN (A+)
DEFINE
A AS A.units_sold > 10
) MR
ORDER BY MR.product, MR.start_tstamp;
{code}
Here, the query matches for each sequence of rows which all have `units_sold > 10`.
For the column `ts_prev` it always shows the timestamp of the timestamp of the previous match (the row before).
But for `ts_last` it shows the SAME value for each column as the `FINAL` modifier changes teh behavior to apply the `LAST` operator to the record set (similar to a window aggregation on the machted subset of rows).
--
This message was sent by Atlassian Jira
(v8.3.2#803003)