some where after OOW, i got this deck "12c_PatternMatching_CON9101_Laker-SQL - the best development language for Big Data.ppt" downloaded, but dont know if it is still available for download.
Slide no 43 has an example for SQL Pattern matching example for "Suspicious Money Transfers"
set feedback off
drop table t purge;
create table t(tstmp date, user_id varchar2(10),event varchar2(20),amt int);
insert into t values(to_date('01-Jan-2012','dd-mon-yyyy') ,'John','Deposit',1000000);
insert into t values(to_date('02-Jan-2012','dd-mon-yyyy') ,'John','Transfer',1000);
insert into t values(to_date('05-Jan-2012','dd-mon-yyyy') ,'John','Withdrawal',2000);
insert into t values(to_date('10-Jan-2012','dd-mon-yyyy') ,'John','Transfer',1500);
insert into t values(to_date('20-Jan-2012','dd-mon-yyyy') ,'John','Transfer',1200);
insert into t values(to_date('25-Jan-2012','dd-mon-yyyy') ,'John','Deposit',1200000);
insert into t values(to_date('27-Jan-2012','dd-mon-yyyy') ,'John','Transfer',1000000);
insert into t values(to_date('02-Feb-2012','dd-mon-yyyy') ,'John ','Deposit',500000);
commit;
set feedback on
<Requirment>
Detect suspicious money transfer pattern for an account
Three or more small amount (<1K) money transfers within 30 days
Subsequent large transfer (>=1M) within 10 days of last small transfer.
Report account, date of first small transfer, date of last large transfer
</Requirment>
Here is the query from that slide to handle this requirement.
SELECT *
FROM t MATCH_RECOGNIZE
( PARTITION BY user_id
ORDER BY tstmp
measures
first(x.tstmp) first_t,
y.tstmp last_t,
y.amt amts
PATTERN ( (; X ) {3,} Y )
DEFINE X AS (event = 'Transfer' AND amt < 1000 AND
last(X.tstmp) - first(X.tstmp) < 30)
Y AS (event = 'Transfer' AND amt > 1000000 AND
y.tstmp - last(x.tstmp) < 10 ) ) but when i run this it gave me this error. could you help me with resolving this error ?
rajesh@ORA12C>
rajesh@ORA12C> SELECT *
2 FROM t MATCH_RECOGNIZE
3 ( PARTITION BY user_id
4 ORDER BY tstmp
5 measures
6 first(x.tstmp) first_t,
7 y.tstmp last_t,
8 y.amt amts
9 PATTERN ( (; X ) {3,} Y )
10 DEFINE X AS (event = 'Transfer' AND amt < 1000 AND
11 last(X.tstmp) - first(X.tstmp) < 30)
12 Y AS (event = 'Transfer' AND amt > 1000000 AND
13 y.tstmp - last(x.tstmp) < 10 ) )
14 /
PATTERN ( (; X ) {3,} Y )
*
ERROR at line 9:
ORA-00931: missing identifier
rajesh@ORA12C>