SQL练习2：消费者行为分析

作者: jinghenggl | 来源:发表于2020-11-11 14:37 被阅读0次

SQL练习2：消费者行为分析
浅谈AISAS营销理论
MySQL Operation
sql 练习(五)
2018-08-05--08-11
SQL 资源汇总
第一周计划（05-20~05-26）
牛客网SQL实战练习——1~5
sql 练习(四)
经典SQL练习题(MySQL版)

目录：
一、将数据导入数据库
二、SQL--用户消费行为分析
1、统计不同月份的下单人数
2、统计用户三月份的回购率和复购率
3、统计男女的消费频次是否有差异
4、统计多次消费的用户，第一次和最后一次消费时间的间隔
5、统计不同年龄段的用户消费金额是否有差异
6、统计消费的二八法则，消费的top20%用户，贡献了多少额度

一、将数据导入数据库

目的：将两份csv文件导入数据库
步骤：建表、导入数据

建表

1、订单明细表orderinfo：

image.png
2、用户表userinfo：

image.png
3、导入数据
我是用Navicat中的导入向导直接导入。两个数据集分别是10万和50万条数据，导入时间时长较快。
数据集更大时可以用cmd命令行导入、或者用KETTLE进行抽取。我尝试用CMD命令导入几次，但一直报 ERROR 1290 (HY000)，尝试几次没解决故作罢。

二、用户消费行为分析

使用MySQL数据库

分析问题：

1、统计不同月份的下单人数
2、统计用户三月份的回购率和复购率
3、统计男女的消费频次是否有差异
4、统计多次消费的用户，第一次和最后一次消费时间的间隔
5、统计不同年龄段的用户消费金额是否有差异
6、统计消费的二八法则，消费的top20%用户，贡献了多少额度

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

select month(paidTime),count(userID) from orderinfo
where isPaid = '已支付'
group by month(paidTime);

image.png

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

（1）复购率是在本月消费一次以上用户的占比(区分消费1次及1次以上的)

select count(ct) as 总消费用户,
sum(case when ct>1 then 1 else 0 end) as 复购用户,
sum(case when ct>1 then 1 else 0 end)/count(ct) as 复购率
from(select userID,count(userID) as ct
from orderinfo
where isPaid = '已支付' and month(paidTime)=3
group by userID) t;

image.png

（2）回购率是三月份购买的人数四月份依旧购买

select t1.m as 月份,count(t1.m) as 本月消费用户,count(t2.m) as 本月回购用户 from 
  (select userID,month(paidTime) as m
  from orderinfo
  where isPaid='已支付'
  group by userID,month(paidTime)) t1
left join 
  (select userID,month(paidTime) as m
  from orderinfo
  where isPaid='已支付'
  group by userID,month(paidTime)) t2
on t1.userID=t2.userID and t1.m= DATE_SUB(t2.m,INTERVAL 1 MONTH)
group by t1.m;

image.png

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

select sex,avg(ct) from
(select o.userID,sex,count(*) as ct
from orderinfo o
inner join
(select * from userinfo where sex is not null ) t
on o.userID= t.userID group by o.userID,sex) t2;

image.png

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

select userID , DATEDIFF(max(paidTime),min(paidTime)) as 消费间隔
from orderinfo
where isPaid='已支付'
group by userID having count(*)>1;

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

select age,avg(ct) from
(select o.userID,age,count(o.userID) as ct from orderinfo o
inner join
(select userID,ceil((year(now())-year(birth))/10) as age
from userinfo
where birth >'0000-00-00') t
on o.userID = t.userID
group by o.userID,age) t2
group by age;

image.png

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

select count(userID) ,sum(total)  as 前20%用户
from
(select userID,sum(price) as total
from orderinfo
where isPaid='已支付'
group by userID
order by total desc
limit 17000) t;