Mimic Row Pattern Matching in Oracle 11g

CodeProject
Oracle 12c introduced MATCH_RECOGNIZE clause for row pattern matching. If you are still using Oracle 11g, you can use listagg() and regexp_like() to implement row pattern matching. For example, in order to find users with event A followed by event D (other events allowed in between),

User	Sequence	Event
1	1	A
1	2	D
1	3	C
1	4	B
2	1	C
2	2	B
2	3	A
3	1	A
3	2	C
3	3	D

you can use the following query:

	with event_seq as (
	select
	user_id,
	listagg(event, '') within group (order by seq) as event_seq
	from
	user_event
	group by user_id
	)
	select *
	from event_seq
	where regexp_like(event_seq, 'A.*D');

view raw row_pattern_match_11g.sql hosted with ❤ by GitHub