零食类top3商品

题目描述：

商品信息表tb_product_info

id	product_id	shop_id	tag	int_	quantity	release_time
1	8001	901	零食	60	1000	2020-01-01 10:00:00
2	8002	901	零食	140	500	2020-01-01 10:00:00
3	8003	901	零食	160	500	2020-01-01 10:00:00

（product_id-商品ID, shop_id-店铺ID, tag-商品类别标签, in_price-进货价格, quantity-进货数量, release_time-上架时间）

 

订单总表tb_order_overall

id	order_id	uid	event_time	total_amount	total_cnt	status
1	301001	101	2021-09-30 10:00:00	140	1	1
2	301002	102	2021-10-01 11:00:00	235	2	1
3	301011	102	2021-10-31 11:00:00	250	2	1
4	301003	101	2021-10-02 10:00:00	300	2	1
5	301013	105	2021-10-02 10:00:00	300	2	1
6	301005	104	2021-10-03 10:00:00	170	1	1

（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）

 

 

订单明细表tb_order_detail

id	order_id	product_id	price	cnt
1	301001	8002	150	1
2	301011	8003	200	1
3	301011	8001	80	1
4	301002	8001	85	1
5	301002	8003	180	1
6	301003	8002	140	1
7	301003	8003	180	1
8	301013	8002	140	2
9	301005	8003	180	1

（order_id-订单号, product_id-商品ID, price-商品单价, cnt-下单数量）

 

 

场景逻辑说明：

• 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款， status-订单状态-订单状态为0表示待付款），在订单明细表生成该订单中每个商品的信息；

• 当用户支付完成时，在订单总表修改对应订单记录的status-订单状态-订单状态为1表示已付款；

• 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，订单状态为2表示已退款）。

 

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

 

注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率

此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数

近90天指包含最大日期（记为当天）在内的近90天。结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序

输出示例：

示例数据的输出结果如下：

product_id	repurchase_rate
8001	1.000
8002	0.500
8003	0.333

解释：

商品8001、8002、8003都是零食类商品，8001只被用户102购买了两次，复购率1.000；

商品8002被101购买了两次，被105购买了1次，复购率0.500；

商品8003被102购买两次，被101和105各购买1次，复购率为0.333。

 

方法1：

第一步:3表连接，订单状态为1且类别为零食，日期为近90天

第二步：计算每个用户，每种商品id购买次数（用户，商品id分组）

第三步：次数大于2的计数作为复购率的分子，用户计数作为分母

select product_id,round(count(if(订单量>1,1,null))/count(distinct uid),3) as '复购' from

(

    select uid,a.product_id,count(a.order_id) as '订单量'

    from tb_order_detail a

    join tb_order_overall b

    on a.order_id = b.order_id

    join tb_product_info c

    on a.product_id = c.product_id

    where status = 1

    and tag = '零食'

    and datediff((select max(event_time) from tb_order_overall),event_time)<90

    group by uid,a.product_id

) t

group by product_id

order by 复购 desc,product_id

limit 3

 

方法二：窗口函数构造

select product_id,round(sum(case when ranking>1 then 1 else 0 end)/count(DISTINCT uid),3) rate from
(select product_id,uid,ROW_NUMBER() over(partition by product_id,uid order by event_time) ranking
from tb_order_detail left join tb_product_info using (product_id) left JOIN tb_order_overall using (order_id)
where tag='零食' and status=1

and datediff((select max(event_time) from tb_order_overall),event_time)<90

#and timestampdiff(day,event_time,(select max(event_time) from tb_order_overall)) between 0 and 89

#and datediff((select max(date(event_time)) from tb_order_overall),date(event_time)) <90

)t
group by product_id
order by rate desc,product_id asc
limit 3
巧妙之处在于不用计数，按照用户和商品分组，排名超过2 的均表示购买了超过两次，并且可以方便计算出任意购买次数的比例，只需令ranking=n即可！

所以数量筛选问题新思路：转化为窗口函数排序问题

注意：日期差函数datediff和timestampdiff