[CALCITE-3294] Implement FINAL Clause for MATCH_RECOGNIZE

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Julian Feinauer
• Created: 26/Aug/19 12:02
• Updated: 28/Aug/19 19:58

Description

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:

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;

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).

Issue Links

• is related to: CALCITE-1935 Reference implementation for MATCH_RECOGNIZE