如何用sql计算回购率、复购率指标

这6道sql题都很好，建议都过一遍；

考察知识点：

• 回购率、复购率的理解
• 子查询
• inner join
• 重点推荐第2题，第5题，第6题
• 理解需求、理解题意 （★★★★★）
• datediff
• ceil 函数
• row_number() 、 子查询内容
• 二八定律的应用

复购率和回购率分析：
复购率：复购率是指重复购买的频率，用于反映用户的付费频率。复购率指的是一定时间内，消费两次以上的用户数/付费人数。
回购率：曾经购买过的用户在某一时期内再次购买的占比

– lulu_Course

附上源码：

-- lulu_Course 

-- 1.统计不同月份的下单人数
select month(paidTime),count(distinct userid) from data.orderinfo
where isPaid = "未支付"
group by month(paidTime)

-- 2.统计用户三月份的回购率和复购率
/*
名词解释：
复购率：在这个月里面，所有的消费人数中有多少个是消费一次以上人数的占比；
回购率：上月购买的人数，在下一个月依旧购买；
*/

-- 复购率：
select count(ct)，count(if(ct>1,1,null))
from(select userid,count(userid) as ct 
	from order_info
	where isPaid = "已支付"
	and month(paidTime) = 3
	group by userid)t 

-- 回购率：涉及跨月份

# 法1：代码适合一次性需求
select count(1) from
where userid in (子查询，算出3月份的userid)
and month(paidTime) = 4
group by userid;

# 法2：
-- step1:
select * from(
      select userid,date_format(paidTime,"%Y-%m-01") as m
      from order_info where ispaid = "已支付"
      group by userid,date_format(paidTime,"%Y-%m-01"))t1
left join(
      select userid,date_format(paidTime,"%Y-%m-01") as m
      from order_info where ispaid = "已支付"
      group by userid,date_format(paidTime,"%Y-%m-01"))t2
)
on t1.userId = t2.userId and t1.m = date_sub(t2.m,interval 1 month)
-- where t1.m = date_sub(t2.m,interval 1 month)亦可

-- step2:

select t1.m,count(t1.m) as 购买人数,count(t2.m) as 回购人数 from(
      select userid,date_format(paidTime,"%Y-%m-01") as m
      from order_info where ispaid = "已支付"
      group by userid,date_format(paidTime,"%Y-%m-01"))t1
left join(
      select userid,date_format(paidTime,"%Y-%m-01") as m
      from order_info where ispaid = "已支付"
      group by userid,date_format(paidTime,"%Y-%m-01"))t2
)
on t1.userId = t2.userId and t1.m = date_sub(t2.m,interval 1 month)
-- where t1.m = date_sub(t2.m,interval 1 month)
group by t1.m

-- 3.统计男女用户的消费频次是否有差异
/*
理解：求消费频次？总计、求平均 (当然篇平均数未必是靠谱的，这只是一个其中的分析思路吧)

*/

select sex,avg(ct) from(
        select t1.userid,sex,count(1) as ct from order_info t1
        inner info(
               select * from user_info
               where sex <> "" )t2
        on o.userid = t.userid
        group by userid,sex)t3
group by sex；

-- 4.统计多次消费的用户，第一次和最后一次消费间隔是多少？
-- 操作1
select userid
      ,max(paidTime)
      ,min(paidTime)
from order_info
where ispaid = '已支付'
group by userid 
having count(1)>1;

-- 操作2：（lulu：勉强估计一下生命周期）
select avg(interval) as avg_interval
from(select userid
      ,max(paidTime)
      ,min(paidTime)
      ,datediff(max(paidTime),min(paidTime)) as interval
from order_info
where ispaid = '已支付'
group by userid 
having count(1)>1
	) tepmt;

-- 5.统计不同年龄段，用户的消费金额是否有差异
-- 计算每个用户的消费频次
select age,avg(ct)
from（select  o.userid
              ,age
              ,count(o.userid) as ct
from order_info o where ispaid = '已支付'
inner join(
        select userid,ceil((year(now())-year(birth))/10) as age
        from userinfo
        where birth > '1901-00-00')t -- 过滤掉117
on o.userid = t.userid
group by o.userid,age）t2
group by age；

-- 补充知识：ceil()函数和floor()函数
-- ceil(n) 取大于等于数值n的最小整数；
-- floor(n) 取小于等于数值n的最小整数；


-- 6.统计消费的二八法则，消费的top20%用户，贡献了多少额度

-- 方法1：取巧做法，见lulu

-- 方法2：row_number/子查询方法
select sum(total) as 'top20%贡献额度'
from(select  userid
            ,sum(price) as total
            ,row_number()over(order by sum(price) desc) as 排名
     from order_info o where ispaid = '已支付'
     group by userid
     ) t
where  排名 < (select count(1) from order_info  where ispaid = '已支付'  group by userid) * 0.2;    
-- 取巧做法：select count(userid)*0.2 得到 17000m-- row_number()/ 注意临时表不能复用