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

• 筛选用户的购买信息,t1
  • 联结
  • 条件：
    • 零食类，where
    • 近90天: >=date_sub(今天，interval 89 day)
• 对t1中的用户进行打标，有复购记为1，无复购记为0,生成t2
  • 分组：group by uid, product_id
  • if(count(uid)>1,1,0) as tag
• 根据t2计算复购率:
  • 分组：group by product_id
  • 复购率：round(sum(tag)/count(tag),3)
  • 排序：order by
  • 取前3：limit

select 
    product_id,
    round(sum(tag)/count(tag),3) as repurchase_rate
from(
    -- 对用户打标，有复购，记为1 ，无复购记为0
    select 
        uid,
        product_id,
        if(count(uid)>1,1,0) as tag
    from(
        -- 用户的购买情况
        select 
            uid,
            product_id,
            event_time
        from tb_order_overall
        left join tb_order_detail using(order_id) 
        left join tb_product_info using(product_id)
        where tag = '零食'
        and event_time >= (select 
                                date_sub(max(event_time),interval 89 day)
                            from tb_order_overall
                          )
    ) t1
    group by uid,product_id
) t2
group by product_id
order by repurchase_rate desc,product_id
limit 3

注意：-- 错误写法：date_sub(select max(event_time) from tb_order_overall,interval 89 day)