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

零食类商品中复购率top3高的商品

http://www.nowcoder.com/practice/9c175775e7ad4d9da41602d588c5caf3

【问题】统计零食类商品中复购率top3高的商品

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

首先，左连接表，生成<原始表>，并根据条件将数据进行筛选

SELECT  * from tb_order_overall o
left join tb_order_detail d
on o.order_id=d.order_id
left join tb_product_info p
on p.product_id=d.product_id

然后，对得到的表进行原始数据筛选，需要满足条件

商品是零食类 p.tag='零食'
商品成功支付 o.status=1
交易日期在近90天，即交易日期date(o.event_time)>=距今90天的日期

于是，锁定【距今90天的日期，如何计算？】

1.先计算标杆日期，也就是最大日期

select date(max(event_time)) recent_day from tb_order_overall

2.以recent_day为基准，向前推89天date_sub(recent_day,interval 89 day)，得到距今90天的日期

select DATE_SUB(t.recent_day,INTERVAL 89 day) FROM
(select date(max(event_time)) recent_day from tb_order_overall)t

所以交易在近90天为

date(o.event_time)>=(select DATE_SUB(t.recent_day,INTERVAL 89 day) FROM
(select date(max(event_time)) recent_day from tb_order_overall)t)

以上，完成原始表的构造

SELECT * from tb_order_overall o
left join tb_order_detail d
on o.order_id=d.order_id
left join tb_product_info p
on p.product_id=d.product_id
where p.tag='零食'
and o.status=1
and date(o.event_time)>=(select DATE_SUB(t.recent_day,INTERVAL 89 day) FROM
(select date(max(event_time)) recent_day from tb_order_overall)t)

然后，统计每类商品下，各用户购买的次数

SELECT p.product_id,o.uid,count(p.product_id) cnt from tb_order_overall o
left join tb_order_detail d
on o.order_id=d.order_id
left join tb_product_info p
on p.product_id=d.product_id
where p.tag='零食'
and o.status=1
and date(o.event_time)>=(select DATE_SUB(t.recent_day,INTERVAL 89 day) FROM
(select date(max(event_time)) recent_day from tb_order_overall)t)
group by p.product_id,o.uid

接着，通过计算每类商品购买两次以上的人数以及总人数，可以计算出复购率

思路：按照商品ID分组

总人数则为count(distinct uid)
购买两次以上的人数，因为上步骤已经计算出没中商品每人购买的次数，所以使用case when 筛选出购买次数在2次以上的，并进行人数统计count(distinct case when 购买次数>=2 then uid else null end)

SELECT t1.product_id,
round(count(distinct case when t1.cnt>=2 then t1.uid else null end)/count(DISTINCT t1.uid),3) repurchase_rate  
FROM
(SELECT p.product_id,o.uid,count(p.product_id) cnt from tb_order_overall o
left join tb_order_detail d
on o.order_id=d.order_id
left join tb_product_info p
on p.product_id=d.product_id
where p.tag='零食'
and o.status=1
and date(o.event_time)>=(select DATE_SUB(t.recent_day,INTERVAL 89 day) FROM
(select date(max(event_time)) recent_day from tb_order_overall)t)
group by p.product_id,o.uid)t1
group by t1.product_id

最后，按照复购率倒序desc、商品ID升序排序，筛选出top3(limit 3)

~~~~~~代码省略