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

select style_id,
round(sales_num_cnt * 100 / (inventory_cnt - sales_num_cnt),2) as pin_rate,
round(gmv * 100 / total_gmv,2) as sell_through_rate
from(SELECT style_id,SUM(inventory) inventory_cnt,SUM(tag_price*inventory) total_gmv FROM product_tb GROUP BY style_id) as t1
left join
(SELECT style_id,SUM(sales_num) sales_num_cnt, SUM(sales_price) gmv FROM sales_tb JOIN product_tb USING(item_id) GROUP BY style_id) as t2
using(style_id)

关键在于理解两个指标的含义

动销率=已售商品总数/剩余库存数

• 已售商品总数：SUM(sales_tb.sales_num)
• 剩余库存数:SUM(product_tb.inventory)-SUM(sales_tb.sales_num)

售罄率=GMV/吊牌价*库存数

• GMV=SUM(sales_tb.sales_price)
• 备货值=SUM(product_tb.tag_price*product_tb.inventory)