xieyuanxiang
xieyuanxiang
采纳率100%
2016-03-10 13:13 阅读 2.5k

SQL 求每月会员流失数量

1000

一个会员6个月以上没有消费记录,则在该月算做一个流失会员。

一张表,有三个字段 年月(YYYYMM) 会员ID 会员消费日期(YYYYMM)

一个年月有多个会员,一个会员有多个消费日期,同一个会员只有一个年月

最后希望输出这样的结果

年月 该月会员流失数量

求做法。伪代码或者文字描述清楚一点就行。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

4条回答 默认 最新

  • 已采纳
    danielinbiti danielinbiti 2016-03-10 17:01

    1、建日期辅助表。
    2、根据消费表算需要消费截止日期。
    3、统计这消费区间内是否有消费记录。
    4、根据统计的消费区间记录记录是0,统计当月的流失会员。

    以下是mysql的写法
     create table PDetail(
       DateValue varchar(8),
       UserId varchar(20),
       CostValue varchar(8)
    );
    create table YearMonth(
       DateValue varchar(8)
    );
    insert into PDetail(DateValue,UserId,CostValue) values('201001','1','201001');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','1','201002');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','1','201003');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','2','201001');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','2','201002');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','2','201003');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','2','201008');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','2','201101');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','2','201105');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','3','201001');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','3','201002');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','3','201003');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','4','201001');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','4','201002');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','4','201007');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','5','201008');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','5','201009');
    insert into PDetail(DateValue,UserId,CostValue) values('201001','5','201010');
    
    insert into YearMonth(DateValue) values('201001');
    insert into YearMonth(DateValue) values('201002');
    insert into YearMonth(DateValue) values('201003');
    insert into YearMonth(DateValue) values('201004');
    insert into YearMonth(DateValue) values('201005');
    insert into YearMonth(DateValue) values('201006');
    insert into YearMonth(DateValue) values('201007');
    insert into YearMonth(DateValue) values('201008');
    insert into YearMonth(DateValue) values('201009');
    insert into YearMonth(DateValue) values('201010');
    insert into YearMonth(DateValue) values('201011');
    insert into YearMonth(DateValue) values('201012');
    insert into YearMonth(DateValue) values('201101');
    insert into YearMonth(DateValue) values('201102');
    insert into YearMonth(DateValue) values('201103');
    insert into YearMonth(DateValue) values('201104');
    insert into YearMonth(DateValue) values('201105');
    insert into YearMonth(DateValue) values('201106');
    insert into YearMonth(DateValue) values('201107');
    insert into YearMonth(DateValue) values('201108');
    insert into YearMonth(DateValue) values('201109');
    
    
    SELECT YearMonth.DateValue 年月,IFNULL(CNT,0) 该月会员流失数量 FROM  YearMonth left join  (
    SELECT DateValue, count(*) CNT from (
    SELECT YearMonth.*,P.UserId from YearMonth left join (
    SELECT P.*,(SELECT COUNT(*) FROM PDetail 
      WHERE PDetail.UserId=P.UserId 
       and PDetail.CostValue>P.CostValue 
       and PDetail.CostValue<=P.NeedCostDate) cnt
    from (
    SELECT P.*,date_format(
    DATE_ADD(
       str_to_date(CONCAT(P.CostValue,'01'), '%Y%m%d')
    ,INTERVAL 6 MONTH)
    ,'%Y%m') NeedCostDate
     FROM PDetail P
    ) P
    ) p ON YearMonth.DateValue = P.NeedCostDate and p.cnt=0
    ) p where p.userid is not null group by datevalue
    ) P ON YearMonth.DateValue = P.DateValue ORDER BY 年月
    
    点赞 1 评论 复制链接分享
  • swdenglian swdenglian 2016-03-10 13:28

    年月 是个什么作用呢

    点赞 评论 复制链接分享
  • Ty_o_yT Ty_o_yT 2016-03-10 14:08

    查询每个会员消费日期,倒序排列 找第一个 然后和当前日期比较 大于6个月的就算作是

    点赞 评论 复制链接分享
  • enpterexpress 把分全给哥 2016-03-10 14:51

    可以理解为消费记录表中每一段时间(六个月)内,会员是否有记录,统计问题

    点赞 评论 复制链接分享

相关推荐