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

select '店铺汇总' as pproduct_id,concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from tb_product_info t join tb_order_overall t1 join tb_order_detail t2 on t.product_id = t2.product_id and t1.order_id = t2.order_id
where shop_id=901  and date(event_time)>='20211001'
union 
select *
from
(select t.product_id as pproduct_id,concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from tb_product_info t join tb_order_overall t1 join tb_order_detail t2 on t.product_id = t2.product_id and t1.order_id = t2.order_id
where shop_id=901  and date(event_time)>='20211001'
group by t.product_id
having 1-sum(in_price*cnt)/sum(price*cnt)>0.249
order by pproduct_id) t