牛客sql面试题 SQL15 某店铺的各商品毛利率及店铺整体毛利率

牛客sql面试题 SQL15 某店铺的各商品毛利率及店铺整体毛利率

• 问题：请计算2021年10月以来店铺901中商品 毛利率大于24.9的商品信息及店铺整体毛利率。
• 商品毛利率=（1-进价/平均单件售价）*100%
  -店铺毛利率=（1-总进价成本/总销售收入）*100%

• 思路，先计算店铺汇总 再纵向连接商品毛利率。
  1.店铺毛利率=(1-总进价成本/总销售收入)*100%。
  – 观察三表可知

#店铺901，2021年10月以来
  -- 总进价价成本= sum(in_price*cnt) as 总进价
  -- 总销售收入  sum( price*cnt ) as 总销售
	select shop_id,
		price*cnt as 总销售,
		in_price*cnt 总进货,event_time
	from tb_order_detail t3 left join tb_product_info t1
		on t1.product_id=t3.product_id join tb_order_overall t2 on t2.order_id=t3.order_id
		where shop_id='901' and date(event_time)>='2021-10-01'

• 注意点：
• 错误点：date（event_time) >=2021-10-01 #要打引号。

select '店铺汇总' as product_id,
	 concat(round((1-sum(总进货)/sum(总销售))*100,1),'%') profit_rate
from(
	select shop_id,
		price*cnt as 总销售,
		in_price*cnt 总进货,event_time
	from tb_order_detail t3 left join tb_product_info t1
		on t1.product_id=t3.product_id join tb_order_overall t2 on t2.order_id=t3.order_id
		where shop_id='901' and date(event_time)>='2021-10-01'
	) t;

-计算商品毛利率 公式同上

select t.product_id as product_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 profit_rate>0.249
	order by product_id

• 最后将两表连接

select '店铺汇总' as product_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 t.product_id as product_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 profit_rate>0.249
	order by product_id);