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

明确题意：

统计2021年10月每个有展示记录的退货率不大于0.5商品的各项指标：

商品点展比=点击数÷展示数；加购率=加购数÷点击数；成单率=付款数÷加购数；退货率=退款数÷付款数，当分母为0时整体结果记为0。

结果中各项指标保留3位小数，并按商品ID升序排序。

问题分解：

• 计算各个维度的计数（生成子表t_product_index_cnt）
  • 筛选时间窗内的记录：where DATE_FORMAT(event_time, '%Y%m') = '202110'
  • 按商品ID分组：group by 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
• 计算各种指标率（除了展示数其他均可能为0，要特殊处理！）：
  • 点击率：click_cnt/show_cnt as ctr
  • 加购率：IF(click_cnt>0, cart_cnt/click_cnt, 0) as cart_rate
  • 付款率：IF(cart_cnt>0, payment_cnt/cart_cnt, 0) as payment_rate
  • 退款率：IF(payment_cnt>0, refund_cnt/payment_cnt, 0) as refund_rate
  • 都保留3位小数：ROUND(x, 3)
• 筛选退款率不大于0.5的商品，需注意分母可能为0：where payment_cnt = 0 or refund_rate <= 0.5

细节问题：

• 表头重命名：as
• 按商品ID排序：order by product_id;

完整代码：

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;