问题描述
- SQL 求每月会员流失数量
-
一个会员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 年月
解决方案二:
年月 是个什么作用呢
解决方案三:
查询每个会员消费日期,倒序排列 找第一个 然后和当前日期比较 大于6个月的就算作是
解决方案四:
可以理解为消费记录表中每一段时间(六个月)内,会员是否有记录,统计问题
时间: 2024-11-08 19:08:55