统计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