题解 | #零食类商品中复购率top3高的商品#

1.三表连接查出需要的总表t 1）tag=‘零食’ 2）近90天（包括当天）event_time>= date_sub(max(event_time),interval 89 day) 3)status=1

2.查询购买商品的人数与对应商品，得到购买次数 t1 select uid,product_id,count(1) as gmcs from t

3.计算复购率：复购人数/总人数 1)次数大于2的属于复购，if(gmcs>=2,1,0) 得到每种商品复购人数sum(if(gmcs>=2,1,0) 2）查询t1表里的每种商品被购买的总次数，count select product_id,sum(if(gmcs>=2,1,0) as rs,count(1) as zs from t1 3)round

    select b.uid,b.order_id,c.product_id,a.tag,b.event_time,b.status
        from tb_product_info a 
        left join tb_order_detail c on a.product_id=c.product_id
        left join tb_order_overall b on b.order_id=c.order_id
        where b.event_time>=date_sub((select max(event_time) from tb_order_overall),interval 89 day)
        and b.status=1 and a.tag='零食' 
)

select product_id,round(sum(if(ds>=2,1,0))/count(product_id),3) as fgl from (
    select uid,product_id,count(1) as ds from t group by uid,product_id
) a group by product_id order by fgl desc,product_id limit 3