mysql复购率_MYSQL系列（5） 电商常用指标查询

表:order ,字段：orderid, userid, isPaid, price, paidTime

表:user,字段: userid, sex, birth

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

select month(date_format(paidtime, '%Y-%m-%d')), count(distinct userid)

from order

where isPaid = '已支付'

group by month(date_format(paidtime, '%Y-%m-%d'));

2.统计3月份回购率和复购率

2.1 3月份复购率

复购率：3月份中购买两次以上的人数占3月份总购买人数的比例

? 3月份购买人数

select userid, count(userid) as count_userid

from order

where ispaid = '已支付'

and month(date_format(paidtime, '%Y-%m-%d')) = 3

group by userid;

? 复购率 =3月份购买两次以上的人数/ 对3月份复购人数统计去重

select count(t.userid) as 购买人数, count(if(t.count_userid>1, 1, null)) as '复购人数',

count(if(t.count_userid>1, 1, null)) / count(t.userid) as '复购率'

from (select userid, count(userid) as count_userid

from order

where ispaid = '已支付'

and month(date_format(paidtime, '%Y-%m-%d')) = 3

group by userid

) t

2.2 用户回购率

? 运用子查询，筛选出第二个月有购买记录的情况，然后进行count

select t1.m, count(t1.userid), count(t2.userid)

from (select userid), date_format(paidtime, '%Y-%m-01') as m

from order

where ispaid = '已支付'

group by userid, date_format(paidtime, '%Y-%m-01')

) as t1

left join

(select userid, date_format(paidtime, '%Y-%m-01') as m

from order

where ispaid = '已支付'

group by userid, date_format(paidtime, '%Y-%m-01')

) as t2

on t1.userid = t2.userid and t1.m = date_sub(t2.m, INTERVAL 1 month)

group by t1.m

统计第一次和最后一次消费间隔多久

select userid, datediff(max(paidtime), min(paidtime))

from order

where ispaid = '已支付'

group by userid

having count(1) > 1

原文：https://www.cnblogs.com/yangjiez/p/12105693.html