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

select a.style_id,
       round(100*sku_sale_num/(sku_on_sale-sku_sale_num),2) pin_rate,
       round(100*GMV/inven_amount,2) sell_through_rate
from (select style_id,
             sum(sales_num) sku_sale_num,   -- 计算每款的有销售的sku数量
             sum(sales_price) GMV           -- 计算每款的GMV
      from sales_tb a
      left join product_tb b
      on a.item_id = b.item_id
      group by style_id) a
left join (select style_id,
                  sum(inventory) sku_on_sale,              -- 计算每款的在售SKU数量
                  sum(tag_price*inventory) inven_amount    -- 计算每款的备货值
           from product_tb
           GROUP BY style_id) b
on a.style_id = b.style_id
order by style_id;