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

-- 商品毛利率=（1-进价/平均单价售价）*100%，其中平均单件售价= sum(price*cnt)/sum(cnt)
-- 店铺毛利率=（1-总进价成本/总销售收入）*100%
-- 先获取901店铺2021年10月店铺内所有商品对应进价、销售单价、销售数量
with a as (
select 
t3.product_id
,in_price
,price
,cnt 
from 
tb_order_detail t3
left join 
tb_product_info t1 
on t3.product_id = t1.product_id 
left join 
tb_order_overall t2
on t3.order_id = t2.order_id 
where shop_id = 901 and substr(event_time,1,7) >= '2021-10' )


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