题解 | 某宝店铺动销率与售罄率

select style_id,round((sum(sumsales)/sum(kucun))*100,2) pin_rate,round((sum(gmv1)/sum(beihuozhi))*100,2) sell_through_rate
from(
select t1.item_id,sumsales,gmv1,(inventory - sumsales) kucun,(inventory *tag_price) beihuozhi,style_id
from product_tb pt
join(
select item_id,sum(sales_num) sumsales,sum(sales_price) gmv1
from sales_tb
group by item_id) as t1
on pt.item_id = t1.item_id) as t2
group by style_id
order by style_id

1.按item_id分组，得到每个item的销售数量，总计价格

2.然后表连接，得到库存数，备货值

3.按照ABC分组，按照题目要求计算即可