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

with a as(
select
    D.order_id,
    I.product_id,
    in_price,
    D.price,
    D.cnt
from
    tb_order_detail as D 
join tb_order_overall as O
on O.order_id = D.order_id
join tb_product_info as I
on I.product_id = D.product_id
where
    event_time >= '2021-10-01' and shop_id = 901
),
b as (
select
    product_id,
    sum(in_price*cnt) as total_in,
    sum(price*cnt) as total_amount,
    round((1-sum(in_price*cnt)/sum(price*cnt)) * 100, 1) as profit_rate
from
    a
group by
    product_id
)
select
    '店铺汇总' as product_id,
    concat(round((1-sum(total_in)/sum(total_amount)) * 100, 1), '%') as profit_rate
from
    b
union
select
    product_id,
    concat(profit_rate, '%') as profit_rate
from
    b
where
    profit_rate > 24.9

这店铺汇总算了我半天，后面看别人题解才知道要把小于24.9%的也算进店铺里