一个会员6个月以上没有消费记录,则在该月算做一个流失会员。
一张表,有三个字段 年月(YYYYMM) 会员ID 会员消费日期(YYYYMM)
一个年月有多个会员,一个会员有多个消费日期,同一个会员只有一个年月
最后希望输出这样的结果
年月 该月会员流失数量
求做法。伪代码或者文字描述清楚一点就行。
一个会员6个月以上没有消费记录,则在该月算做一个流失会员。
一张表,有三个字段 年月(YYYYMM) 会员ID 会员消费日期(YYYYMM)
一个年月有多个会员,一个会员有多个消费日期,同一个会员只有一个年月
最后希望输出这样的结果
年月 该月会员流失数量
求做法。伪代码或者文字描述清楚一点就行。
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 年月