超级复杂sql with as join on

2019独角兽企业重金招聘Python工程师标准>>>

  ---规则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 

    where  tm_rec_crt_time between to_date('2016030300', 'yyyymmddhh24') - 30/24/60 and  to_date('2016030400', 'yyyymmddhh24')

    group by sa_slmt_mcht_id

    having count(sa_slmt_mcht_id) > 3

    ) a

    where   exists(

           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 distinct  sa_slmt_mcht_id from tbl_txn_mon1 a

    where tm_rec_crt_time between to_date('2016030300', 'yyyymmddhh24') - 30/24/60 and  to_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 distinct  sa_slmt_mcht_id from tbl_txn_mon1 a

    where tm_rec_crt_time between to_date('2016030300', 'yyyymmddhh24') - 30/24/60 and  to_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 distinct  sa_slmt_mcht_id from tbl_txn_mon1 a

    where tm_rec_crt_time between to_date('2016030300', 'yyyymmddhh24') - 30/24/60 and  to_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 on  a.sa_slmt_mcht_id=b.sa_slmt_mcht_id and  a.sa_slmt_term_id=b.sa_slmt_term_id and  a.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.tm2  from t2 a join t3 b on  a.mid=b.mid and  a.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 and  to_date('2016030310', 'yyyymmddhh24')

group by a.sa_slmt_mcht_id

having count(*)>3

----------------------

-----规则7------------

--rule 7

 /*异常密码错*/

-------二次修改 rule 7  succ------------------

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_acct   where  b.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(

select  mid,acct,m0,tm0,max(tm1) tm2 from t group by mid,acct,m0,tm0

)

select  distinct 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 and  to_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 and  to_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 and  to_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 and  to_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 and  to_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(*) > 20   and 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 and  to_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 and  to_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