Oracle-利用解析函数计算连续、回流

 

---最大连续交易天数
select t2.customer_no,max(t2.co) 
from 
(select 
t1.customer_no,t1.yp-t1.rn rk,count(1) co
  from (select ctd.customer_no,
               ctd.order_time yp,
               row_number() over(partition by ctd.customer_no order by ctd.order_time) rn
          from posp_boss.customer_trans_day ctd
          where ctd.order_time >= to_date('20180101', 'yyyymmdd')
          order by ctd.customer_no,ctd.order_time)t1
          group by t1.customer_no,t1.yp-t1.rn)t2
          group by t2.customer_no

 

---计算回流状况
with t1 as
(select ctd.customer_no,
               ctd.order_time yp,
               row_number() over(partition by ctd.customer_no order by ctd.order_time) rn
          from posp_boss.customer_trans_day ctd
          where ctd.order_time >= to_date('20180101', 'yyyymmdd')
          order by ctd.customer_no,ctd.order_time)
          
          
select t1.customer_no,max(ceil(t2.yp-t1.yp)) ypc   
from        
(select t1.customer_no,t1.yp,t1.rn-1 rm
from t1) t2
left join t1 on t1.customer_no = t2.customer_no and t1.rn = t2.rm
group by t1.customer_no

---计算回流涉及天数，商户，交易量
with t1 as
(select ctd.customer_no,
               ctd.order_time yp,
               row_number() over(partition by ctd.customer_no order by ctd.order_time) rn
          from posp_boss.customer_trans_day ctd
          where ctd.order_time >= to_date('20180101', 'yyyymmdd')
          order by ctd.customer_no,ctd.order_time)
          
select 
t3.ypc,
count(distinct t3.customer_no) mt,
sum(t4.amt) amount
from 
(select t1.customer_no,max(ceil(t2.yp-t1.yp)) ypc   
from        
(select t1.customer_no,t1.yp,t1.rn-1 rm
from t1) t2
left join t1 on t1.customer_no = t2.customer_no and t1.rn = t2.rm
group by t1.customer_no)t3
join 
(select ctd.customer_no,
          sum(ctd.trans_amount) amt 
          from posp_boss.customer_trans_day ctd
          where ctd.order_time >= to_date('20180101', 'yyyymmdd')
          group by ctd.customer_no)t4 on t3.customer_no = t4.customer_no
group by t3.ypc