明确题意

请统计零食类商品中复购率top3高的商品。

• 某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数
• 近90天：90天指包含最大日期（记为当天）在内的近90天
• 复购率保留 3 位小数
• 复购率 倒序 、商品ID 升序 排序

问题拆解

1. 得到每个商品每个用户购买次数——表tb

• 左连接多表：**

    tb_product_info pi
    LEFT JOIN tb_order_detail od 
      ON pi.product_id = od.product_id 
    LEFT JOIN tb_order_overall oo 
      ON od.order_id = oo.order_id 

• 筛选零食类商品：WHERE tag = '零食'
• 筛选近90天的记录：

DATEDIFF(
      (SELECT 
        MAX(DATE(event_time)) max_time 
      FROM
        tb_order_overall),
      DATE(event_time)
    ) <= 89

• 按商品与用户分组：GROUP BY product_id,uid
• 某个商品用户购买次数：COUNT(*)

2. 根据表 tb 计算每种商品复购的人数以及总人数

• 计算复购率：SUM(IF(buy_times >= 2, 1, 0)) / COUNT(*)
• 复购率保留3位数：ROUND(repurchase_rate, 3)

代码

SELECT 
  product_id,
  ROUND(SUM(IF(buy_times >= 2, 1, 0)) / COUNT(*), 3) repurchase_rate 
        -- if(buy_times>=2,1,0)购买次数如果>=2,则算复购人数
FROM
  (SELECT 
    pi.product_id,
    uid,
    COUNT(*) buy_times -- 某个商品客户购买次数
  FROM
    tb_product_info pi
    LEFT JOIN tb_order_detail od 
      ON pi.product_id = od.product_id 
    LEFT JOIN tb_order_overall oo 
      ON od.order_id = oo.order_id 
  WHERE tag = '零食' -- 零食类商品
    AND DATEDIFF(
      (SELECT 
        MAX(DATE(event_time)) max_time 
      FROM
        tb_order_overall),
      DATE(event_time)
    ) <= 89 -- 近90天
    AND STATUS = 1 -- 有购买的
  GROUP BY pi.product_id,
    uid) tb 
GROUP BY product_id -- 按商品ID进行分组
ORDER BY repurchase_rate DESC,
  product_id -- 按复购率倒序，商品ID升序
LIMIT 3 -- TOP3