New post
Question
· Apr 6, 2018

sequence pattern querying like with Oracle's MATCH_RECOGNIZE

#Caché #SQL

Any insights, news, alternatives or experience about sequence pattern querying like with Oracle's MATCH_RECOGNIZE, more declarative, less simple direct COS way? Both obvious solutions (shadowing data to oracle cloud or implementing MATCH_RECOGNIZE compiler in COS from scratch) seem big overkill. Do Intersystems have plans to adopt this "2007 ANSI standard proposal" and "SQL:2016 standard"

My problem is regular need to do analytics on medical lab data dynamics and correlations, and while its always doable by COS and multiple SQL statements, such handcrafting for every new hypothesis/idea or statistic takes some hours each and definitely is not something that can be delegated to non-programmers.

Discussion (3)0
Log in or sign up to continue
Benjamin De Boe · May 4, 2018

We currently don't support analytic windowing functions (PARTITION BY syntax), but have been looking into it for a future release. MATCH_RECOGNIZE is certainly one of the more advanced ones in that bucket. Is this the very one you would need or do you have scenarios that would be served by core windowing functionality, excluding the pattern matching piece?

Or is it the pattern matching and not as much the windowing you're looking for?

0 0
Eriks Tauckels · May 4, 2018

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.

0 0