SQL统计次月复购率

 

-- 复购率
select zry 首次购买月份,zyhs 当月新增客户数,
max(case when fgy-zry=1 then fgyhs else null end) m1,
max(case when fgy-zry=2 then fgyhs else null end) m2,
max(case when fgy-zry=3 then fgyhs else null end) m3,
max(case when fgy-zry=4 then fgyhs else null end) m4,
max(case when fgy-zry=5 then fgyhs else null end) m5,
max(case when fgy-zry=6 then fgyhs else null end) m6,
max(case when fgy-zry=7 then fgyhs else null end) m7
from
(select zry,fgy,fgyhs,zyhs,round(fgyhs/zyhs,2) fgl
from 
(select t1.dt zry,t2.dt fgy,count(distinct t2.user_id) fgyhs 
from (select month(order_date) dt,user_id from order_detail where order_status='交易成功' group by month(order_date),user_id) t1 
join (select month(order_date) dt,user_id from order_detail where order_status='交易成功' group by month(order_date),user_id) t2 
on t1.user_id=t2.user_id and t1.dt < t2.dt 
group by t1.dt,t2.dt) a -- 号码加小于本身时间连接
join 
(select month(order_date) yf,count(distinct user_id) zyhs
from order_detail 
group by month(order_date)) b 
on a.zry=b.yf) a  group by zry,zyhs;

-- 复购人数
select zry 首次购买月份,zyhs 当月新增客户数,
max(case when fgy-zry=1 then fgl else null end) m1,
max(case when fgy-zry=2 then fgl else null end) m2,
max(case when fgy-zry=3 then fgl else null end) m3,
max(case when fgy-zry=4 then fgl else null end) m4,
max(case when fgy-zry=5 then fgl else null end) m5,
max(case when fgy-zry=6 then fgl else null end) m6,
max(case when fgy-zry=7 then fgl else null end) m7
from
(select zry,fgy,fgyhs,zyhs,round(fgyhs/zyhs,2) fgl
from 
(select t1.dt zry,t2.dt fgy,count(distinct t2.user_id) fgyhs 
from (select month(order_date) dt,user_id from order_detail where order_status='交易成功' group by month(order_date),user_id) t1 
join (select month(order_date) dt,user_id from order_detail where order_status='交易成功' group by month(order_date),user_id) t2 
on t1.user_id=t2.user_id and t1.dt < t2.dt 
group by t1.dt,t2.dt) a 
join 
(select month(order_date) yf,count(distinct user_id) zyhs
from order_detail 
group by month(order_date)) b 
on a.zry=b.yf) a  group by zry,zyhs;