with t1 as(select product_id,count(product_id) cnt, sum(if_click) click, sum(if_cart) cart, sum(if_payment) payment, sum(if_refund) refund
from tb_user_event
where event_time between '2021-10-01' and '2021-10-31'
group by product_id)
select product_id,
ifnull(round(click/cnt,3),0) ctr,
ifnull(round(cart/click,3),0) cart_rate, 
ifnull(round(payment/cart,3),0) payment_rate, 
ifnull(round(refund/payment,3),0) refund_rate
from t1
where round(refund/payment,3)<=0.5
order by product_id
  • 使用聚合函数得到相关的数据
  • 使用ifnull和round控制格式和去除none