MySQL中用户订单复购率的计算

1.项目需求（计算复购率）
订单表中有用户ID、订单金额、每笔订单的下单时间等信息，需要统计每个月在接下来几个月用户复购情况

// 创建数据表 create table orders( order_id int primary key, user_id int, amount int, create_time datetime ); // 插入数据 insert into orders values (101,11211,749.00,'2021-05-06 00:04:00'), (102,75205,939.00,'2021-05-08 09:13:00'), (103,81384,349.00,'2021-05-09 22:18:00'), (104,50437,687.00,'2021-05-19 22:20:00'), (105,30321,658.00,'2021-05-19 21:20:00'), (106,49811,355.00,'2021-05-20 21:20:00'), (107,30352,363.00,'2021-05-19 21:56:00'), (108,30362,435.00,'2021-05-26 23:45:00'), (109,30363,270.00,'2021-05-27 10:16:00'), (110,30324,552.00,'2021-05-30 15:28:00'), (111,11211,692.00,'2021-05-08 16:34:00'), (112,75205,536.00,'2021-06-09 17:38:00'), (113,81384,478.00,'2021-06-13 21:36:00'), (114,30362,675.00,'2021-06-16 23:29:00'), (115,30363,723.00,'2021-06-20 11:42:00'), (116,30324,914.00,'2021-06-21 11:42:00'), (117,49262,1110.00,'2021-06-24 20:32:00'), (118,12074,617.00,'2021-06-29 21:12:00'), (119,50437,911.00,'2021-07-02 16:36:00'), (120,30321,695.00,'2021-07-14 22:56:00'), (121,12074,275.00,'2021-07-15 18:37:00'), (122,81384,1066.00,'2021-07-18 17:39:00'), (123,30362,958.00,'2021-07-23 11:30:00'), (124,27727,687.00,'2021-07-25 16:38:00'), (125,27727,858.00,'2021-07-28 15:20:00'), (126,75205,1120.00,'2021-07-28 15:20:00'), (127,30324,618.00,'2021-08-13 15:20:00'), (128,30362,958.00,'2021-08-16 11:30:00'), (129,27727,323.00,'2021-08-22 16:38:00'), (130,30362,558.00,'2021-08-26 17:20:00'), (131,75205,1120.00,'2021-08-28 19:26:00'), (132,27727,858.00,'2021-08-28 15:20:00');

目标分解
1.首先统计每个月下单的用户数

SELECT MONTH( create_time ) as 月份,user_id FROMorders GROUP BYyuefen,user_id;

2.将每个月下单的用户数表自连接判断是否复购,同时计算复购人数

SELECT a.月份 as 购买月份, b.月份 as 复购月, COUNT(distinct(b.user_id)) as 复购人数 from (SELECT MONTH( create_time ) as 月份,user_id FROMorders GROUP BY月份,user_id) a join (SELECT MONTH( create_time ) as 月份,user_id FROMorders GROUP BY月份,user_id ) b on a.user_id = b.user_id and a.月份< b.月份 GROUP BY a.月份,b.月份

3.统计每个月总用户下单数

SELECT MONTH( create_time ) as 月份,COUNT( distinct ( user_id ) ) 总用户数 fromorders GROUP BY月份;

4.计算复购率，复购人数/总用户下单数

select 购买月份,复购月,复购人数,总用户数,CONCAT(ROUND(复购人数/总用户数,2) *100,'%') as 复购率 FROM (SELECT a.月份 as 购买月份,b.月份 as 复购月,COUNT(distinct(b.user_id)) as 复购人数 from (SELECT MONTH( create_time ) as 月份,user_id FROMorders GROUP BY月份,user_id) a join (SELECT MONTH( create_time ) as 月份,user_id FROMorders GROUP BY月份,user_id ) b on a.user_id = b.user_id and a.月份< b.月份 GROUP BY a.月份,b.月份 ) fg join (SELECT MONTH( create_time ) as 月份,COUNT( distinct ( user_id ) ) 总用户数 fromorders GROUP BY月份 ) zr on fg.购买月份 = zr.月份

总结

以上是生活随笔为你收集整理的MySQL中用户订单复购率的计算的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。