mysql 留存率统计_【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;

小杰博客 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权

转载请注明原文链接：【mysql】统计订购，留存率，付费率数据分析