SQL：分析用户消费行为

目录：

一. 建表和导入数据

二. 分析用户的消费行为

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

2. 统计用户三月份的回购率和复购率

3. 统计多次消费的用户，第一次和最后一次消费间隔

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

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

6. 根据二八法则，统计消费金额的前 20% 用户贡献的额度

---

一. 建表和导入数据

• 数据：order_info_utf ，user_info_utf
• 工具：Navicat 和 MySQL
• 建表和导入数据：直接用Navicat导入向导导入。
  建表：
  orderinfo (订单)表

orderId 订单ID(主键)
userId 用户ID
isPaid 是否支付
price 订单价格
paidTime 订单支付时间

userinfo (用户)表

userId 用户ID(主键)
sex 用户性别
birth 用户出生日期

二. 分析用户的消费行为

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

在订单表中查询，先筛选出 已支付 的用户，然后以月份( 这里年份同为一年) 来分组，再以非重复的用户 ID 来计数

#统计不同月份的下单人数
select month(paidTime) as month, count(distinct userId) as num from orderinfo
where isPaid= '已支付'
group by month(paidTime)

num 即为当月的下单人数

2. 统计用户三月份的回购率和复购率

• 复购率

在订单表中查询，三月份中，对有消费行为的用户计数，再用条件筛选复购 (当月消费次数大于一次) 的用户，与总的有消费用户相比，得出比率 rate

#统计用户三月份的复购率
 select count(if(ct > 1, 1, null)) / count(ct) as rate from 
(
 select count(userId) ct from orderinfo
 where isPaid= '已支付' and month(paidTime)= 3
 group by userId
) t

三月份的复购率为 30.87%

• 回购率

回购率：当月消费用户中有多少在下一月份有消费

在订单表中查询，在四月份中有消费的并且其在三月份中也有消费的用户的计数，与在三月份中有消费的用户的计数相比

#统计用户三月份的回购率
select ct34/ct3 rate from 
(select count(distinct userId) ct34 from orderinfo
where isPaid = '已支付' and month(paidTime) = 4
            and userId in (select distinct userId from orderinfo
                                         where isPaid = '已支付' and month(paidTime) = 3))t1,
                                     
(select count(distinct userId) ct3 from orderinfo
where isPaid = '已支付' and month(paidTime) = 3)t2

三月份的回购率为 23.94%

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

由于数据中性别有空值，先进行数据清洗

然后先对每一个用户进行频次计数，再求男女用户的平均频次

#统计男女用户的消费频次是否有差异
select sex,avg(ct) as f from
(
select o.userId,sex,count(1) ct from orderinfo o
join (
select * from userinfo
where sex <> 'null') t
on o.userId = t.userId
group by o.userId) t2
group by sex

男女用户的消费频次基本无差异
无需针对其一类别用户进行维护和管理

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

在多次消费的用户中，先分别求出第一次和最后一次的消费时间，再求差

与用户生命周期相关

#统计多次消费的用户，第一次和最后一次消费间隔
select userId, datediff(max(paidTime), min(paidTime)) as diff from orderinfo
where isPaid = '已支付'
group by userId having count(1) > 1

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

需要连接订单表和用户表来查询，用 case 语句将用户年龄分段，再以年龄段来分组求平均消费金额

select t.cut_ages, round(avg(t.price), 2) avg_price from(
select o.orderId, o.userId, u.ages, cast(o.price as float) price,
       case when u.ages between 10 and 19 then '10-19 岁'
            when u.ages between 20 and 29 then '20-29 岁'
            when u.ages between 30 and 39 then '30-39 岁'
            when u.ages between 40 and 49 then '40-49 岁'
            when u.ages between 50 and 59 then '50-59 岁'
            when u.ages between 60 and 69 then '60-69 岁'
            when u.ages between 70 and 79 then '70-79 岁'
            else null end as cut_ages                                
from
(select * from orderinfo 
where isPaid= '已支付') o
join
(select userId, (year(now()) - year(birth)) as ages 
from userinfo
where birth <> 'null') u
on o.userId= u.userId) t
group by t.cut_ages
having t.cut_ages <> 'null'
order by t.cut_ages

青少年用户的消费金额较少
中年人用户的消费金额较高
但差异不是十分的大，可在推广中稍侧重在中年用户方面

6. 根据二八法则，统计消费金额的前 20% 用户贡献的额度

计算每个用户的消费总额，再以总额排名，筛选出前 20%

select sum(total) from
(
select *, row_number()over(order by total desc) rn from
(select userId, round(sum(cast(price as float)),2) total from orderinfo
where isPaid = '已支付'
group by userId) t) t2
where rn < (select round(count(distinct userId)*0.2, 0) from orderinfo where isPaid = '已支付')

所有用户的消费金额

消费金额的 top 20% 用户约占总金额的 85%
这 20% 高价值的用户要进行有针对的客户管理和维护