# 1、将产品情况表中各spu商品的存量信息提取出来
with a as (select style_id,sum(inventory) as inventory_sum,sum(tag_price*inventory) as tag_price 
           from product_tb group by style_id)
# 3、将连接的表与a表连接
select a.style_id
,round(sum(t1.sales_num)*100/(sum(a.inventory_sum)/count(a.style_id)-sum(t1.sales_num)),2) as pin_rate
,round(sum(t1.sales_price)*100/(sum(a.tag_price)/count(a.style_id)),2) as sell_through_rate 
from 
# 2、将产品情况表和销售数据表连接，获取spu信息，用于匹配a表
(select s.* ,p.style_id 
from sales_tb as s left join product_tb as p on s.item_id=p.item_id) t1 
left join a on t1.style_id=a.style_id 

group by a.style_id order by a.style_id asc