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   Rxr 2016.03.10 22:51

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

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!
其他相关推荐
Oracle查询每日新增用户数量
SELECT NVL(A.dateDay,duleTable.days) dateTimes ,NVL(a.addnum, 0) newNumFROM ( SELECT TO_CHAR ( SYSDATE - ROWNUM + 1, 'YYYY-MM-dd' ) AS days FROM dual CONNECT BY ROWNUM &amp;lt; 60 ) duleTabl...
统计:mysql查询一段时间用户发布信息数量
统计当天日期信息发布总数(curdate()函数---当天) select count(bi.information_id) from bbg_informationbi where date(bi.release_time) = curdate() 统计前一天的信息发布总数date_sub(curdate(),interval 1 day);) select coun
用户流失预测模型
用户流失预测模型
MySQL:查询每月新增用户数
(4399的)一道大题,直接在网页上写。又不能调试 -_-! 简直…1. 设计一张表t,有如下字段。用户名,密码,性别,年龄,注册时间。2. 统计每个月新增的年龄大于18岁的男性用户。纯SQL查询可以写一段很长的SQL语句出来,但太麻烦。如果能创建视图,就很好了。第一步,建视图。根据第1个问题的表,可以造一张这样的表出来。m (时间)num (成年男性用户总数)2017-01342017-0246...
按月统计订单数量报表SQL语句(CASE 和DECODE的使用)
本文主要是使用CASE 和DECODE实现按月统计订单数量,通过此示例希望能帮助读者理解CASE和DECODE在ORCALE中的使用。 1.使用CASE实现: 按 Ctrl+C 复制代码 按 Ctrl+C 复制代码 这是一条按月统计每个OPERATING_WAREHOUSE_CODE订单数量的SQL语句,统计2012年一到三月份每个OPERATING_WAREHOUSE_COD
怎样一条SQL语句统计该年每月的数据个数
先说明一下数据库:Oracle的数据库   我原本向一位老大哥求救,人家用的MySql   最后我上网扒的,自己慢慢写的      这条SQL语句是查询某个区间范围里的月份统计个数   作为记录吧,网上的好多都不好用 ,数据库列的格式是Date类型  至于char类型的我没写,所以也不会,我在底下会把向老大哥请教的也写上 以下语句亲测有效!至于你们要是不好使,请自行了断!   第一种
查询SQL Server数据库中的用户表的数量的问题
今天在开发时,遇到要统计SQL Server 2005中数据库表的个数的问题。于是自己写下SQL:select count(1) from sysobjects where XTYPE='u',一执行,发现查询的得到表的个数比实际的用户表的个数多了一个。仔细一看发现结果中多了条数
SQL计算每个月的第一天,最后一天,本月的天数
SQL 计算每个月的第一天,最后一天,本月的天数。sql server:--frist dayselect dateadd(dd,-datepart(dd,getdate())+1,getdate())--last dayselect dateadd(dd,-datepart(dd,getdate()) ,dateadd(mm,1,getdate()))--next month
统计每个商品每个月的销量???
SELECT  M.B2B_SKU AS SKU,G.MATERIALNAME AS GOODNAME, SUM(CASE MONTH(O.SUBMITTIME) WHEN '1' THEN G.PURCHASQUANTITY ELSE 0 END) AS one,   SUM(CASE MONTH(O.SUBMITTIME) WHEN '2' THEN G.PURCHASQUANTITY
SQL语句统计每天、每月、每年的 数据
1、每年 select year(ordertime) AS '年', sum(Total) '销售合计' from order_list group by year(ordertime) 2、每月 select year(ordertime) '年', month(ordertime) '月', sum(Total) '销售合计' from order_list group by year(o