2 xieyuanxiang xieyuanxiang 于 2016.03.10 21:13 提问

SQL 求每月会员流失数量
sql

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

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

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

最后希望输出这样的结果

年月 该月会员流失数量

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

4个回答

danielinbiti
danielinbiti   Ds   Rxr 2016.03.11 01: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 年月
danielinbiti
danielinbiti 回复xieyuanxiang: 每行相同的数?比如上面测试数据中会员id是1的,在201009月就是流失,一致没消费
接近 2 年之前 回复
xieyuanxiang
xieyuanxiang 回复danielinbiti:结果不对。 生成一列cnt这里,你给每行加了一个相同的数什么意思。
接近 2 年之前 回复
danielinbiti
danielinbiti 回复xieyuanxiang: 增加6个月
接近 2 年之前 回复
xieyuanxiang
xieyuanxiang 谢谢! 请问一下 INTERVAL 6 MONTH 是返回一个什么值?
接近 2 年之前 回复
swdenglian
swdenglian   2016.03.10 21:28

年月 是个什么作用呢

xieyuanxiang
xieyuanxiang 最后输出需要每个年月对应一个当月的流失数量。 你如果觉得没必要,那也可以说说假如原表没有这个字段,该怎么做
接近 2 年之前 回复
xieyuanxiang
xieyuanxiang 最后输出需要每个年月对应一个当月的流失数量。 你如果觉得没必要,那也可以说说假如原表没有这个字段,该怎么做
接近 2 年之前 回复
Ty_o_yT
Ty_o_yT   2016.03.10 22:08

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

xieyuanxiang
xieyuanxiang 回复Ty_o_yT: 不是跟当前比较,是显示每个月的流失量,包括历史的,
接近 2 年之前 回复
Ty_o_yT
Ty_o_yT 回复xieyuanxiang: 我知道是注册日期啊 但是数据库可以获取当期日期啊 和最新的一条消费记录日期比较就行了
接近 2 年之前 回复
xieyuanxiang
xieyuanxiang 表里面的年月不是当前日期,你可以理解为会员的注册日期。 如果说你说的当前日期是输入参数,那出来的结果只是我输入的这个月的流失量,我要的是一个日期列表排列下来,每行对应当月流失量。
接近 2 年之前 回复
enpterexpress
enpterexpress   2016.03.10 22:51

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

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!