MySQL用户消费行为分析

2 年前

鸡蛋卷

对两张表userinfo和orderinfo利用Navicat Premium进行用户消费行为分析。

查看表字段信息

desc orderinfo；
desc userinfo；

orderid--订单id，userid--用户id，ispaid--是否支付，price--金额，paidtime--付款时间

分析问题

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

SELECT date_format(paidtime,'%Y-%m')'月份',count(DISTINCT userid) '人数'from orderinfo
where ispaid='已支付' 
and date_format(paidtime,'%Y-%m') is not null  #paidtime有空值，去掉空值
GROUP by date_format(paidtime,'%Y-%m')；

根据月份分组，使用聚合函数count对userid去重计算出每个月付款的人数

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

复购率

购买一次后又购买第二次占所有购买过用户的占比，即三月份内消费次数大于两次的用户占所有消费用户的比例

先找出三月份消费用户的消费次数

SELECT userid,count(*)from orderinfo
where ispaid='已支付' and date_format(paidtime,'%Y-%m')='2016-03'
GROUP BY userid；

再从中选出消费次数大于1次的计算个数

SELECT sum(case when n>1 then 1 else 0 end) '复购人数',count(*)'总人数'
from(
SELECT userid,count(*) n from orderinfo
where ispaid='已支付' and date_format(paidtime,'%Y-%m')='2016-03'
GROUP BY userid
) t；

复购率=复购人数/总人数≈30.8%

回购率

三月份付款用户在四月份仍然购买

先将三月份四月份付过款的用户通过外连接联系起来，再分别对两个月用户count计数

SELECT count(3yue) '总数',count(4yue) '复购' from
(SELECT userid 3yue from orderinfo
where ispaid='已支付' and date_format(paidtime,'%Y-%m')='2016-03'
GROUP BY userid) t1 
LEFT JOIN(
SELECT userid 4yue from orderinfo
where ispaid='已支付' and date_format(paidtime,'%Y-%m')='2016-04'
GROUP BY userid) t2
on 3yue=4yue

回购率=复购/总数≈23.9%
3-统计男女的消费频次是否有差异

先根据userid分类求出没人的消费频次

SELECT userid,count(*)  from orderinfo
WHERE ispaid='已支付'
GROUP BY userid

再和userinfo连接，根据性别分类求出平均消费频次

SELECT sex,avg(n) from
(SELECT userid,count(*) n from orderinfo
WHERE ispaid='已支付'
GROUP BY userid) t1 INNER JOIN userinfo u
on t1.userid=u.userid
where sex is not null
GROUP BY sex

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

第一次和最后一次消费时间则分别是时间的最小值和最大值，使用datediff函数求出差值便是消费时间间隔

SELECT userid,count(*),max(paidtime) 最后付款时间,min(paidtime) 第一次付款时间,DATEDIFF(max(paidtime),min(paidtime)) 时间差 
from orderinfo 
WHERE ispaid='已支付'
and paidtime is not null
GROUP BY userid
having count(*)>1

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

先求出每个用户的累计消费再和userinfo表连接，去掉年龄不符合实际的，把年龄分类分组算出每个年龄段的平均消费金额。

select 消费,
case age when 1 then '0-10' when 2 then '10-20' when 3 then '20-30' when 4 then '30-40'
when 5 then '40-50' when 6 then '60-70' when 7 then '70-80' else 0 end 年龄段 
(SELECT round(avg(sum),0) 消费,ceil((DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(borndate,'%Y'))/10) 'age' 
from(
SELECT userid,sum(price) sum from orderinfo
where ispaid='已支付'
GROUP BY userid) t INNER JOIN userinfo u
on t.userid=u.userid
WHERE borndate is not null and borndate>'1950/01/01'
GROUP by age
) a

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

先分别求出总人数和20%的人数

SELECT count(*) from
(SELECT count(*) from orderinfo
where ispaid='已支付'
group by userid)t；
SELECT count(*)*0.2 from
(SELECT count(*) from orderinfo
where ispaid='已支付'