Democratize MATCH_RECOGNIZE!

Download PDF

Abstract

Row pattern matching in terms of the MATCH_RECOGNIZE clause is a powerful and relatively recent feature in SQL that allows users to de￿ne regular patterns over ordered rows in a table. As of today, few database systems o￿er support for match recognize , making it unaccessible to a wide range of users. We demonstrate the implementation of a transpiler that translates match recognize into a plain SQL query executable by any database system that supports window functions and recursive common table expressions—no changes to the underlying database systems are required. We evaluate the performance of this approach on the running example to show that the transpiler generates code competitive with contemporary database systems that implement row pattern matching natively. The on-site demonstration is based on DuckDB.