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

1.计算每个订单中的销售额和进价总额

select t1.order_id,t1.product_id,shop_id,price*cnt sales,in_price*cnt inprice
from tb_order_detail t1
join tb_order_overall t2
on t1.order_id = t2.order_id
join tb_product_info t3
on t3.product_id = t1.product_id
where status=1 and shop_id =901
and date(event_time)>='20211001';t1

2.计算商品和店铺的毛利率

select '店铺汇总' product_id, concat(round(100*(1-sum(inprice)/sum(sales)),1),'%') as profit_rate
from t1 
union
select * from 
(select product_id,
concat(round(100*(1-sum(inprice)/sum(sales)),1),'%') as profit_rate
from t1
group by product_id
having profit_rate>24.9
order by product_id) t2

3.最终的sql代码

with t1 as
(select t1.order_id,t1.product_id,shop_id,price*cnt sales,in_price*cnt inprice
from tb_order_detail t1
join tb_order_overall t2
on t1.order_id = t2.order_id
join tb_product_info t3
on t3.product_id = t1.product_id
where status=1 and shop_id =901
and date(event_time)>='20211001')

select '店铺汇总' product_id, concat(round(100*(1-sum(inprice)/sum(sales)),1),'%') as profit_rate
from t1 
union
select * from 
(select product_id,
concat(round(100*(1-sum(inprice)/sum(sales)),1),'%') as profit_rate
from t1
group by product_id
having profit_rate>24.9
order by product_id) t2