How to shorten MATCH_RECOGNIZE's DEFINE clause

[Dongwon Kim <ea...@gmail.com>]

Hi,

Flink relational apis with MATCH_RECOGNITION looks very attractive and
promising but it's currently not easy to use at all.

While looking into [1],
especially the following DEFINE clause,

> DEFINE
> PRICE_DOWN AS
> (LAST(PRICE_DOWN.price, 1) IS NULL AND PRICE_DOWN.price < START_ROW.price) OR
> PRICE_DOWN.price < LAST(PRICE_DOWN.price, 1),
> PRICE_UP AS
> PRICE_UP.price > LAST(PRICE_DOWN.price, 1)

what came up to my mind is that the condition is quite verbose.

Meanwhile, I found [2] in which Oracle uses the similar data and the
MATCH_RECOGNIZE clause as follows:

> SELECT *
> FROM Ticker MATCH_RECOGNIZE (
>      PARTITION BY symbol
>      ORDER BY tstamp
>      MEASURES  STRT.tstamp AS start_tstamp,
>                LAST(DOWN.tstamp) AS bottom_tstamp,
>                LAST(UP.tstamp) AS end_tstamp
>      ONE ROW PER MATCH
>      AFTER MATCH SKIP TO LAST UP
>      PATTERN (STRT DOWN+ UP+)
>      DEFINE
>         DOWN AS DOWN.price < PREV(DOWN.price),
>         UP AS UP.price > PREV(UP.price)
>      ) MR
> ORDER BY MR.symbol, MR.start_tstamp;
>
> Oracle's way of defining pattern variables looks much simple and neat
thanks to
1) its use of PREV which is not available in Flink's MATCH_RECOGNIZE clause
(mentioned in [3])
2) no need to check the initial case or when there is no last row that met
the PRICE_DOWN condition

Is there a way to shorten the DEFINE cluase in MATCH_RECOGNIZE at Flink 1.8
or 1.9?
If there's any plan regarding this, could anyone let me know related Jira
issues?

Best,
Dongwon

[1]
https://ci.apache.org/projects/flink/flink-docs-stable/dev/table/streaming/match_recognize.html#introduction-and-examples
[2]  https://docs.oracle.com/database/121/DWHSG/pattern.htm
[3]
https://ci.apache.org/projects/flink/flink-docs-stable/dev/table/streaming/match_recognize.html#known-limitations

Re: How to shorten MATCH_RECOGNIZE's DEFINE clause

[Fabian Hueske <fh...@gmail.com>]

Hi Dongwon,

I'm not super familiar with Flink's MATCH_RECOGNIZE support, but Dawid (in
CC) might have some ideas about it.

Best,
Fabian

Am Mi., 21. Aug. 2019 um 07:23 Uhr schrieb Dongwon Kim <
eastcirclek@gmail.com>:

> Hi,
>
> Flink relational apis with MATCH_RECOGNITION looks very attractive and
> promising but it's currently not easy to use at all.
>
> While looking into [1],
> especially the following DEFINE clause,
>
>> DEFINE
>> PRICE_DOWN AS
>> (LAST(PRICE_DOWN.price, 1) IS NULL AND PRICE_DOWN.price < START_ROW.price
>> ) OR PRICE_DOWN.price < LAST(PRICE_DOWN.price, 1),
>> PRICE_UP AS
>> PRICE_UP.price > LAST(PRICE_DOWN.price, 1)
>
> what came up to my mind is that the condition is quite verbose.
>
> Meanwhile, I found [2] in which Oracle uses the similar data and the
> MATCH_RECOGNIZE clause as follows:
>
>> SELECT *
>> FROM Ticker MATCH_RECOGNIZE (
>>      PARTITION BY symbol
>>      ORDER BY tstamp
>>      MEASURES  STRT.tstamp AS start_tstamp,
>>                LAST(DOWN.tstamp) AS bottom_tstamp,
>>                LAST(UP.tstamp) AS end_tstamp
>>      ONE ROW PER MATCH
>>      AFTER MATCH SKIP TO LAST UP
>>      PATTERN (STRT DOWN+ UP+)
>>      DEFINE
>>         DOWN AS DOWN.price < PREV(DOWN.price),
>>         UP AS UP.price > PREV(UP.price)
>>      ) MR
>> ORDER BY MR.symbol, MR.start_tstamp;
>>
>> Oracle's way of defining pattern variables looks much simple and neat
> thanks to
> 1) its use of PREV which is not available in Flink's MATCH_RECOGNIZE
> clause (mentioned in [3])
> 2) no need to check the initial case or when there is no last row that met
> the PRICE_DOWN condition
>
> Is there a way to shorten the DEFINE cluase in MATCH_RECOGNIZE at Flink
> 1.8 or 1.9?
> If there's any plan regarding this, could anyone let me know related Jira
> issues?
>
> Best,
> Dongwon
>
> [1]
> https://ci.apache.org/projects/flink/flink-docs-stable/dev/table/streaming/match_recognize.html#introduction-and-examples
> [2]  https://docs.oracle.com/database/121/DWHSG/pattern.htm
> [3]
> https://ci.apache.org/projects/flink/flink-docs-stable/dev/table/streaming/match_recognize.html#known-limitations
>
>