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

JOIN product_tb和sales_tb之后，由于sales_tb中记录了同一标签商品多次被购买的记录，product_tb中某些被多次购买商品的inventory和tag_price会出现多次，所以相加时用DISTINCT只加一次即可。

SELECT 
	style_id,
    ROUND(SUM(sales_num) / (SUM(DISTINCT inventory) - SUM(sales_num)) * 100, 2) AS 'pin_rate (%)',
    ROUND(SUM(sales_price) / SUM(DISTINCT tag_price * inventory) * 100, 2) AS 'sell-through_rate (%)'
FROM product_tb
JOIN sales_tb USING (item_id)
GROUP BY style_id
ORDER BY style_id

（以上的版本有误） 以下是修改过的。

SELECT 
	style_id,
    ROUND(total_sales_m / (total_inventory - total_sales_m) * 100, 2) AS 'pin_rate(%)',
    ROUND(total_price / total_tag_price * 100, 2) AS 'sell-through_rate(%)'
FROM
(
	SELECT 
		*,
		(
			SELECT SUM(inventory) AS total_inventory
			FROM product_tb
			WHERE style_id = t1.style_id
			GROUP BY style_id
		) AS total_inventory,
        (
			SELECT SUM(tag_price * inventory) AS total_tag_price
			FROM product_tb
			WHERE style_id = t1.style_id
			GROUP BY style_id
		) AS total_tag_price
	FROM
	(
		SELECT 
			style_id, SUM(sales_num) AS total_sales_m, SUM(sales_price) AS total_price
		FROM product_tb
		JOIN sales_tb USING (item_id)
		GROUP BY style_id
		ORDER BY style_id
	) AS t1
) AS t1