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

呃, 先上最直观的思路:

• 先写一个子查询, 根据product_id分组, 查询对应商品狗买的总人数(无近90天限制)
• 根据时间过滤, 商品product_id分组, 查询出近90天购买最少两次的人数
• 然后根据商品product_id连接, 相除即可

下边说一下我的解法 (追求用窗口函数一步到位, 感觉逼格很高, 但是很难理解), 个人在思考的时候, 花了不少时间去实现通过构造新列, 通过窗口函数的特性: 获取组内的统计信息

1. 技巧: max(event_time) over() 能省略单独写个子查询去查询最大日期; 将购买日期和最大日期比较, 能很容易的获取到购买日期小于90天的用户id

        select od.product_id, oo.uid,
            date(oo.event_time),
            # max(oo.event_time) over() 全局分组可求得最大日期, 记为当天
            # 使用datediff将最大日期和购买日期比较, 得到购买日期小于90天的用户id
            if(datediff(date(max(oo.event_time) over()), date(oo.event_time)) < 90, oo.uid, null) as days
        from tb_order_detail od
        left join tb_order_overall oo on od.order_id=oo.order_id
        left join tb_product_info pi on od.product_id=pi.product_id
        where pi.tag='零食'

2. 技巧: 通过count(*) over(partition by t1.product_id, t1.days) 组内根据product_id, t1.days分组, 得到每款商品, 每个用户90天的购买次数

    select t1.product_id, t1.uid,
        # 根据上述得到购买日期小于90天的用户id, 再次使用窗口函数, 根据t1.product_id, t1.days分组,
        # 进行count(*)统计, 得到number, 如果number大于2则表明, 该用户在90天内购买过至少2次
        count(*) over(partition by t1.product_id, t1.days) number
    from (
        select od.product_id, oo.uid,
            date(oo.event_time),
            if(datediff(date(max(oo.event_time) over()), date(oo.event_time)) < 90, oo.uid, null) as days
        from tb_order_detail od
        left join tb_order_overall oo on od.order_id=oo.order_id
        left join tb_product_info pi on od.product_id=pi.product_id
        where pi.tag='零食'
    ) t1

   来, 感受OLAP分析函数的强大吧!

   select t2.product_id,
    round(count(distinct if(number < 2, null, t2.uid)) / count(distinct t2.uid), 3) as repurchase_rate
   from (
    select t1.product_id,
        t1.uid,
        count(*) over(partition by t1.product_id, t1.days) number
    from (
        select od.product_id,
            oo.uid,
            date(oo.event_time),
            if(datediff(date(max(oo.event_time) over()), date(oo.event_time)) < 90, oo.uid, null) as days
        from tb_order_detail od
        left join tb_order_overall oo on od.order_id=oo.order_id
        left join tb_product_info pi on od.product_id=pi.product_id
        where pi.tag='零食'
    ) t1
   ) t2
   group by t2.product_id
   order by repurchase_rate desc, product_id
   limit 3