题解 | #某店铺的各商品毛利率及店铺整体毛利率#

1. 首先，子查询：where中 筛选 202110，按 product_id 分组，用 count(*) 统计出展示数，用 sum() 统计出点击数，加购数，付款数，退款数。

with tb as(
    select
        product_id,
        count(*) show_cnt,
        sum(if_click) click_cnt,
        sum(if_cart) cart_cnt,
        sum(if_payment) pay_cnt,
        sum(if_refund) refund_cnt
    from  
        tb_user_event
    where
        date_format(event_time, '%Y%m') = '202110'
    group by
        product_id
)

2. 然后按照相应计算规则计算各个比率，这里用 if判断了分母，如果为0，则计算表达式就直接为0；不为0时才返回正常计算结果，最后还要 round() 保留3位小数。

select 
    product_id,
    round(if(show_cnt = 0, 0, click_cnt / show_cnt), 3) ctr,
    round(if(click_cnt = 0, 0, cart_cnt / click_cnt), 3) cart_rate,
    round(if(cart_cnt = 0, 0, pay_cnt / cart_cnt), 3) payment_rate,
    round(if(pay_cnt = 0, 0, refund_cnt / pay_cnt), 3) refund_rate
from
    tb
order by
    product_id

3. 附上总体代码：

with tb as(
    select
        product_id,
        count(*) show_cnt,
        sum(if_click) click_cnt,
        sum(if_cart) cart_cnt,
        sum(if_payment) pay_cnt,
        sum(if_refund) refund_cnt
    from  
        tb_user_event
    where
        date_format(event_time, '%Y%m') = '202110'
    group by
        product_id
)
select 
    product_id,
    round(if(show_cnt = 0, 0, click_cnt / show_cnt), 3) ctr,
    round(if(click_cnt = 0, 0, cart_cnt / click_cnt), 3) cart_rate,
    round(if(cart_cnt = 0, 0, pay_cnt / cart_cnt), 3) payment_rate,
    round(if(pay_cnt = 0, 0, refund_cnt / pay_cnt), 3) refund_rate
from
    tb
order by
    product_id