Patter matching SQL support in Oracle 12c is a very powerful. Let take a took at the following stock price table. We want to calculate days when the price goes up for 4 or more consecutive days.
SQL> select * from STOCK order by dat; SYM DAT PRICE --- --------- ---------- XYZ 31-MAY-11 14 XYZ 01-JUN-11 19 XYZ 02-JUN-11 21 XYZ 03-JUN-11 23 XYZ 04-JUN-11 27 XYZ 05-JUN-11 14 XYZ 06-JUN-11 17 XYZ 07-JUN-11 22 XYZ 08-JUN-11 26 XYZ 09-JUN-11 27 XYZ 10-JUN-11 21 XYZ 11-JUN-11 17 XYZ 12-JUN-11 27 XYZ 13-JUN-11 27 XYZ 14-JUN-11 16 XYZ 15-JUN-11 14 XYZ 16-JUN-11 16 XYZ 17-JUN-11 26 XYZ 18-JUN-11 25 XYZ 19-JUN-11 24To find patterns where the prices goes up for 4 or more consecutive days, we use the following pattern matching query.
SELECT *
FROM stock MATCH_RECOGNIZE (
PARTITION BY SYMBOL
ORDER BY dat
MEASURES strt.price bal,strt.dat dat,
last (up.price) as last_up_price,
last (up.dat) AS last_up_dat
ONE ROW PER MATCH
AFTER MATCH SKIP TO LAST UP
PATTERN (STRT up{4,} )
DEFINE
up AS up.price > PREV(up.price)
) MR
ORDER BY SYMBOL, dat;
SYM BAL DAT LAST_UP_PRICE LAST_UP_D
--- ---------- --------- ------------- ---------
XYZ 14 31-MAY-11 27 04-JUN-11
XYZ 14 05-JUN-11 27 09-JUN-11
In the above query, the pattern is defined as (strt up{4,}), any day followed by 4 days up. It also returns the last up price and day (last (up.price) and last (up.dat)).
