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

select '店铺汇总' as product_id, concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from tb_order_overall join tb_order_detail using(order_id)
join tb_product_info using(product_id)
where date_format(event_time,'%Y%m')>='202110' and shop_id=901
union all
(select product_id,concat(round((1-avg(in_price)/(avg(price)))*100,1),'%') profit_rate
from tb_order_overall join tb_order_detail using(order_id)
join tb_product_info using(product_id)
where date_format(event_time,'%Y%m')>='202110' and shop_id=901
group by product_id
having (1-avg(in_price)/(avg(price)))*100 > 24.9
order by product_id)

分为两步来算，分别求得店铺和产品的值再汇总。
主要是搞明白公式计算。

若不想使用union all 可以使用 with rollup 来完成汇总。

select ifnull(product_id,'店铺汇总'),concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from tb_order_overall join tb_order_detail using(order_id)
join tb_product_info using(product_id)
where date_format(event_time,'%Y%m')>='202110' and shop_id=901
group by product_id
with rollup
having case when product_id>0 then (1-avg(in_price)/(avg(price)))*100 > 29.4 else (1-avg(in_price)/(avg(price)))*100 > 0 end 
order by product_id

case when 在sql中真是有妙用。