Support an option for ranking queries to use window functions #19646

Currently ranking queries in Matomo are generated using a iterative counter method, this works well in MySQL but isn't well supported for other database types and can suffer from performance issues.

MySQL 8.0+ introduces new window functions which offer standardization and improved performance for ranking queries by allowing query planner optimizations. Specifically the RANK() and ROW_NUMBER() functions can be used to replace counters.

Windows functions are only supported for MySQL in version 8.0+, so old style counter queries will still need to be generated for MySQL 5.5 - 5.7.

Suggested approach

• Add a PDO adapter interface boolean option supportsWindowFunctions, the standard Matomo MySQL PDO adapter should return false. Future PDO adapters for alternative databases that support windows functions can return true.

• Make the core/DataAccess/RankingQuery class aware of the PDO adapter being used (via dependency injection?)

• Have the RankingQuery class check the PDO adapter supportsWindowFunctions option and either generate a counter style ranking query using existing code or generate a query using the new window functions.

• Code using the RankingQuery class should not need to be aware of whether a counter or window function ranking query is being generated (note: the goals by page sub-query might be an exception to this).

• There should be no change at all to ranking queries generated when using the standard Matomo MySQL PDO adapter.

• A MySQL8 PDO adapter descendant of the standard MySQL PDO adapter could be created to override this option and allow window function usage on MySQL 8.