mysql 电商实战_电商购买行为数据分析练习-MYSQL

对面大批量的数据来说，SQL的处理速度会比Excel快上许多。今天我将使用一次几十万行的电商购买行为数据进行练习。

一、理解数据

二、提出问题

·统计不同月份下单人数

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

·统计男女消费频次是否有差异

·统计多次消费的用户，第一次购买和最后一次购买的消费间隔是多少？

·统计不同年龄段，用户消费金额是否有差异？

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

三、分析问题

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

select ispaid,paidTime,count(distinct userId) 下单人数 from orderinfo

where ispaid = "已支付"

group by month(paidTime)

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

①复购率

select count(if(test>=2,1,null))/count(test) 复购率 from (

select userId, count(1) test from orderinfo

where ispaid = "已支付"

and month(paidTime) = 3

GROUP BY userId

) t;

②回购率

select t1.m,COUNT(t1.m),COUNT(t2.m) from (

select userId,date_format(paidTime,'%Y-%m-01') m from orderinfo

where ispaid = '已支付'

group by userId,date_format(paidTime,'%Y-%m-01')

) t1

left join

(

select userId,date_format(paidTime,'%Y-%m-01') m from orderinfo

where ispaid = '已支付'

group by userId,date_format(paidTime,'%Y-%m-01')

) t2

on t1.userId = t2.userId

AND t1.m = date_sub(t2.m,interval 1 month)

group by t1.m

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

select sex,avg(ct) 消费频次 from (

select o.userId,sex,count(1) ct from orderinfo o

join userinfo u

ON o.userId = u.userId and ispaid = '已支付' and sex in ('男','女')

group by userId,sex ) t

group by sex

4、统计多次消费的用户，第一次购买和最后一次购买的消费间隔是多少？

select userId,max(paidTime),min(paidTime),

datediff(max(paidTime),min(paidTime)) 消费间隔 from orderinfo

where ispaid = '已支付'

group by userId

having count(1) >= 2

这里的消费间隔我们可以粗略知道一个用户的生命周期是多少。

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

select round(datediff(curdate(),birth)/356,0) 年龄,

truncate(avg(price),2) 平均消费金额

from orderinfo o

inner join userinfo u

on o.userId = u.userId and month(birth) <> 00 and ispaid = '已支付'

group by round(datediff(curdate(),birth)/356,0)

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

这里需要分两个步骤：

①首先将将消费金额TOP20%的用户数量找出来

select ceil(count(1)* 0.2) from (

select userId,SUM(price) from orderinfo

where ispaid = '已支付'

group by userId) t

这里得出来的结果为17310是TOP20%用户的数量

②根据以上结果使用limit就可以算出来消费总金额

select sum(su) 消费总金额 from (

select userId,SUM(price) su from orderinfo

where ispaid = '已支付'

group by userId

order by sum(price) desc

limit 17130) t

根据二八法则我们可以知道这20%的用户贡献了80%的消费金额，持续关注这些用户能够让我们业务事半工倍。