[
https://issues.apache.org/jira/browse/CALCITE-3294?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16918044#comment-16918044
]
Julian Feinauer commented on CALCITE-3294:
------------------------------------------
Found this pretty nice expansion
https://docs.oracle.com/database/121/DWHSG/pattern.htm#GUID-F7ECC486-0BDA-47AD-90B9-C356F5E18C5B
> 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
> Priority: Major
>
> 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)
