xieyuanxiang 于 2016.03.10 21:13 提问

SQL 求每月会员流失数量

4个回答

danielinbiti      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(
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 回复xieyuanxiang: 每行相同的数？比如上面测试数据中会员id是1的，在201009月就是流失，一致没消费

xieyuanxiang 回复danielinbiti:结果不对。 生成一列cnt这里，你给每行加了一个相同的数什么意思。

danielinbiti 回复xieyuanxiang: 增加6个月

xieyuanxiang 谢谢！ 请问一下 INTERVAL 6 MONTH 是返回一个什么值？

swdenglian   2016.03.10 21:28

xieyuanxiang 最后输出需要每个年月对应一个当月的流失数量。 你如果觉得没必要，那也可以说说假如原表没有这个字段，该怎么做

xieyuanxiang 最后输出需要每个年月对应一个当月的流失数量。 你如果觉得没必要，那也可以说说假如原表没有这个字段，该怎么做

Ty_o_yT   2016.03.10 22:08

xieyuanxiang 回复Ty_o_yT: 不是跟当前比较，是显示每个月的流失量，包括历史的，

Ty_o_yT 回复xieyuanxiang: 我知道是注册日期啊 但是数据库可以获取当期日期啊 和最新的一条消费记录日期比较就行了

xieyuanxiang 表里面的年月不是当前日期，你可以理解为会员的注册日期。 如果说你说的当前日期是输入参数，那出来的结果只是我输入的这个月的流失量，我要的是一个日期列表排列下来，每行对应当月流失量。

enpterexpress   2016.03.10 22:51