MATCH_RECOGNIZE in Oracle

 MATCH_RECOGNIZE is a powerful feature in Oracle that brings pattern recognition capabilities directly into SQL. It’s part of the SQL:2016 standard and allows you to find patterns within your data using a syntax similar to regular expressions, but specifically designed for relational data.

The Core Concept

At its heart, MATCH_RECOGNIZE helps you detect sequences and patterns in your data that would otherwise require complex procedural code or multiple self-joins. Think of it as a way to find stories in your data — “Show me all cases where sales increased for three consecutive months and then dropped.”

When to Use MATCH_RECOGNIZE

You should consider using MATCH_RECOGNIZE when:

-You need to identify specific sequences or patterns in ordered data

-You’re working with time-series data analysis

-You want to detect trends, anomalies, or specific behaviors in your data

-Complex self-joins or procedural code would otherwise be needed

Basic Structure of MATCH_RECOGNIZE

Let me break down the bqsic syntax:

SELECT *
FROM your_table
MATCH_RECOGNIZE (
PARTITION BY partition_column -- Optional: group data for separate pattern matching
ORDER BY ordering_column -- Required: defines the sequence order
MEASURES -- Define what to return when pattern is found
measure_expression AS measure_column_name
[ALL|ONE] ROW PER MATCH -- How many rows to return per match
[AFTER MATCH SKIP...] -- Where to continue pattern search after finding a match
PATTERN (pattern_definition) -- The pattern to search for using pattern variables
DEFINE -- Define each pattern variable using boolean conditions
pattern_variable AS condition,
...
)

Example 1: Basic Pattern — Finding Stock Price Increases

Let’s say we have a table of daily stock prices and want to find instances where the price increased for three consecutive days:

-- First, let's create sample data

CREATE TABLE stock_prices (

trade_date DATE,

ticker VARCHAR2(10),

closing_price NUMBER(10,2)

);

-- Insert some sample data

INSERT INTO stock_prices VALUES (DATE '2023-01-01', 'ORCL', 80.00);

INSERT INTO stock_prices VALUES (DATE '2023-01-02', 'ORCL', 82.50);

INSERT INTO stock_prices VALUES (DATE '2023-01-03', 'ORCL', 85.75);

INSERT INTO stock_prices VALUES (DATE '2023-01-04', 'ORCL', 84.25);

INSERT INTO stock_prices VALUES (DATE '2023-01-05', 'ORCL', 86.50);

INSERT INTO stock_prices VALUES (DATE '2023-01-06', 'ORCL', 89.75);

INSERT INTO stock_prices VALUES (DATE '2023-01-07', 'ORCL', 91.25);

INSERT INTO stock_prices VALUES (DATE '2023-01-08', 'ORCL', 93.50);

INSERT INTO stock_prices VALUES (DATE '2023-01-09', 'ORCL', 94.75);

INSERT INTO stock_prices VALUES (DATE '2023-01-10', 'ORCL', 92.25);

INSERT INTO stock_prices VALUES (DATE '2023-01-11', 'ORCL', 89.00);

INSERT INTO stock_prices VALUES (DATE '2023-01-12', 'ORCL', 84.50);

INSERT INTO stock_prices VALUES (DATE '2023-01-13', 'ORCL', 87.25);

INSERT INTO stock_prices VALUES (DATE '2023-01-14', 'ORCL', 91.00);

INSERT INTO stock_prices VALUES (DATE '2023-01-15', 'ORCL', 94.50);

INSERT INTO stock_prices VALUES (DATE '2023-01-16', 'ORCL', 92.00);

INSERT INTO stock_prices VALUES (DATE '2023-01-17', 'ORCL', 88.75);

COMMIT;

-- Add another stock with double top pattern

INSERT INTO stock_prices VALUES (DATE '2023-01-01', 'MSFT', 310.00);

INSERT INTO stock_prices VALUES (DATE '2023-01-02', 'MSFT', 315.50);

INSERT INTO stock_prices VALUES (DATE '2023-01-03', 'MSFT', 322.75);

INSERT INTO stock_prices VALUES (DATE '2023-01-04', 'MSFT', 327.50);

INSERT INTO stock_prices VALUES (DATE '2023-01-05', 'MSFT', 330.25);

INSERT INTO stock_prices VALUES (DATE '2023-01-06', 'MSFT', 326.50);

INSERT INTO stock_prices VALUES (DATE '2023-01-07', 'MSFT', 318.75);

INSERT INTO stock_prices VALUES (DATE '2023-01-08', 'MSFT', 310.25);

INSERT INTO stock_prices VALUES (DATE '2023-01-09', 'MSFT', 305.00);

INSERT INTO stock_prices VALUES (DATE '2023-01-10', 'MSFT', 311.25);

INSERT INTO stock_prices VALUES (DATE '2023-01-11', 'MSFT', 318.50);

INSERT INTO stock_prices VALUES (DATE '2023-01-12', 'MSFT', 325.75);

INSERT INTO stock_prices VALUES (DATE '2023-01-13', 'MSFT', 329.25);

INSERT INTO stock_prices VALUES (DATE '2023-01-14', 'MSFT', 325.00);

INSERT INTO stock_prices VALUES (DATE '2023-01-15', 'MSFT', 320.75);

COMMIT;

INSERT INTO stock_prices VALUES (DATE '2023-01-01', 'GOOG', 150.00);

INSERT INTO stock_prices VALUES (DATE '2023-01-02', 'GOOG', 158.50);

INSERT INTO stock_prices VALUES (DATE '2023-01-03', 'GOOG', 151.75);

INSERT INTO stock_prices VALUES (DATE '2023-01-04', 'GOOG', 160.25);

INSERT INTO stock_prices VALUES (DATE '2023-01-05', 'GOOG', 155.50);

INSERT INTO stock_prices VALUES (DATE '2023-01-06', 'GOOG', 162.75);

INSERT INTO stock_prices VALUES (DATE '2023-01-07', 'GOOG', 160.00);

INSERT INTO stock_prices VALUES (DATE '2023-01-08', 'GOOG', 159.25);

INSERT INTO stock_prices VALUES (DATE '2023-01-09', 'GOOG', 164.50);

INSERT INTO stock_prices VALUES (DATE '2023-01-10', 'GOOG', 161.75);

COMMIT;

-- Add data exhibiting a channel pattern

INSERT INTO stock_prices VALUES (DATE '2023-01-01', 'AMZN', 130.00);

INSERT INTO stock_prices VALUES (DATE '2023-01-02', 'AMZN', 133.50);

INSERT INTO stock_prices VALUES (DATE '2023-01-03', 'AMZN', 131.75);

INSERT INTO stock_prices VALUES (DATE '2023-01-04', 'AMZN', 134.25);

INSERT INTO stock_prices VALUES (DATE '2023-01-05', 'AMZN', 132.50);

INSERT INTO stock_prices VALUES (DATE '2023-01-06', 'AMZN', 135.75);

INSERT INTO stock_prices VALUES (DATE '2023-01-07', 'AMZN', 133.25);

INSERT INTO stock_prices VALUES (DATE '2023-01-08', 'AMZN', 136.50);

INSERT INTO stock_prices VALUES (DATE '2023-01-09', 'AMZN', 134.75);

INSERT INTO stock_prices VALUES (DATE '2023-01-10', 'AMZN', 137.00);

INSERT INTO stock_prices VALUES (DATE '2023-01-11', 'AMZN', 135.25);

INSERT INTO stock_prices VALUES (DATE '2023-01-12', 'AMZN', 143.50);

COMMIT;

Output:  (Verla en la pagina original)  https://medium.com/@lahsaini/match-recognize-in-oracle-f413bee749cc

Now, let’s use MATCH_RECOGNIZE to find patterns of three consecutive price increases

SELECT *

FROM stock_prices

MATCH_RECOGNIZE (

PARTITION BY ticker -- Apply pattern matching per stock

ORDER BY trade_date -- Order by date to establish sequence

MEASURES

FIRST(trade_date) AS start_date, -- Return first date of pattern

LAST(trade_date) AS end_date, -- Return last date of pattern

FIRST(closing_price) AS start_price, -- Starting price

LAST(closing_price) AS end_price -- Ending price

ONE ROW PER MATCH -- Return one summary row per match

AFTER MATCH SKIP TO NEXT ROW -- After finding a match, start looking from next row

PATTERN (A B C) -- Look for pattern with three variables

DEFINE

B AS B.closing_price > A.closing_price, -- B's price must be higher than A's

C AS C.closing_price > B.closing_price -- C's price must be higher than B's

)

ORDER BY start_date;

Output:  (Verla en la pagina original)  https://medium.com/@lahsaini/match-recognize-in-oracle-f413bee749cc

This query will find all instances where there were three consecutive days of price increases for each stock ticker.

Example 2: More Complex Pattern — V-shaped Recovery

Now let’s detect a V-shaped pattern where the price drops for at least 2 days and then rises for at least 2 days:

SELECT ticker,
start_date,
bottom_date,
end_date,
start_price,
bottom_price,
end_price,
ROUND((end_price - bottom_price) / bottom_price * 100, 2) AS recovery_percent
FROM stock_prices
MATCH_RECOGNIZE (
PARTITION BY ticker
ORDER BY trade_date
MEASURES
FIRST(trade_date) AS start_date,
LAST(DOWN.trade_date) AS bottom_date,
LAST(trade_date) AS end_date,
FIRST(closing_price) AS start_price,
LAST(DOWN.closing_price) AS bottom_price,
LAST(closing_price) AS end_price
ONE ROW PER MATCH
AFTER MATCH SKIP TO LAST DOWN
PATTERN (STRT DOWN+ UP+)
DEFINE
DOWN AS DOWN.closing_price < PREV(DOWN.closing_price),
UP AS UP.closing_price > PREV(UP.closing_price)
)
WHERE (end_price - bottom_price) / bottom_price > 0.05 -- Only show recoveries of more than 5%
ORDER BY ticker, start_date;

Output:  (Verla en la pagina original)  https://medium.com/@lahsaini/match-recognize-in-oracle-f413bee749cc

In this example:

-We’re looking for a starting point, followed by one or more consecutive down days, followed by one or more consecutive up days

-The DOWN+ and UP+ syntax means "one or more" (like regular expressions)

-We’re skipping to the last DOWN after a match, which allows overlapping patterns

-We’re adding a condition to only show significant recoveries

Example 3: Advanced Pattern — Double Top Pattern in Trading

Now let’s look for a classic trading pattern — the double top, which can signal a reversal:

SELECT ticker,
first_peak_date,
valley_date,
second_peak_date,
first_peak_price,
valley_price,
second_peak_price,
ROUND(ABS(first_peak_price - second_peak_price) / first_peak_price * 100, 2) AS peak_diff_percent
FROM stock_prices
MATCH_RECOGNIZE (
PARTITION BY ticker
ORDER BY trade_date
MEASURES
PEAK1.trade_date AS first_peak_date,
VALLEY.trade_date AS valley_date,
PEAK2.trade_date AS second_peak_date,
PEAK1.closing_price AS first_peak_price,
VALLEY.closing_price AS valley_price,
PEAK2.closing_price AS second_peak_price
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (RISE1* PEAK1 FALL1+ VALLEY RISE2+ PEAK2 FALL2*)
DEFINE
PEAK1 AS PEAK1.closing_price > PREV(closing_price) AND
PEAK1.closing_price >= NEXT(closing_price),
FALL1 AS FALL1.closing_price < PREV(closing_price),
VALLEY AS VALLEY.closing_price <= PREV(closing_price) AND
VALLEY.closing_price <= NEXT(closing_price),
RISE2 AS RISE2.closing_price > PREV(closing_price),
PEAK2 AS PEAK2.closing_price >= PREV(closing_price) AND
PEAK2.closing_price >= NEXT(closing_price) AND
ABS(PEAK2.closing_price - PEAK1.closing_price) / PEAK1.closing_price < 0.05,
FALL2 AS FALL2.closing_price < PREV(closing_price)
)
WHERE valley_price < first_peak_price * 0.95 -- Valley must be at least 5% below peaks
ORDER BY ticker, first_peak_date;

Output:  (Verla en la pagina original)  https://medium.com/@lahsaini/match-recognize-in-oracle-f413bee749cc

This example looks for the classic double top pattern with several specific conditions:

-A rise to a peak

-A fall to a valley

-Another rise to a second peak that’s within 5% of the first peak’s price

-Another fall afterward

-The valley must be at least 5% below the peaks

Example 4: Advanced With Aggregation — Detecting Volatility Clusters

Let’s detect periods of high volatility, defined as having daily price changes greater than 3% for at least 3 out of 5 consecutive days:

WITH daily_changes AS (
SELECT
ticker,
trade_date,
closing_price,
LAG(closing_price) OVER (PARTITION BY ticker ORDER BY trade_date) AS prev_price,
CASE
WHEN LAG(closing_price) OVER (PARTITION BY ticker ORDER BY trade_date) IS NOT NULL
THEN ABS(closing_price - LAG(closing_price) OVER (PARTITION BY ticker ORDER BY trade_date)) /
LAG(closing_price) OVER (PARTITION BY ticker ORDER BY trade_date) * 100
ELSE NULL
END AS pct_change
FROM stock_prices
)
SELECT
ticker,
start_date,
end_date,
num_volatile_days,
avg_volatility
FROM daily_changes
MATCH_RECOGNIZE (
PARTITION BY ticker
ORDER BY trade_date
MEASURES
FIRST(trade_date) AS start_date,
LAST(trade_date) AS end_date,
COUNT(*) AS total_days,
SUM(CASE WHEN pct_change > 3.0 THEN 1 ELSE 0 END) AS num_volatile_days,
AVG(pct_change) AS avg_volatility
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW
PATTERN (STRT DAY1 DAY2 DAY3 DAY4)
DEFINE
STRT AS 1=1,
DAY1 AS DAY1.trade_date = PREV(trade_date) + 1,
DAY2 AS DAY2.trade_date = PREV(trade_date) + 1,
DAY3 AS DAY3.trade_date = PREV(trade_date) + 1,
DAY4 AS DAY4.trade_date = PREV(trade_date) + 1
)
WHERE num_volatile_days >= 3
ORDER BY ticker, start_date;

The CTE was used to pre-calculate daily changes:

Output:  (Verla en la pagina original)  https://medium.com/@lahsaini/match-recognize-in-oracle-f413bee749cc

Example 5: Finding Price Channels

(Verla en la pagina original)  https://medium.com/@lahsaini/match-recognize-in-oracle-f413bee749cc

Fuente:

Artículo:   "MATCH_RECOGNIZE in Oracle" Publicado en https://medium.com/ por Lh Mohammed el 19 feb 2025.  consultado el 23 ago 2026

URl: https://medium.com/@lahsaini/match-recognize-in-oracle-f413bee749cc