点击查看【bilibili】

1、统计不同月份的下单人数

  1. SELECT date_format(create_time,"%Y-%m"),count(1) 
  2. FROM `member_order`where status=1 
  3. group by date_format(create_time,"%Y-%m")

2、统计用户每月的复购率和回购率

点击查看：复购率与回购率的区别是什么？

2.1 复购率

  • 第一步：找出付费用户、付费月份、当月付费次数。注意分组时，同时基于ID和月份分组（可以调换顺序）。

    select month(create_time)as m,operator_id,count(1) as ct 
from member_order 
where status=1 
group by operator_id,m

  • 第二步：找出当月付费的人数和当月复购的人数。基于第一步，

    • 当月付费人数是count(ct)或count(operator_id)；
    • 复购人数先用if(ct>1,1,null)判断，再count，即：count(if(ct>1,1,null))，
    • 最后按月分组group by m，得到的结果就是每月的付费人数和复购人数
      select m as"月份",count(ct) as "当月购买人数",count(if(ct>1,1,null))as "复购人数",
concat(round(count(if(ct>1,1,null))/count(ct)*100),"%")as "复购率" 
from
(select month(create_time)as m,operator_id,count(1) as ct 
from member_order where status=1 group by operator_id,m)t
group by m

      2.2 回购率

      select t1.m,count(t1.m)as "本月购买人数",count(t2.m) as "次月回购人数",
concat(round(count(t2.m)/count(t1.m)*100),"%") as "次月回购率"
from
(select operator_id,date_format(create_time,"%Y-%m-01")as m 
from member_order where status=1)t1
left join
(select operator_id,date_format(create_time,"%Y-%m-01")as m 
from member_order where status=1)t2
on t1.operator_id=t2.operator_id and t1.m = date_sub(t2.m,interval 1 month)
group by t1.m

  • 第一步：找出付费用户，并添加辅助列。

格式：年-月-01，目的是将购买时间以“月”为单位归一化，为了下一步的计算。

疑问：日期格式是”年-月”行不行？答：不行，因为下一步date_sub()要求日期格式是“年-月-日”，否则计算结果会有问题。

select operator_id,date_format(create_time,"%Y-%m-01")as m 
from member_order where status=1
  • 第二步：将第一步的表自连接，并增加连接的筛选条件t1.operator_id=t2.operator_id和t1.m = date_sub(t2.m,interval 1 month)，即：t1的日期=t2的日期减1个月。目的是找出本月和次月都购买的用户。
  • 第三步：group by t1.m，按月分组，统计每个月的购买人数
  • 第四步：找出本月购买人数、次月购买人数、回购率=次月购买人数/本月购买人数
    select t1.m,count(distinct t1.operator_id)as "本月购买人数",
count(distinct t2.operator_id) as "次月回购人数",
concat(round(count(distinct t2.operator_id)/count(distinct t1.operator_id)*100),"%") as "次月回购率"
    视频教程中此处使用的是count(t1.m)，会有问题：同一个人在一个月内发生多次购买行为，会被统计多次，所以我用了distinct t1.operator_id去重。

    3、统计男女用户的消费频次是否有差异

4、统计多次消费的用户，第一次和最后一次消费间隔是多久

select operator_id,max(create_time),min(create_time),
datediff(max(create_time),min(create_time)) 
from member_order 
where status=1 
group by operator_id 
having count(1)>1

5、统计不同年龄段，用户的消费金额是否有差异

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

Mysql无法用一条语句实现此需求，需将问题逐步拆解为3条SQL：

  • 付费的用户按降序排列

    select sum(total_amount) as total 
from member_order 
where status=1 
group by operator_id 
order by total desc

  • top20%的用户有多少人？

    select count(total)*0.2 
from (select sum(total_amount) as total 
    from member_order where status=1 
    group by operator_id 
    order by total desc)t1

  • 用limit限制行数（也就是上一步count的结果），再统计总金额

    select count(total),sum(total) 
from (select sum(total_amount) as total 
    from member_order 
    where status=1 
    group by operator_id 
    order by total desc 
    limit 414)t1