One more obscure syntax

As 10g introduced MODEL, which is mostly used to impress your colleagues but seldom used in production, 12c has a new syntax, MATCH_RECOGNIZE

I gave it a first try to recognize trends in EMPs salaries over hire date.


SELECT ename, hiredate, sal, trend
FROM emp 
MATCH_RECOGNIZE (
  ORDER BY hiredate
  MEASURES CLASSIFIER () AS TREND
  ALL ROWS PER MATCH
  PATTERN (FIRST * Better * Worst * Same *)
  DEFINE FIRST AS ROWNUM = 1,
  Better AS Better.sal > PREV (sal),
  Same AS Same.sal = PREV (sal),
  Worst AS Worst.sal < PREV (sal));


ENAME      HIREDATE          SAL TREND                         
---------- ---------- ---------- ------------------------------
SMITH      1980-12-17        800 FIRST                         
ALLEN      1981-02-20       1600 BETTER                        
WARD       1981-02-22       1250 WORST                         
JONES      1981-04-02       2975 BETTER                        
BLAKE      1981-05-01       2850 WORST                         
CLARK      1981-06-09       2450 WORST                         
TURNER     1981-09-08       1500 WORST                         
MARTIN     1981-09-28       1250 WORST                         
KING       1981-11-17       5000 BETTER                        
JAMES      1981-12-03        950 WORST                         
FORD       1981-12-03       3000 BETTER                        
MILLER     1982-01-23       1300 WORST                         
SCOTT      1987-04-19       3000 BETTER                        
ADAMS      1987-05-23       1100 WORST                         

One thought on “One more obscure syntax”

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>