Undeniably. But that is exact reason, why migration is no longer possible - project is running 24x7, with 3TB data, modified system classes, ~1000 classes and 2000+ fine tuned (for very exact COS plan) queries. There were attempt at 2013 but failed, mainly because of changes in SQL optimization.

But system is running fine. This was more theoretical question - perhaps something can be tuned on vmware or windows level - what cache process can be doing between iops and COS interpreter. 1 job - 25000 IOPS <1% CPU, 2 jobs - 50000 IOPS <1% CPU, 4 jobs - 100000 IOPS <1% CPU. As system is usually running more than 20 IOPS hungry jobs, result is good SSD load. Just wondering what state sits single process on idle system.

Syntax is not so important, PARTITION BY is kinda supported by %FOREACH, or there is also planned RANK,  NTILE and, FIRST, LAST, LAG, LEAD with ORDER BY and WHERE in window? That would help, but yes, really I'm looking for pattern matching, not in regex sense, but as compact syntax to work on whole window.  For each window row to be able access other same window rows with aggregates like
LAST(x %FOREACH(window ORDER dat) WHERE y<THIS.y) > x
just MATCH_RECOGNIZE seams better as already well defined syntax.

Right now I'm stuck in 5.0.14 anyway, many reasons, one of them many hundreds hand-optimized queries (real time working on edge of performance) with too much work to re-optimize them in newer compiler, plenty of other reasons too. But still could shadow data to modern Cache (already running  for other tasks) for analytics, if it could give advanced querying ability.