 所需数据：ORDER_INFO_UTF.CSV、USER_INFO_UTF.CSV

 数据链接:https://pan.baidu.com/s/11ZtjKnv5-nwyf6cMyk_3JQ

 提取码:yu63

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

一、将数据导入数据库

前面给出的数据较大，分别是10万和50万条数据，如果用数据库自带的导数工具比较缓慢，像WORKBENCH导入几千行数据就需要3分钟时间，这里可以用cmd命令行导入、或者用KETTLE进行抽取。

建表

创建订单明细表orderinfo和用户表userinfo：

create database if not exists learning;
create table if not exists userinfo(
     userId int         null ,    -- 用户ID，主键
     sex varchar(20)    null ,    -- 性别
     brith varchar(20)	null	  -- 出生日期
);
create table if not exists orderinfo(
    orederId int(11),   -- 订单ID，主键
    userId int(11),     -- 用户ID，可以和用户表进行关联
    isPaid varchar(10), -- 是否支付
    price varchar(10),  -- 订单价格
    paidtime varchar(20)-- 订单支付时间
);

导入数据

本次使用的是用cmd命令行导入。

首先，在cmd中输入

mysql --local-infile=1 -u root -p;

再输入mysql的密码，进入mysql

其次，先后输入如下代码，导入成功

load data local infile "C:/Users/Yoki/Desktop/学习/练习数据/user_info_utf.csv" into table learning.userinfo fields terminated by ',';
load data local infile "C:/Users/Yoki/Desktop/学习/练习数据/order_info_utf.csv" into table learning.orderinfo fields terminated by ',';

二、SQL--用户消费行为分析

使用的是learning数据库

分析问题：

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

#1-统计不同月份的下单人数
select concat(substring(paidtime,1,4),'-',substring(paidtime,6,1)) as m,
       count(distinct userId) as '下单人数' 
from orderinfo
where isPaid='已支付'
group by m;

结果：

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

复购率：

-- 复购率是在本月消费中多少人消费一次以上的占比
create view buy1 as
select concat(substring(paidtime,1,4),'-',substring(paidtime,6,1)) as m,
       userId,count(*) as cons
from orderinfo
where isPaid='已支付' 
group by m,userId
having m='2016-3';
#转换成布尔类型
select round(avg(cons!=1),2) 复购率
from buy1;

结果：

可以看出，用户三月份的复购率为0.31

回购率：

-- 回购率是三月份购买的人数四月份依旧购买
select substring(a.paidtime,1,6) '年月',
       count(distinct a.USERID) '本月消费的用户数量',
	   count(distinct b.USERID)'本月回购的用户数'
from(
	select  userid,paidtime 
        from orderinfo
	where ispaid = '已支付'
	group by userid,paidtime) a
left join (
	select  userid,paidtime
	from orderinfo
	where ispaid= '已支付'
	group by userid,paidtime) b 
on a.userid = b.userid and substring(a.paidtime,6,1) =  substring(b.paidtime,6,1)-1
group by substring(a.paidtime,1,6);

结果：

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

#3-统计男女的消费频次是否有差异
select sex,count(*) 消费频次
from orderinfo
join userinfo using(userId)
where isPaid='已支付' 
group by sex;

结果：

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

select userId,count(*) as cons,max(paidtime),min(paidtime),
       datediff(max(substring(paidtime,1,9)),min(substring(paidtime,1,9)))  时间间隔
from orderinfo
where isPaid='已支付' 
group by userId
having cons>1;

结果：

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

select 
case 
	when age between 10 and 19 then '10-19岁'
    when age between 20 and 29 then '20-29岁'
    when age between 30 and 39 then '30-39岁'
    when age between 40 and 49 then '40-49岁'
    when age>50 then '50岁以上'
end as age_group,avg(price) 消费金额
from (
	select o.userId,year(now())-year(u.brith) as age,price
	from orderinfo o
	join userinfo u using(userid)
	where u.brith is not null 
	and o.isPaid='已支付'
) age_price
where age is not null
group by age_group
having age_group is not null;

结果：

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

# 6-统计消费的二八法则，消费的top20%用户，贡献了多少额度
select sum(sum_price) 贡献额度
from(
	select *,row_number()over(order by sprice.sum_price desc) as row_num
	from (
		select userid,sum(price) as sum_price
		from orderinfo
		where isPaid='已支付'
		group by userId
	) as sprice ) as top_price
where row_num < (
	select floor(count(distinct USERID)*0.2)
	from  ORDERINFO 
	where ISPAID = '已支付'
)

结果：

如果有其他的简便方法欢迎大家留言。

原分析问题来自：

SQL练习2：用户消费行为分析(回购率和复购率)

ITPub博客

sql-用户消费行为分析

建表

导入数据

二、SQL--用户消费行为分析

分析问题：

最新文章