题解 169 | #统计2021年10月每个退货率不大于#

【场景】：分组求和

【分类】：分组查询

分析思路

select 查询结果 [产品ID;点击率;加购率;成单率;退货率]
from 从哪张表中查询数据[用户行为表]
where 查询条件 [2021年10月]
group by 分组条件 [产品ID]
order by 对查询结果排序 [产品ID升序];

求解代码

#统计2021年10月每个有展示记录的退货率不大于0.5的商品各项指标
select
    product_id,
    round(if(sum(if_click) = 0,0,sum(if_click)/count(product_id)),3) as ctr,
    round(if(sum(if_cart) = 0,0,sum(if_cart)/sum(if_click)),3) as cart_rate,
    round(if(sum(if_payment) = 0,0,sum(if_payment)/sum(if_cart)),3) as payment_rate,
    round(if(sum(if_refund) = 0,0,sum(if_refund)/sum(if_payment)),3) as refund_rate
from tb_user_event
where date_format(event_time,'%Y%m')='202110'
group by product_id
order by product_id