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

with t1 as(
    select p.product_id,shop_id,in_price,quantity,od.order_id,uid,event_time,total_amount,total_cnt,price,cnt
from tb_product_info p
JOIN tb_order_detail od ON p.product_id = od.product_id
JOIN tb_order_overall oo ON od.order_id = oo.order_id
where shop_id = 901
and event_time >= '2021-10-01 00:00:00'
)
(select 
'店铺汇总' as product_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from t1)
union all
(select
product_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from t1
group by product_id
having (1-sum(in_price*cnt)/sum(price*cnt))>=0.249)