题解 | #每个退货率不大于0.5的商品各项指标#

主要就是理清楚:

1、展示次数用count(*)

2、其他各种次数都是sum()

先构建一张表，把每个商品的展示、点击、加购等次数计算出来，然后筛选退货率<=0.5的展示即可

SELECT 
product_id,
if(view_times<>0,round(click_times/view_times,3),0) as "ctr",
if(click_times<>0,round(cart_times/click_times,3),0) as "cart_rate",
if(cart_times<>0,round(payment_times/cart_times,3),0) as "payment_rate",
if(payment_times<>0,round(refund_times/payment_times,3),0) as "refund_rate"
from (
        SELECT 
        product_id,
        count(*) as "view_times",
        sum(if_click) as "click_times",
        sum(if_cart) as "cart_times",
        sum(if_payment) as "payment_times",
        sum(if_refund) as "refund_times"
        from tb_user_event
        where date_format(event_time,'%Y-%m') = '2021-10'
        group by product_id     
) as df
where if(payment_times<>0,round(refund_times/payment_times,3),0) <= 0.5
order by product_id asc