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

select style_id
,round(100*sum(item_num)/(sum(inventory)-sum(item_num)),2) 'pin_rate(%)'
,round(100*sum(item_price)/sum(inventory*tag_price),2) 'sell-through_rate(%)'
from product_tb a
join (
    select item_id
    ,sum(sales_num) item_num
    ,sum(sales_price) item_price
    from sales_tb
    where date_format(sales_date,'%Y-%m') = '2021-11'
    group by item_id
) b using(item_id)
group by style_id
order by style_id 

1、先对sale_tb分组聚合再连接表，否则表1有重复

2、表2的sale_price是指一个订单的总支付金额，不用再乘sale_num！！