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

统计2021年10月每个退货率不大于0.5的商品各项指标_牛客题霸_牛客网s

mysql（ifnull）：

select product_id,
format(ifnull(sum(if_click)/nullif(count(*),0),0),3) as ctr,
format(ifnull(sum(if_cart)/nullif(sum(if_click),0),0),3) as car_rate,
format(ifnull(sum(if_payment)/nullif(sum(if_cart),0),0),3) as payment_rate,
format(ifnull(sum(if_refund)/nullif(sum(if_payment),0),0),3) as refund_rate
from tb_user_event
where date_format(event_time,"%Y-%m")='2021-10'
group by product_id
having refund_rate<=0.5
order by product_id

mysql和oracle（coalesce）：

select product_id,
format(coalesce(sum(if_click)/nullif(count(*),0),0),3) as ctr,
format(coalesce(sum(if_cart)/nullif(sum(if_click),0),0),3) as car_rate,
format(coalesce(sum(if_payment)/nullif(sum(if_cart),0),0),3) as payment_rate,
format(coalesce(sum(if_refund)/nullif(sum(if_payment),0),0),3) as refund_rate
from tb_user_event
where date_format(event_time,"%Y-%m")='2021-10'
group by product_id
having refund_rate<=0.5
order by product_id

这两个代码唯一的区别就是mysql支持ifnull，oracle不支持ifnull，oracle可以用coalesce，这个mysql也支持。

关于ifnull：http://t.csdn.cn/SEF2V 

关于coalesce：http://t.csdn.cn/cTpk6