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