回购率、复购率（SQL求解以及提升策略）

假设一瓶大瓶鲜奶，保质期只有3天。从顾客买回去计算只有3天时间的话  这3天就是单位时间。3天内他重复购买了，这是【复购】。可以成为分析出的顾客需求量大小，以及家庭结构画像的基础因子。如果3天内他没有重复购买，但是在第3个3天的时候再次购买了，这是【回购】。回购只是单次事件，不足以让我们刻画出用户画像，反复多次即可确认用户此类行为关联到他的基础画像里。当然也体现用户的忠诚度，多次。单次是不足以关联上他的忠诚度的。

SELECT t.`月份`,count(DISTINCT t.userid) as 消费人数,count(case when t.`消费次数`>1 then t.userid ELSE null END)as 复购人数,count(case when t.`消费次数`>1 then t.userid ELSE null END)/count(DISTINCT t.userid) as 复购率from (SELECT DATE_FORMAT(paidtime,"%Y-%m") as 月份,userid,count(userid) 消费次数 from 03order_infowhere ispaid="已支付"GROUP BY DATE_FORMAT(paidtime,"%Y-%m"),userid) as tGROUP BY t.`月份`;

SELECT t.month1,count(DISTINCT t.userid) as 当月消费人数,count(DISTINCT case when t.diff=1 then t.userid else null end) as 次月消费人数,count(DISTINCT case when t.diff=3 then t.userid else null end) as 第3月消费人数,count(DISTINCT case when t.diff=1 then t.userid else null end)/count(DISTINCT t.userid) 次月回购率,count(DISTINCT case when t.diff=3 then t.userid else null end)/count(DISTINCT t.userid) 3月回购率FROM(SELECT a.userid,a.month1,b.month2,month(b.month2)-month(a.month1) as diff FROM(SELECT userid,DATE_FORMAT(paidtime,"%Y-%m-01") as month1 from 03order_infoWHERE ispaid="已支付"GROUP BY userid,DATE_FORMAT(paidtime,"%Y-%m")) as aLEFT JOIN (SELECT userid,DATE_FORMAT(paidtime,"%Y-%m-01") as month2 from 03order_infoWHERE ispaid="已支付"GROUP BY userid,DATE_FORMAT(paidtime,"%Y-%m")) as bon a.userid=b.userid) as tGROUP BY t.month1