Calculate Stock Price Consecutive Ups Using Pattern Matching

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         24

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