For example, that pattern describes a trading day during which the stock of XYZ Corp. began the day at a price of $100/share, then steadily fell throughout the morning until it hit a price of $50/share at noon, after which the price rose throughout the afternoon until it hit a peak of $150/share at 15:00 and then began to decline again. The info…

rowpatternmatch.sql

SELECT  a_symbol, /* stock symbol */
        a_tstamp, /* start time */
        a_price, /* start price */
        max_c_tstamp, /* inflection time */
        last_c_price, /* lowest price */
        max_f_tstamp, /* end time */
        last_f_price, /* end price */
        matchno
FROM stock_ticker
  MATCH_RECOGNIZE (
  PARTITION BY symbol
  ORDER BY tstamp
  MEASURES  A.symbol AS a_symbol,
            A.tstamp AS a_tstamp,
            A.price AS a_price,
            MAX(C.tstamp) AS max_c_tstamp,
            LAST(C.price) AS last_c_price,
            MAX(F.tstamp) AS max_f_tstamp,
            LAST(F.price) AS last_f_price,
            MATCH_NUMBER () AS matchno
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN ( A B C* D E* F+ )
  DEFINE  /* A is unspecified, defaults to TRUE, matches any row */
          B AS (B.price < PREV (B.price)),
          C AS (C.price <= PREV (C.price)),
          D AS (D.price > PREV (D.price)),
          E AS (E.price >= PREV (E.price)),
          F AS (F.price >= PREV (F.price)
            AND F.price > A.price)) AS T