[jira] [Commented] (CALCITE-3294) Implement FINAL Clause for MATCH_RECOGNIZE

["Julian Feinauer (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3294?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16915742#comment-16915742 ] 

Julian Feinauer commented on CALCITE-3294:
------------------------------------------

[~julianhyde] I could imagine working on that (although I have not that much time atm, just as a warning). But I would like to have your insights or ideas on how to implement that. My first idea is to treat it somewhat similar to how the Window Aggreation works as it should be really similar under the covers. Any ideas?

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