Feature Request: Implement `MATCH_RECOGNIZE` for Advanced Pattern Matching

[zhuliquan]

Is your feature request related to a problem or challenge?

The MATCH_RECOGNIZE clause, introduced in SQL:2016, provides powerful capabilities for pattern matching in data streams or tables. It is particularly useful for scenarios such as identifying user behavior patterns—for example, determining which users followed a specific sequence of page views and actions on a website before opening a support ticket or completing a purchase. I noticed that datafusion-sqlparser-rs can parse SQL queries which contain the MATCH_RECOGNIZE clause, which could serve as a foundation for implementation. Is there any interest in implementing this feature?

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response

[geoffreyclaude]

take

[geoffreyclaude]

I've got a pretty terrible hackathon POC which more or less works. I'll clean it up and hopefully open up a draft PR by the end of the week for follow up discussions!

[geoffreyclaude]

I'm pretty much AFK until September, so sharing what I've got so far: #16685

As written in the PR description, this is a partial POC of MATCH_RECOGNIZE, restricted to no cross referencing symbols in the DEFINE clause, and no streaming support (fully buffered as the WindowAgg node.)

The goal was really to explore if MATCH_RECOGNIZE could be built using existing DataFusion operators. Besides the core pattern matching part, it does seem pretty feasible!

[geoffreyclaude]

MATCH_RECOGNIZE is most probably too complex to integrate in the core DataFusion. As mentioned in #16505 (comment) for TABLESAMPLE, I believe it would make more sense to develop it as an extension in datafusion-contrib.
I've opened #18078, which is a prerequisite to allow building such extensions.