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

解题思路：因为除了观看次数不能为零外，其他的数据均可能为零，因此需要利用if（条件，条件成立的话，不成立则）将为零的条件化解；所以需要用到子查询：将点技术等数量提前计算好，并用where payment_cnt= 0 or refund_cnt/payment_cnt <= 0.5来达到整体退货率低于0.5的目的

• select product_id, round(click_cnt/show_cnt, 3) as ctr,

• round(IF(click_cnt>0, cart_cnt/click_cnt, 0), 3) as cart_rate,
  

• round(IF(cart_cnt>0, payment_cnt/cart_cnt, 0), 3) as payment_rate,
  

• round(IF(payment_cnt>0, refund_cnt/payment_cnt, 0), 3) as refund_rate
  

• from (

• select product_id, COUNT(1) as show_cnt,
  

•     sum(if_click) as click_cnt,
  

•     sum(if_cart) as cart_cnt,
  

•     sum(if_payment) as payment_cnt,
  

•     sum(if_refund) as refund_cnt
  

• from tb_user_event
  

• where DATE_FORMAT(event_time, '%Y%m') = '202110'
  

• group by product_id
  

• ) as t_product_index_cnt
• where payment_cnt = 0 or refund_cnt/payment_cnt <= 0.5
• order by product_id;