oracle数据回流,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

标签：customer,no,t1,ctd,time,Oracle,回流,解析,order

来源： https://www.cnblogs.com/daoren/p/10461615.html