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

这题主要是理解什么是动销率及什么是售罄率

动销率 = 有销售的SKU数量 / （备货的SKU数量 - 有销售的SKU数量）

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

1.先找到每个style_id的SKU销量总和及对应的GMV

 select style_id, 
    sum(sales_num) as sale,
    sum(sales_price) as gmv
    from product_tb 
    inner join sales_tb
    using(item_id)
    GROUP BY style_id

2. 再找到每个style_id的备货的SKU数量及备货值

 select 
    style_id,
    sum(inventory) as total_inv,
    sum(inventory*tag_price) as total_price
    FROM product_tb
    GROUP BY style_id

3. 合并两个提前计算好的表单用来计算动销率及售罄率并按照style_id升序

select style_id,
    round(100*sale/(total_inv-sale),2) as pin_rate,
    round(100*gmv/total_price,2) as self_throug_rate
    from(
    select style_id, 
    sum(sales_num) as sale,
    sum(sales_price) as gmv
    from product_tb 
    inner join sales_tb
    using(item_id)
    GROUP BY style_id
) temp inner join(
    select 
    style_id,
    sum(inventory) as total_inv,
    sum(inventory*tag_price) as total_price
    FROM product_tb
    GROUP BY style_id
) temp2 using(style_id)
    order by style_id