Stack Overflow
Stack Overflow

Questions tagged [match-recognize]

This tag should be used with questions the SQL MATCH_RECOGNIZE clause

Oracle 12c introduced a powerful pattern matching feature that works similar to the MODEL clause, syntactically. For example, we can write queries like:

SELECT *
FROM series
MATCH_RECOGNIZE (
  ORDER BY id
  MEASURES classifier() AS trg
  ALL ROWS PER MATCH
  PATTERN (S (R X R+)?)
  DEFINE
    R AS sign(R.amount) = prev(sign(R.amount)),
    X AS sign(X.amount) = prev(sign(X.amount))
)

For more info, read the Oracle MATCH_RECOGNIZE whitepaper.

35 questions
7
votes
1 answer

What's the correct way to escape the ? character in a JDBC PreparedStatement when using Oracle 12c MATCH_RECOGNIZE?

The following query is correct in Oracle 12c: SELECT * FROM dual MATCH_RECOGNIZE ( MEASURES a.dummy AS dummy PATTERN (a?) DEFINE a AS (1 = 1) ) But it doesn't work through JDBC because of the ? character that is used as a regular expression…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
3
votes
2 answers

Snowflake match_recognize not including all instances of a given pattern using quantifiers

I'm trying to use Snowflake's match_recognize tool to match a particular pattern across rows. The pattern consists of any sequence of a's and b's, provided that long runs of b's are excluded. In my test case, I want to allow runs of up to 4 b's to…
Eric Cler
  • 33
  • 3
2
votes
2 answers

MATCH_RECOGNIZE to find events that do not end with certain pattern

I have the transaction audit history as following. All audits start with a INIT and may have a REVERSAL and a MODIFICATION. I need to find all the audits with INIT -> REVERSAL but no…
Saqib Ali
  • 3,953
  • 10
  • 55
  • 100
1
vote
2 answers

Query Consequtive Events Occuring on Item on Oracle Databse

I have an Oracle table (Oracle database v12.2.0.2.1) that records when an item has a protection (PROT) placed on it and it's subsequent protection removal (RMPR). The rule is, an item should not be protected twice consecutively. In other words,…
Mike
  • 4,099
  • 17
  • 61
  • 83
1
vote
2 answers

FIRST() and LAST() for MATCH_RECOGNIZE

We are analyzing the streaming twitter data to find users who are posting similar (almost same) tweets over and over. I am using MATCH_RECOGNIZE for this. It is able to find the pattern, but I am not able to get the FIRST() and the LAST() values…
Saqib Ali
  • 3,953
  • 10
  • 55
  • 100
1
vote
0 answers

LIMIT DURATION equivalent in Snowflake MATCH_RECOGNIZE

I am looking at pattern matching on streaming Stock data using Snowflake MATCH_RECOGNIZE. I need to limit the window of the data at any point to 30 days worth of data. In Microsoft Azure this is achieved using LIMIT DURATION clause in the…
Saqib Ali
  • 3,953
  • 10
  • 55
  • 100
1
vote
1 answer

How to define a pattern with match_recognize to find ordered events that aren't consecutive?

I'm trying to find sessions that contain 3 specific events, they need to be ordered, meaning that event_1 happens first, then event_2, then event_3, but they don't need to be concentrated exactly one after another. Instead, any number of other…
mare011
  • 117
  • 7
1
vote
1 answer

Snowflake match_recognize function to create step funnel

I've read several articles giving examples of what can MATCH_RECOGNIZE do. One of them is creating a step funnel. Let's say we want to track certain events and see after each of the event how many users left. For instance, arriving at the home page,…
datahack
  • 477
  • 1
  • 11
  • 32
1
vote
0 answers

Snowflake building step funnel

I have a table with 3 columns USER_ID EVENT_ID EVENT_DATETIME I am trying to figure out how can I write MATCH_RECOGNIZE so I can answer to following questions: How many distinct users interacted with event_id = 234, then during 10 minutes…
datahack
  • 477
  • 1
  • 11
  • 32
1
vote
2 answers

MATCH_RECOGNIZE with CTE in Snowflake

I am using MATCH_RECOGNIZE function in a query with a few CTEs. When I run the query, I got the following error: SQL compilation error: MATCH_RECOGNIZE not supported in this context. In my query, there are several CTEs before and after the…
kimi
  • 525
  • 5
  • 17
1
vote
1 answer

How to get specific rows for each id?

To understand a business process with several statuses, I want to get the rows with the following rules based on created_at column:
 First row of status ‘created’ Last row of ‘missing_info’ after ‘created’ (row_no 4) First row of ‘pending’…
kimi
  • 525
  • 5
  • 17
1
vote
2 answers

Match_Recognize in Snowflake is not returning what I think it should

I'm sure I'm not understanding this, but this is defined as PATTERN: Specifying the Pattern to Match PATTERN ( ) The pattern defines a valid sequence of rows that represents a match. The pattern is defined like a regular expression (regex) and is…
andru
  • 31
  • 4
1
vote
1 answer

Insert into first and last row of a MATCH_RECOGNIZE select Oracle

I am not really good in sql and i need your help. I got a table with sample data in Oracle DB like this: | DATE_TIME | TYPE | LAT | LON | MSG | | :------------------------ | :--- | :---- | :---- | :---- | | 12.11.20…
user14691287
  • 15
  • 5
1
vote
1 answer

Does match_recognize have an option to keep all original rows and to flag the matches?

I would like to add a flag to the original rows which indicates when a match was found. Is there a better way using only MATCH_RECOGNIZE without the need for the RIGHT JOIN that I've done below? There are 6 matches and I would like to flag these 6…
Bobby
  • 1,585
  • 3
  • 19
  • 42
0
votes
2 answers

Need help using match_recognize in Oracle 21c

I truly hope I can find understaning here as to what is wrong with this code. I have some equipment that send event data as event tags to a database; each timestamped with the time of the equipment of origin. There is a problem with an equipment's…
spyker
  • 1
  • 1
1
2 3