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

首先，根据底表的记录信息，统计出各个商品的对应数量指标，按照product_id聚合分组，得到对应的展示数，加购数，付款数，退款数作为子表 注意：当记录为1时才可以记录，活用count函数中的null不计数，可以表示为case when if_click =1 then if_click else null end

 select product_id
        , count(*) as show_num
        , count(case when if_click =1 then if_click else null end) as click_num
        , count(case when if_cart = 1 then if_cart else null end ) as cart_num
        , count(case when if_payment = 1 then if_payment else null end ) as pay_ment_num
        , count(case when if_refund = 1 then if_refund else null end) as refund_num
    from tb_user_event
    where year(event_time)='2021' and month(event_time) ='10'
    group by product_id

接着，题目要求保留三位小数，且当分母为0时，整体输出为0，需要用if来确保该条件 例如

round(if(show_num!=0,click_num/show_num,0),3) as ctr

最终的实现代码如下：

select product_id
    , round(if(show_num!=0,click_num/show_num,0),3) as ctr
    , round(if(click_num!=0,cart_num/click_num,0),3) as cart_rate
    , round(if(cart_num!=0,pay_ment_num/cart_num,0),3) as payment_rate
    , round(if(pay_ment_num!=0,refund_num/pay_ment_num,0),3) as refund_rate
from 
(
    select product_id
        , count(*) as show_num
        , count(case when if_click =1 then if_click else null end) as click_num
        , count(case when if_cart = 1 then if_cart else null end ) as cart_num
        , count(case when if_payment = 1 then if_payment else null end ) as pay_ment_num
        , count(case when if_refund = 1 then if_refund else null end) as refund_num
    from tb_user_event
    where year(event_time)='2021' and month(event_time) ='10'
    group by product_id
)a
where round(if(pay_ment_num!=0,refund_num/pay_ment_num,0),3) <=0.5
order by product_id