题解|两次不同分组的求和 #某宝店铺动销率与售罄率#

# ## 动销率 = 有销售的SKU数量/在售的SKU数量
# ## 有销售的SKU数量是指已售出的数量总和
# ## 在售的SKU数量是指剩余的数量总和 = 总库存-售出的
# ## 售罄率 = GMV(总销售额)/吊牌价*库存数

# FROM sales_tb a
# LEFT JOIN product_tb a
# ON a.item_id = b.item_id
# ROUND(SUM(sales_num)*100/SUM(inventory)-SUM(sales_num),2) AS pin_rate
# ROUND(SUM(sales_price)*100/SUM(tag_price*(inventory-sales_num)),2) AS sell-through_rate

# 需要先按照item_id分组求和，再按照style_id分组求和，所以需要使用一个嵌套，计算两次求和
SELECT style_id,
       ROUND(SUM(num)*100/(SUM(inventory)-SUM(num)),2) AS 'pin_rate(%)',
       ROUND(SUM(GMV)*100/SUM(tag_price*inventory),2) AS 'sell-through_rate(%)'
FROM product_tb a
JOIN (
    SELECT item_id,SUM(sales_price) AS GMV,SUM(sales_num) AS num
    FROM sales_tb
    GROUP BY item_id
)t1
ON a.item_id = t1.item_id
GROUP BY style_id
ORDER BY style_id