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:
