锐客网

  1. 首页 > it技术 > >

超级复杂sql|超级复杂sql with as join on

2019独角兽企业重金招聘Python工程师标准>>> 超级复杂sql|超级复杂sql with as join on
文章图片

---规则2----
/*风险返回码交易*/
/*同一卡号同终端近半小时内，交易笔数澹(含成功及失败)其中任一笔含有风险返回码(扩充后的风险返回码)*/
/*排除以下三种情况*/
/*a.同卡号连续返回码为51且时间跨度在5分钟之内的交易*/
/*b.同卡号连续返回码为55且时间跨度在5分钟之内的交易*/
/*c.同卡号连续返回码为61且时间跨度在5分钟之内的交易*/

select sa_slmt_mcht_id from
(
select sa_slmt_mcht_id
from tbl_txn_mon1
wheretm_rec_crt_time between to_date('2016030300', 'yyyymmddhh24') - 30/24/60 andto_date('2016030400', 'yyyymmddhh24')
group by sa_slmt_mcht_id
having count(sa_slmt_mcht_id) > 3
) a
whereexists(
select sa_slmt_mcht_id from tbl_txn_mon1 b
where a.sa_slmt_mcht_id=b.sa_slmt_mcht_id
and b.sa_rsp_code1 in ('03','04','14','15','21','34','38','40','41','43','45','51','54','55','57','58','59,''61','62','65','75','; Y','; 3')
)
minus
select distinctsa_slmt_mcht_id from tbl_txn_mon1 a
where tm_rec_crt_time between to_date('2016030300', 'yyyymmddhh24') - 30/24/60 andto_date('2016030400', 'yyyymmddhh24')
and a.sa_rsp_code1 = '55'
group by sa_slmt_mcht_id,a.sa_slmt_pri_acct
having max(tm_rec_crt_time)-min(tm_rec_crt_time) <(5/60/24)
minus
select distinctsa_slmt_mcht_id from tbl_txn_mon1 a
where tm_rec_crt_time between to_date('2016030300', 'yyyymmddhh24') - 30/24/60 andto_date('2016030400', 'yyyymmddhh24')
and a.sa_rsp_code1 = '51'
group by sa_slmt_mcht_id,a.sa_slmt_pri_acct
having max(tm_rec_crt_time)-min(tm_rec_crt_time) <(5/60/24)
minus
select distinctsa_slmt_mcht_id from tbl_txn_mon1 a
where tm_rec_crt_time between to_date('2016030300', 'yyyymmddhh24') - 30/24/60 andto_date('2016030400', 'yyyymmddhh24')
and a.sa_rsp_code1 = '61'
group by sa_slmt_mcht_id,a.sa_slmt_pri_acct
having max(tm_rec_crt_time)-min(tm_rec_crt_time) <(5/60/24)
--
-----规则4------------
/*故意降级尝试*/
/*同终端同卡号上一笔为降级交易45 单笔为正常
IC 卡交易，关停商户*/
-----二次修改 rule4- succ---------------------------


with t2 as (
select a.sa_slmt_mcht_id mid,a.sa_slmt_term_id tid,a.sa_slmt_pri_acct acc,a.sa_rsp_code1 rsp0,b.sa_rsp_code1 rsp1,a.tm_rec_crt_time tm0,b.tm_rec_crt_time tm1 from tbl_txn_mon1 a join tbl_txn_mon1 b ona.sa_slmt_mcht_id=b.sa_slmt_mcht_id anda.sa_slmt_term_id=b.sa_slmt_term_id anda.sa_slmt_pri_acct=b.sa_slmt_pri_acct where a.tm_rec_crt_time>b.tm_rec_crt_time
order by a.sa_slmt_mcht_id,a.sa_slmt_term_id,a.sa_slmt_pri_acct,a.tm_rec_crt_time
),
t3 as (
select mid,tid,acc,tm0,max(tm1) tm2 from t2 group by mid,tid,acc,tm0
)
select distinct mid from (
select a.mid,a.tid,a.acc,a.rsp0,a.rsp1,a.tm0,a.tm1,b.tm2from t2 a join t3 b ona.mid=b.mid anda.tid=b.tid and a.acc=b.acc and a.tm0=b.tm0
where tm1=tm2 and rsp1='45'
)
----
----------------------
-----规则5------------
/*临近交易差值过大*/
--rule 5
--同终端上一笔交易10<金额<30,当笔-上笔>10 0000 ,排除指定mcc
-----------三次修改 规则5- succ-----------
with t1 as (
select a.sa_slmt_mcht_id mid,a.sa_slmt_term_id tid,a.sa_txn_amount m,a.tm_rec_crt_time time0,b.sa_txn_amount m1,b.tm_rec_crt_time time1 from tbl_txn_mon1 a join tbl_txn_mon1 b on a.sa_slmt_mcht_id=b.sa_slmt_mcht_id and a.sa_slmt_term_id=b.sa_slmt_term_id where a.tm_rec_crt_time>b.tm_rec_crt_time
order by mid,tid,time0,m
)
select distinct mid from (
select t3.mid,t3.tid,t3.m,t3.time0,t3.m1,t3.time1,t2.time3 from t1 t3 join (select mid,tid,m,time0,max(time1) time3 from t1 group by mid,tid,m,time0) t2 on t2.mid=t3.mid and t2.tid=t2.tid and t2.m=t3.m and t2.time0=t3.time0
where time1=time3 and t3.m1 between 10 and 30 and t3.m-t3.m1>100000
)
-----------------
----------------------
-----规则6------------
/*短时间小额测试*/
--rule 6
select a.sa_slmt_mcht_id from tbl_txn_mon1 a
where N_txn_type in ('5998','5173','5045','5094','5722')
and a.sa_txn_amount between 5 and 25
and tm_rec_crt_time between to_date('2016030310', 'yyyymmddhh24') - 10/24/60 andto_date('2016030310', 'yyyymmddhh24')
group by a.sa_slmt_mcht_id
having count(*)>3
----------------------
【超级复杂sql|超级复杂sql with as join on】-----规则7------------
--rule 7
/*异常密码错*/
-------二次修改 rule 7succ------------------
with t as(
select a.sa_slmt_mcht_id mid,a.sa_slmt_pri_acct acct,a.sa_txn_amount m0,a.tm_rec_crt_time tm0,b.sa_txn_amount m1,b.tm_rec_crt_time tm1 from tbl_txn_mon1 a join tbl_txn_mon1 b on a.sa_slmt_mcht_id=b.sa_slmt_mcht_id and a.sa_slmt_pri_acct=b.sa_slmt_pri_acctwhereb.sa_rsp_code1 = '55' and a.tm_rec_crt_time>b.tm_rec_crt_time
order by a.sa_slmt_mcht_id,a.sa_slmt_pri_acct,a.sa_txn_amount,a.tm_rec_crt_time
),
t1 as(
selectmid,acct,m0,tm0,max(tm1) tm2 from t group by mid,acct,m0,tm0
)


selectdistinct mid from (
select a.mid,a.acct,a.m0,a.tm0,a.m1,a.tm1,b.tm2 from t a join t1 b on a.mid=b.mid and a.acct=b.acct and a.tm0=b.tm0
where a.tm1=b.tm2 and a.m0<>a.m1
)
-----------------
----------------------
-----规则8------------
--rule 8
/*日交易金额突增*/
with t30 as (
select sa_slmt_mcht_id,sum(b.sa_txn_amount)/30 sum30 from tbl_txn_mon1 b
where b.tm_rec_crt_time between to_date('20160303235959', 'yyyymmddhh24miss') - 30 andto_date('20160303235959', 'yyyymmddhh24miss')
group by b.sa_slmt_mcht_id
),
t1 as (
select sa_slmt_mcht_id,sum(sa_txn_amount) sum1 from tbl_txn_mon1 a
where tm_rec_crt_time between to_date('20160303235959', 'yyyymmddhh24miss') - 1 andto_date('20160303235959', 'yyyymmddhh24miss')
group by a.sa_slmt_mcht_id
)
--select a.sa_slmt_mcht_id,a.sum1,b.sum30 from t1 a,t30 b where a.sa_slmt_mcht_id=b.sa_slmt_mcht_id and a.sum1>b.sum30
select distinct a.sa_slmt_mcht_id from t1 a join t30 b on a.sa_slmt_mcht_id =b.sa_slmt_mcht_id where sum1>sum30*3


----------------------
-----规则9------------
/*日交易量突增*/
--rule 9
with t30 as (
select sa_slmt_mcht_id,count(*)/30 sum30 from tbl_txn_mon1 b
where b.tm_rec_crt_time between to_date('20160303235959', 'yyyymmddhh24miss') - 30 andto_date('20160303235959', 'yyyymmddhh24miss')
group by b.sa_slmt_mcht_id
),
t1 as (
select sa_slmt_mcht_id,count(*) sum1 from tbl_txn_mon1 a
where tm_rec_crt_time between to_date('20160303235959', 'yyyymmddhh24miss') - 1 andto_date('20160303235959', 'yyyymmddhh24miss')
group by a.sa_slmt_mcht_id
)
select a.sa_slmt_mcht_id from t1 a join t30 b on a.sa_slmt_mcht_id =b.sa_slmt_mcht_id where sum1>sum30*3


----------------------
-----规则11-----------
/*凌晨交易*/
--rule 11
select distinct sa_slmt_mcht_id from tbl_txn_mon1
where tm_rec_crt_time between to_date('20160303120000', 'yyyymmddhh24miss') - 30/60/24 andto_date('20160303120000', 'yyyymmddhh24miss')
and n_txn_type not in('7011','7012','5812','5813','5814','7911','5541','5542','4784','5912','7297','7298','7832','5331','7999','8011','8062')
group by sa_slmt_mcht_id
having count(*) > 20and avg(sa_txn_amount)>1000
union
select distinct sa_slmt_mcht_id from tbl_txn_mon1 b
where tm_rec_crt_time between to_date('20160303120000', 'yyyymmddhh24miss') - 30/60/24 andto_date('20160303120000', 'yyyymmddhh24miss')
and n_txn_type not in('7011','7012','5812','5813','5814','7911','5541','5542','4784','5912','7297','7298','7832','5331','7999','8011','8062')
group by sa_slmt_mcht_id,b.sa_slmt_pri_acct
having count(*) > 15


--select * from tbl_txn_mon1
--where tm_rec_crt_time between to_date('20160303120000', 'yyyymmddhh24miss') - 30/60/24 andto_date('20160303120000', 'yyyymmddhh24miss')
--and n_txn_type not in('7011','7012','5812','5813','5814','7911','5541','5542','4784','5912','7297','7298','7832','5331','7999','8011','8062')
--and sa_txn_amount>300000
----------------------
-----规则12-----------
--rule 12 延缓入账
/*出现34 41 43 57 59 62 的交易返回码，后续出现交易金额大于30万的交易，
此交易自动延缓入账，次日交单手工入账*/
--rule 12 延缓入账
select distinct sa_slmt_mcht_id from tbl_txn_mon1 a
where a.sa_txn_amount>300000
and exists(
select count(*) from tbl_txn_mon1 b
where b.sa_slmt_mcht_id=a.sa_slmt_mcht_id
and b.tm_rec_crt_time < a.tm_rec_crt_time
and b.sa_rsp_code1 in ('34','41','43','57','59','62')
)
--------
----------------------

--
select * from tbl_merch_risklog


-----------二次修改-----------








转载于:https://my.oschina.net/liuguoyao/blog/661828

    推荐阅读


    上一篇：Linux下部署SSH登录时的二次身份验证环境记录（利用Google|Linux下部署SSH登录时的二次身份验证环境记录（利用Google Authenticator）

    下一篇：HttpClient4.5实现登陆和跳转