How to shorten MATCH_RECOGNIZE's DEFINE clause

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

How to shorten MATCH_RECOGNIZE's DEFINE clause

eastcirclek
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


Reply | Threaded
Open this post in threaded view
|

Re: How to shorten MATCH_RECOGNIZE's DEFINE clause

Fabian Hueske-2
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 <[hidden email]>:
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