Bringing Row Pattern Recognition to DuckDB: Translating MATCH_RECOGNIZE to WITH RECURSIVE
The MATCH_RECOGNIZE (MR) clause implements expressive row pattern recognition directly within
SQL queries. Despite its utility in time-series and data analysis, the adoption of this technology
remains limited due to inconsistent support across database systems. This thesis delineates a method-
ology for adapting predefined translation rules into Python code to implement a MATCH_RECOGNIZE
transpiler module. This objective is accomplished by converting an intermediate representation of
a MATCH_RECOGNIZE query into an equivalent representation using an Abstract Syntax Tree structure
that can be converted into recursive Common Table Expressions (CTEs). The generated results are
then evaluated in DuckDB, demonstrating that the transpiled WITH RECURSIVE (WR) query extends the
compatibility of the database system and faithfully replicates the behavior of MATCH_RECOGNIZE. The
present work aims to serve as a prototype for implementing row pattern matching within the SQL
language, thereby facilitating maintainable analytical queries.