mysql sum 和 count_MySQL分析用户消费行为

MySQL是关系型数据库，SQL语言可以很好地操作数据库并进行数据分析。现对用户消费数据进行初步分析，数据来源于某网站的销售记录，包含2张表：订单信息表和用户信息表。

下面内容为使用SQL进行的实际案例分析，创建数据库、对数据的处理实现实际业务分析，包含4部分：创建数据库，创建表，导入数据、业务分析。

一、创建数据库

create 

二、创建表

create 

三、导入数据

a，可通过workbench操作导入，导入时间过长。

b，通过命令行窗口执行语句导入数据，速度快。

订单信息表：

select * from orderinfo limit 10;

用户信息表：

select 

四、业务分析

1、统计不同月份的下单人数。（用户人数）

首先观察业务数据，类似每月消费人数，每月消费金额等较为关注的指标数据，这些指标一定程度上可以反映用户的留存情况或者是运营效果。

select month(paidtime) as 月份,count( distinct userid) as 人数, 
round(sum(price))/10000 as 消费金额万元 
from orderinfo 
where ispaid='已支付' 
group by month(paidtime) ;

2、统计男女用户的消费频次是否有差异。（用户属性差异）

select userinfo.sex 性别,date_format(paidtime,'%Y-%m') 月份, 
round(sum(price)/count(distinct orderinfo.userid)) as 平均每人消费金额,
count(price)/count(distinct orderinfo.userid) as 平均每人购买次数  
from orderinfo inner join userinfo on orderinfo.userid=userinfo.userid
where ispaid='已支付'  and sex <>" " 
group by userinfo.sex,date_format(paidtime,'%Y-%m')
order by 月份;

对不同属性（性别）的用户消费进行分析，可以看出男女用户的消费频次和消费金额差异不大，说明基本需求差异也不大。

3、统计用户三月份的回购率和复购率。（消费频次）

对复购率统计，可以看出用户消费的时间频次，用于针对性地推广、以尽可能少的成本实现有效的运营效果。

对回购率统计，可以看出用户的留存情况，通过挖掘背后的原因，可以反映运营的效果、产品功能质量，也可能发现新的增长点，实现营收。

#三月份，用户的回购率
select count(m1) as 3月份消费人数 ,count(m2) 3、4月份都消费的人数,
concat(round((count(m2)/count(m1))*100,2),"%") as 回购率 
from
(select userid, date_format(paidtime,'%Y-%m-01') as m1
from orderinfo
where ispaid='已支付'  and month(paidtime)=3 
group by  userid,date_format(paidtime,'%Y-%m-01')) t1
left join 
(select userid, date_format(paidtime,'%Y-%m-01') as m2
from orderinfo
where ispaid='已支付'  
group by  userid,date_format(paidtime,'%Y-%m-01') ) t2  
on t1.userid=t2.userid  and t1.m1=date_sub(t2.m2,interval 1 month);

#三月份，用户的复购率
select  count(ct) as 总人数, count(if(ct>1,1,null)) as 购买2次及以上的人数,
concat(round((count(if(ct=1,null,1))/count(*))*100,2),"%") as 复购率
from
(select userid, count(price) as ct
from orderinfo
where ispaid='已支付'  and month(paidtime)=3 
group by  userid ) t;

4、统计多次消费的用户，首购和最后一次消费的间隔。

函数：date_diff( )，时间函数，可用于计算两个日期相隔天数。

select userid, datediff(max(Paidtime),min(Paidtime)) as 间隔天数
from orderinfo
where isPaid="已支付" and Paidtime>0
group by userid
having count(orderid)>1 ;

对用户分组，查看其消费时间，间隔天数长的用户算是忠诚用户了，可以调研询问他们喜欢的功能或者是哪一点，反馈的结果有可能是当初开发或设计时并不太关注的某个点。对间隔天数短的用户做问卷调查，可以了解引起高流失率的原因，以便做出改善，提高营收。

拓：假定这份数据集记录了某段时间新增用户在后续时间的消费情况，想要查看所采取的营销策略效果如何，运行下面代码可知，用户流失率为71.99%，说明当前运营效果不好或者是产品质量功能有缺陷等，因此需要根据结果采取新策略或改善产品功能。

#生命周期为0的用户数占比
select count(t1.userid) as 总人数,count(t2.userid) 生命周期为0的人数,
concat(round((count(t2.userid)/count(t1.userid))*100,2),"%") as 生命周期为0的用户占比
from 

((select distinct userid  from orderinfo
where isPaid="已支付" and Paidtime>0)  t1   #总消费人数
left join 
(select userid
from orderinfo
where isPaid="已支付" and Paidtime>0
group by userid
having datediff(max(Paidtime),min(Paidtime))=0 ) t2  #生命周期为0的人数，包括同一天消费一次或多次
on t1.userid=t2.userid)

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

对不同年龄段的用户消费金额进行统计，筛选出重点客户，可用于用户的分层与画像中，为用户运营提供依据。

select concat(floor((year(now())-year(birth))/10)*10,"~",ceil((year(now())-year(birth))/10)*10,"岁") as 年龄段,
round(sum(price)/10000) as 消费金额万元
from orderinfo  inner join userinfo on orderinfo.userid=userinfo.userid
where isPaid="已支付" and Paidtime>0 and birth>0  and ceil((year(now())-year(userinfo.birth))/10)<10
group by ceil((year(now())-year(birth))/10)
order by 消费金额万元 desc;

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

为了增加收入，根据客户的消费金额，标记出重点客户，实行精细化运营。

# 20%的消费人数是多少
select count(distinct userid) as 总消费人数,round(count(distinct userid)*0.2) as 百分之二十的人数
from orderinfo
where isPaid="已支付" ;

select count(t1.userid) as 总人数, count(t2.userid) as 百分之20的人数,
round(sum(total1)) as 总消费, round(sum(total2)) as 百分之20的消费金额, round(sum(total2)/sum(total1),2) as 消费占比
from 
(select userid,sum(price) as total1
from orderinfo
where  ispaid="已支付"
group by userid) t1
left join 
(select userid,sum(price) as total2
from orderinfo
where  ispaid="已支付"
group by userid
order by  total2 desc
limit 17130) t2 on t1.userid=t2.userid;

可知，20%的客户贡献了85%的消费额，验证了二八定律，这也是运营时要重点关注这20%的客户，进一步对这部分客户进行精细化运营，提高收入。