题解 | #统计2021年10月每个退货率不大于0.5的商品各项指标#

1、计算每个指标所需要的参数

2、处理分母为0的情况，case when

round((case when show_num=0 then 0 else click_num/show_num end),3)as ctr,

或者是 if(条件，是的值，否的值)

round((if(show_num=0,0,click_num/show_num),3)as ctr,

完整代码如下

select product_id,
round((case when show_num=0 then 0 else click_num/show_num end),3)as ctr,
round((case when click_num=0 then 0 else cart_num/click_num end),3)as cart_rate,
round((case when cart_num=0 then 0 else payment_num/cart_num end),3)as payment_rate,
round((case when payment_num=0 then 0 else refund_num/payment_num end),3)as refund_rate
from(
SELECT product_id,sum(if_click)as click_num,count(if_click)as show_num,
sum(if_cart)as cart_num,sum(if_payment)as payment_num,sum(if_refund)as refund_num
from tb_user_event
where DATE_FORMAT(event_time,"%Y-%m")="2021-10"
group by product_id)a
having  refund_rate<=0.5
order by product_id