MATCH_RECOGNIZE - Fraud demo for OracleCODE events

 

This is a simple demo showing how to use SQL pattern matching for fraud analysis. It is part of a presentation for the Oracle CODE events program for developers.

In Database 12c there is a new SQL construct MATCH_RECOGNIZE for finding rows matching a pattern across a sequence of rows, using a regular expressions.

Pattern matching using SQL is based around four logical concepts:

• Logically partition and order the data/li>
• Define pattern using regular expression and pattern variables/li>
• Regular expression is matched against a sequence of rows/li>
• Each pattern variable is defined using conditions on rows and aggregates

irst step is to create the JSON table that will hold our transaction log. The log will provide the details of the time, account/user id, type of event (deposit or transfer) and the transaction amount.

Oracle Database 12c supports storing JSON documents inside the database. Use following code to create a table to store the transaction log which is in JSON format

CREATE TABLE json_transactions 
(transaction_doc CLOB, 
 CONSTRAINT "VALID_JSON" CHECK (transaction_doc IS JSON) ENABLE
);
SELECT 
  j.transaction_doc.time_id as time_id, 
  j.transaction_doc.user_id as user_id,
  j.transaction_doc.event_id as event_id,   
  j.transaction_doc.trans_amount as amount
FROM json_transactions j;
Here is the list of transfer transactions that we are of interest for our analysis
SELECT 
       TO_DATE(j.transaction_doc.time_id, 'DD-MON-YYYY') as time_id, 
       j.transaction_doc.user_id as user_id,
       j.transaction_doc.event_id as event_id,   
       to_number(j.transaction_doc.trans_amount) as amount
     FROM json_transactions j
     WHERE  j.transaction_doc.event_id = 'Transfer';

SELECT user_id, first_t, last_t, big_amount 
FROM (SELECT 
       TO_DATE(j.transaction_doc.time_id, 'DD-MON-YYYY') as time_id, 
       j.transaction_doc.user_id as user_id,
       j.transaction_doc.event_id as event_id,   
       j.transaction_doc.transfer_id as transfer_id,   
       TO_NUMBER(j.transaction_doc.trans_amount) as amount
     FROM json_transactions j
     WHERE  j.transaction_doc.event_id = 'Transfer')
MATCH_RECOGNIZE(
 PARTITION BY user_id 
 ORDER BY time_id
 MEASURES 
   FIRST(x.time_id) AS first_t, 
   LAST(y.time_id) AS last_t, 
   y.amount AS big_amount
 ONE ROW PER MATCH
 PATTERN (X{3,} Y)
 DEFINE
   X as (amount < 2000) AND 
         LAST(time_id) - FIRST(time_id) < 30,
   Y as (amount >= 1000000) AND 
         time_id - LAST(x.time_id) < 10);