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

### 问题分解：

• 计算各个维度的计数（生成子表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;
``````