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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle-利用解析函数计算连续、回流，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含1674字，纯文字阅读大概需要3分钟。

内容图文

 

---最大连续交易天数
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

 

内容总结

以上是互联网集市为您收集整理的Oracle-利用解析函数计算连续、回流全部内容，希望文章能够帮你解决Oracle-利用解析函数计算连续、回流所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---