• 欢迎访问小杰博客网站
  • 欢迎访问小杰博客网站哦

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

未分类 小杰 4年前 (2015-12-15) 435次浏览 已收录 0个评论

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】统计订购,留存率,付费率数据分析
喜欢 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址