SODBASE CEP学习（十五）：常见场景EPL示例

EX0001 统计10s的股票统计值，2s输出一次。

解决方法：输入定时触发器timer周期设置为2s

CREATE QUERY demo

SELECT T1._start_time_ AS starttime,average(T2.price) ASavg,T3._end_time_ AS endtime

FROM T1:timer,T2:模拟股票,T3:timer

PATTERN T1;T2^+;T3 

WHERE T1._start_time_=T3._start_time_-10000 

WITHIN 10000

EX0002统计每一只股票的10s的统计值,2s输出一次

解决方法：输入timer周期设置为2s

CREATE QUERY demo

SELECT T1,T2,T3

FROM T1:timer,T2:模拟股票,T3:timer

PATTERN T1;T2^+;T3 

Group BY T2.name

WHERE T1._start_time_=T3._start_time_-10000 

WITHIN 10000

EX0003每当股价超过阈值后，开启10秒窗口，统计10秒内股价超过阈值的事件（>80.0）。这10秒内的超过阈值事件，就不再开启新的10秒窗口。

CREATE QUERY delayoutput2

SELECT T1,T2,T3

FROM T1:stokIn,T2:stockIn,T3:delay10sec

PATTERN T1;T2^+;T3 

WHERE T1._start_time_=T3._start_time_-10000 

AND T1.price>80.0 

AND T2.price>80.0

WITHIN 10000 BATCHMODE

EX0004查询10分钟以内，单笔金额小于等于10元，大于等于2笔的交易卡号和金额

 (1) SODSQL

select T1.cardid AS T1_cardid,T1.value AS T1_value,tostring(T2.value)AS T2_value,T3.value AS T3_value

from T1:Event,T2:Event,T3:Event

PATTERN T1;T2^*;T3

GROUP By T1.cardid

WHERE T1.value<=10 AND T3.value<=10 ANDT1.cardid=T3.cardid AND T1.cardid=T2.cardid AND T2.value<=10

WITHIN 36000000

（2）参考Esper

select cardid

from Event.win:time(10 min)

where value<=10

group by cardid

having count(*)>=2

 

P00003检测短时间内，同一账号连续发生接近授权金额的交易

SODSQL语句：

CREATE QUERY P00003

SELECT T1,T2,T3

FROM T1: 模拟银行交易数据,T2:模拟银行交易数据,T3:模拟银行交易数据PATTERN T1;T2^*;T3 

WHERE T2.amt<50000 AND T2.amt>45000

AND T3.amt<50000 AND T3.amt>45000 

AND T1.amt<50000 AND T1.amt>45000

AND T1.Account=T2.Account 

AND T2.Account=T3.Account 

WITHIN 11000

P00004：同一个账户连续进行的大额转账相反交易

SODSQL语句：

CREATE QUERY P00004

SELECT T1,T2 FROM T1:模拟银行交易数据,T2:非法交易2

PATTERN T1;T2 

WHERE T1.Account = T2.Account

AND T1.trans_flag='in'

AND T2.trans_flag='out' 

WITHIN 500

P00023：检测短时间内柜面现金交易，防止将用户的“先取后存”业务办理为“先存后取”

SODSQL语句：

CREATE QUERY P00023

SELECT T1,T2 FROM T1:模拟银行交易数据,T2:非法交易2

PATTERN T1;T2 

WHERE T1.Account = T2.Account 

AND T2.dc_flag='C'

AND T1.dc_flag = 'A'

AND T1.cash_trans_flag= '1'

AND T2.cash_trans_flag = '1' 

WITHIN 1000

P00101监测是否存在当日现金出现大收大付

SODSQL语句：

CREATE QUERY P00101

SELECT T1,T2

FROM T1:模拟银行交易数据,T2:模拟银行交易数据

PATTERN T1&T2

WHERE T1Account=T2.Account 

WITHIN 86400000

OLP0001检测"用户确认支付"后,没有"银行转账成功"，就"出货"

SODSQL语句：

CREATE QUERY OLP0001

SELECT T1,T2,T3

FROM T1:在线支付模拟数据,T2:在线支付模拟数据,T3:在线支付模拟数据 PATTERN T1;!T2;T3 

WHERE T1.userid=T3.userid

AND T2.userid=T3.userid

AND T1.message='verifypay'

AND T2.message='bankpaycomplete'

AND T3.message ='productrelease'

AND T1.productid=T3.productid

AND T2.productid=T3.productid 

WITHIN 20000

OLP0002"用户确认支付"后，"银行转账成功"，3秒内没有"出货"

SODSQL语句：

CREATE QUERY OLP002

SELECT  T1,T2,T3,T4

FROM T1:onlinepayment,T2:onlinepayment,T3:onlinepayment,T4:timer PATTERNT1;(T2;!T3;T4)

WHERE T2.userid=T3.userid

AND T2.userid=T1.userid

AND T1.message='verifypay'

AND T2.message='bankpaycomplete'

AND T3.message ='productrelease'

AND T3.productid=T1.productid

AND T3.productid=T2.productid

AND T4._start_time_-T2._start_time_> 1000 WITHIN 2000

EX0005查询30s内输入不同密码的次数大于5的用户

解决方法：输入定时触发器timer周期设置为30s

CREATE QUERY demo

SELECT T1._start_time_ AS starttime,average(T2.price) ASavg,T3._end_time_ AS endtime

FROM T1: 登录,T2:登录,T3: 登录

PATTERN T1;T2^+;T3 

GROUP BY T2.name

WHERE countdistinct(T2.password)>3

AND T1.name=T2.name AND T3.name=T1.name

WITHIN 30000

EX0006 当reader 读到标签后，在1s内该标签还在则认为是连续读到该标签，2s后该标签还存在，则出发进入通道事件，进入通道后，如果2s内reader 未读到标签则触发离开事件

 

“读到标签ReadSuccessiveEvent”

CREATE QUERY readsuccessive

SELECT 'readsuccessive' AS type, T1.id AS T1_id,T1.num AS T1_num,T2.id AST2_id,T2.num AS T2_num

FROM T1:readerevent,T2:readerevent

PATTERN T1;T2 

WHERE T1.id=T2.id 

WITHIN 1000

 

 

"进入通道"readenterchannel

CREATE QUERY readenterchannel

SELECT 'enterchannel' AS type,T3.id AS T3_id,T3.num AS num FROMT1:readerevent,T2:readerevent,T3:readerevent

PATTERN T1;T2;T3 

WHERE T1.id=T2.id

AND T1.id=T3.id

AND T2._end_time_-T1._end_time_<=1000

AND T3._end_time_-T2._end_time_<2000

AND T3._end_time_-T1._end_time_>1000  

WITHIN 3000

 

"离开事件" readoutofchannel

CREATE QUERY readoutofchannel2

SELECT T1.id AS T1_id,T1.num AS T1_num,'outofchannel' AS type

FROM T1:readerevent,T2:readerevent,T3:delay2sectimer

PATTERN T1;!T2;T3 

WHERE T1.id=T2.id AND T3._end_time_-T1._end_time_=2000 

WITHIN 2000

EX0007 查询哪个卡号1小时内的总交易金额大于1w

CREATE QUERY pos

SELECT T1.acctnum AS acctum,T1.value AST1_value,tostring(T2.value) AS T2.value,T3.value AS T3_value

 FROM T1: pos,T2:pos,T3:pos

PATTERN T1;T2^+;T3 

GROUP BY T2.acctnum

WHERE T1. acctnum =T2. acctnum

AND T3. acctnum =T1. acctnum

AND T1.value+T2.value+sum(T3.value)>10000

WITHIN 10000