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