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

WITH t as(
SELECT DISTINCT style_id,SUM(inventory) AS p1,
SUM(tag_price * inventory) AS p2
FROM product_tb AS pt
GROUP BY style_id
),
t1 as(
SELECT DISTINCT SUBSTRING(item_id,1,1) AS style_id,SUM(sales_num) AS p3,SUM(sales_price) AS p4
FROM sales_tb 
GROUP BY style_id
)
SELECT t.style_id,
ROUND((p3 / (p1 - p3) ) * 100,2) AS 'pin_rate(%)',
ROUND(p4 / p2 * 100,2) AS '	sell-through_rate(%)'  
FROM t
JOIN t1 ON t.style_id = t1.style_id 

如果采用两个表直接连接会有重复数据，对每个表进行分开计算，再采用DISTINCT去重。