MySQL商品复购率计算

先看表格

复购率计算：
根据商品ID、商品名称、订单状态、订单创建时间、收货人电话来进行复购率计算：

select 
b.`商品ID`,b.`名称`,b.`购买人数`,c.`复购人数`,c.`复购人数`/b.`购买人数` as "复购率" from 
(
select `商品ID`,max(`商品名称`) as "名称",count(distinct 收货人电话) as "购买人数" from all_user_tb
where year(`订单创建时间`) = 2019 and `订单状态` = "交易成功"
group by `商品ID`
) as b
left join
(select `商品ID`,count(`收货人电话`) as "复购人数" from (
select `商品ID`,`收货人电话`,count(`收货人电话`) as buy_count from all_user_tb
where year(`订单创建时间`) = 2019 and `订单状态` = "交易成功"
group by `商品ID`,`收货人电话`
having buy_count >=2
) as a
group by `商品ID`
) as c
on b.`商品ID` = c.`商品ID`;

效果预览：