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)