How Does the RQFP Algorithm Uncover Hidden Patterns in Data?

Imagine you want to know which products are bought together in a supermarket or which university courses are often taken by the same student. In data mining this is called "frequent pattern mining". Traditionally, algorithms like Apriori or FP‑Growth are used, but they have two drawbacks: they generate a huge number of candidate combinations and they consume a lot of memory, especially with very large tables.

The new Recursive Queried Frequent Patterns (RQFP) algorithm offers a different solution, based on recursive SQL queries. In practice, RQFP creates a relational mining table and, using recursive Common Table Expressions (CTEs), builds the most frequent itemsets without having to list all possible combinations beforehand.

The team implemented RQFP on Microsoft SQL Server and built a small C# web interface. Tests on an academic dataset show that RQFP finds the same patterns as Apriori and FP‑Growth, but with a considerably lower RAM usage and with results that are easier to interpret. Moreover, because the algorithm runs directly on the database, it is simpler to integrate into existing systems.

Future experiments aim to evaluate RQFP on health‑care and retail data, where the amount of information is massive. Although it is currently an educational version, the method demonstrates that pattern mining can become more scalable and "database‑native".

The core of the RQFP algorithm lies in the use of recursive Common Table Expressions (CTEs), an SQL feature introduced with SQL:1999 and supported by all major modern DBMSs. A recursive CTE allows you to define a base query (seed) and a recursion rule that iteratively adds new rows until a stop condition is met.

In frequent pattern mining, the seed query selects all single items whose support exceeds the minimum threshold. The recursion rule then combines already discovered items with subsequent ones, computes the support of the union, and retains only those combinations that surpass the threshold. This approach eliminates the explicit candidate generation phase typical of Apriori, drastically reducing the number of temporary tuples to store.

From a computational complexity perspective, RQFP still exhibits exponential growth tied to the number of frequent items, but the constant factor is much lower than Apriori because memory is managed by the DBMS query engine, which optimizes index and buffer usage. FP‑Growth, on the other hand, builds a prefix‑tree (FP‑tree) that requires a full dataset scan and a complex in‑memory structure; RQFP avoids this structure by leveraging the SQL server's parallel execution capabilities.

Another advantage is result transparency: each recursion level corresponds to an itemset length level, making it easier to trace the generation path of patterns. Moreover, since all operations occur entirely in SQL, you can exploit existing database security, logging, and transaction management features, simplifying integration into enterprise environments.

Current limitations include dependence on the DBMS's optimization capabilities and the need for proper indexing on item and support columns. In scenarios with millions of transactions and thousands of items, recursion depth can become a bottleneck; in such cases, it is advisable to combine RQFP with data partitioning techniques or hybrid approaches that pre‑filter the most promising items.

In summary, RQFP shows that frequent pattern mining can be performed natively within the database, reducing reliance on external data structures and improving memory efficiency while maintaining accuracy comparable to traditional methods.