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

SELECT style_id,
    ROUND(IFNULL(total_sales_num, 0) / (total_inventory - total_sales_num) * 100, 2) AS 'pin_rate(%)',
    ROUND(GVM / inventory_values * 100, 2) AS 'sell-through_rate(%)'
FROM (   
    SELECT style_id,
        SUM(inventory) AS total_inventory,
        SUM(tag_price * inventory) AS inventory_values
    FROM product_tb
    GROUP BY style_id
) inventory_t
LEFT JOIN (
    SELECT style_id,
        SUM(sales_num) AS total_sales_num,
        SUM(sales_price) AS GVM
    FROM product_tb
        JOIN sales_tb
        USING(item_id)
    GROUP BY style_id
) sale_t
USING(style_id)
ORDER BY style_id