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

with t1 as (
select product_id, price, cnt, in_price
from tb_order_detail
join tb_product_info using(product_id)
join tb_order_overall using(order_id)
where shop_id=901 and date(event_time)>='2021-10-01'), t2 as (

select ifnull(product_id,'店铺汇总') as product_id,
    round(100*(1-sum(in_price*cnt)/sum(price*cnt)),1) profit_rate
from t1
group by product_id
with rollup
having round(100*(1-sum(in_price*cnt)/sum(price*cnt)),1) >24.9 or product_id is null
order by product_id)

select product_id, concat(profit_rate, '%') profit_rate
from t2