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
0
votes
1 answer

match_recognize pattern matches without seeing an event

In this use case, the overall pattern for breaching capacity is A followed by B, or B followed by A when the capacity goes above or below 100 ... for which the below match_recognize code works perfectly! expression breach { (A, B) => B.seqid >…
Dakotah North
  • 1,324
  • 12
  • 29
0
votes
2 answers

Oracle SQL - Return date range using MATCH_RECOGNIZE

I need help with one task I'm trying to finish. I need to join my data into the smallest possible date ranges and retrieve MIN(P_MIN) and SUM(P_MAX) over objects (in column 'name') under one id. |ID |NAME |DATE_FROM |DATE_TO …
MichalAndrzej
  • 77
  • 1
  • 11
0
votes
1 answer

match recognize collect row data into single column

I'm following the tutorial for match_recognize found here: create or replace temporary table stock_price_history (company text, price_date date, price int); insert into stock_price_history values ('ABCD', '2020-10-01', 50), ('ABCD',…
MoneyBall
  • 2,343
  • 5
  • 26
  • 59
0
votes
1 answer

Dynamic pattern quantifier usage in match_recognize clause

Sample query: select * from table match_recognize ( order by column1 pattern (anything {**3**,}) define anything as column1 = 'col' ); Considering I have a table and I want to locate consecutive rows which follow the pattern as…
avkrish
  • 15
  • 3
0
votes
2 answers

Oracle match recognize rows from the previous day

I have this table and I would like to extract, by partition of split, the rows that have a part P1 in their PART column and have a row of PART='P2' in the day before. SPLIT PART DATE -------------------------- S1 P1 21/09/2022 …
Javi Torre
  • 724
  • 8
  • 23
0
votes
1 answer

Oracle SQL - How to create common date periods (using MATCH_RECOGNIZE?)

I have problem with divide dates into correct periods. Here's an example: id count code date_from date_to 4364 2 201 01/08/2022 15:00:00 10/09/2022 22:00:00 4364 2 201 13/09/2022 05:20:00 30/09/2022 17:00:00 4364 2 4013 29/08/2022…
MichalAndrzej
  • 77
  • 1
  • 11
0
votes
1 answer

Flink SQL 1.14 : Match Recognize doesn't support consuming update and delete changes which is produced by node Join(joinType=[InnerJoin])

I have tried to resolve this issue : Could not execute SQL statement. Reason: org.apache.flink.table.api.TableException: Match Recognize doesn't support consuming update and delete changes which is produced by node Join(joinType=[InnerJoin],…
FLISS Sabrine
  • 11
  • 1
0
votes
1 answer

Snowflake MATCH_RECOGNIZE to skip not important events

I have the following events ordered by the time they happened: e4 -> e2 -> e2 -> e3 -> e10 -> e4 How should I write PATTERN part of MATCH_RECOGNIZE to match record if e2 event happened and then e4 happened(e2 before e4) no matter if there are 0 or…
datahack
  • 477
  • 1
  • 11
  • 32
0
votes
1 answer

Oracle SQL or PL/SQL: Add multiple columns in MATCH_RECOGNIZE with different calculations or computed columns

Happy Thanksgiving!! This post is for learning and educational purpose only Structure of table, insert statements and SQL query are updated in below db fiddle link to keep this post clean and short. Please refer below link: db<>fiddle I am trying to…
Richa
  • 337
  • 4
  • 18
0
votes
1 answer

Is Flink's Match_Recognize function suitable for capturing this type of pattern?

I'm trying to catch events in pattern described below: Start event = SalePackageA event (Customer A purchasing PackageA) 2-nd event = PackageUsage event (Customer A uses PackageA) 3-rd event = PackageUsage event (Customer A uses PackageA) 4-th…
deeplay
  • 376
  • 3
  • 20
0
votes
1 answer

Understanding the below clauses in match recognize

This question is already answered but I could not get few parts of the query. Below is the input table. ID NAME DISEASE DRUG SHIP_DATE SUPPLY 1 aa D1 dd 10-05-2020 30 1 aa D1 dd 07-06-2020 30 1 aa …
Random Guy
  • 7
  • 4
0
votes
1 answer

Flink: What are the best alternatives to using Python UDFs in MATCH_RECOGNIZE?

When I try to use MATCH_RECOGNIZE in my SQL queries with Python UDFs, I get the error Python Function can not be used in MATCH_RECOGNIZE for now. For example, the following is not supported: SELECT T.aa as ta FROM MyTable MATCH_RECOGNIZE ( ORDER…
pledab
  • 120
  • 1
  • 13
0
votes
1 answer

SQL Oracle - Group by ID, task ID, min and max timestamp

I have data of users performing different tasks. I would like to group this data per userid and task id to get the start and end times per task. When the employee changes to another task, there should be a new row with new start and end…
Nani_Momo
  • 13
  • 2
0
votes
1 answer

Funnel Analytics using SQL in Snowflake

I am working on building a query to track the life cycle of an user through the platform via events. The table EVENTS has 3 columns USER_ID, DATE_TIME and EVENT_NAME. Below is a snapshot of the table, Below is my query, SELECT * FROM…
Jude92
  • 167
  • 2
  • 6
  • 20
0
votes
1 answer

Nested match_recognize query not supported in flink SQL?

I am using flink 1.11 and trying nested query where match_recognize is inside, as shown below : select * from events where id = (SELECT * FROM events MATCH_RECOGNIZE (PARTITION BY org_id ORDER BY proctime MEASURES A.id AS startId ONE ROW PER MATCH…
voidMainReturn
  • 3,339
  • 6
  • 38
  • 66
1
2
3