【mysql】统计订购，留存率，付费率数据分析

drop table member_time_orders_temp;

create table member_time_orders_temp
select m.id
from 
member_time_orders m
where 
(
(m.date >= ‘2014-07-01’ and m.date < ‘2015-06-01’) 
)
and not EXISTS(select 1 from member_time_orders bm where bm.member_id = m.member_id and bm.date < m.date limit 1)
group by m.date,m.member_id;

select m.date,m.member_id,
(select ifnull(sum(m1.successful_order_number),0) from member_time_orders m1 where m1.member_id = m.member_id and m1.date >=m.date and m1.date < date_add(m.date, interval 30 day) group by m1.member_id)as 30_success_num,
(select ifnull(sum(m1.successful_order_number*ifnull(m1.price,0.1)),0) from member_time_orders m1 where m1.member_id = m.member_id and  m1.date >=m.date and m1.date < date_add(m.date, interval 30 day) group by m1.member_id)as 30_success_price,
(select ifnull(sum(m1.successful_order_number),0) from member_time_orders m1 where m1.member_id = m.member_id and  m1.date >=m.date and m1.date < date_add(m.date, interval 60 day) group by m1.member_id)as 60_success_num,
(select ifnull(sum(m1.successful_order_number*ifnull(m1.price,0.1)),0) from member_time_orders m1 where m1.member_id = m.member_id and  m1.date >=m.date and m1.date < date_add(m.date, interval 60 day) group by m1.member_id)as 60_success_price

from 
member_time_orders m
inner join member_time_orders_temp t ON m.id = t.id
group by m.date,m.member_id;