-- 1、求出近90天内所有购买零食类商品的信息 WITH t AS ( SELECT d.product_id,event_time,uid FROM tb_order_overall o JOIN tb_order_detail d ON o.order_id=d.order_id JOIN tb_product_info p ON d.product_id=p.product_id WHERE tag='零食' AND status=1 AND TIMESTAMPDIFF(DAY,DATE(event_time),(SELECT MAX(DATE(event_time)) FROM tb_order_overall))<90 ) -- 2、根据商品id分类,购买人数:COUNT(DISTINCT uid),重复购买人数:COUNT(uid)-COUNT(DISTINCT uid) SELECT product_id, ROUND((COUNT(uid)-COUNT(DISTINCT uid))/COUNT(DISTINCT uid),3) repurchase_rate FROM t GROUP BY product_id ORDER BY repurchase_rate DESC LIMIT 3