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

with t as (
	select tod.product_id, in_price, price, tod.cnt
	from tb_order_detail tod
	left join tb_order_overall too on tod.order_id = too.order_id
	left join tb_product_info tpi on tod.product_id = tpi.product_id
	where left(too.event_time, 7) > '2021-09' and tpi.shop_id = 901
)

select '店铺汇总', concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100, 1), '%') profit_rate
from t
union all
(
	select product_id, concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100, 1), '%') profit_rate
	from t
	group by product_id
	having 1-(sum(in_price*cnt)/sum(price*cnt)) > 0.249
	order by profit_rate
)