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

#pin_rate = (total s.sales_num)/(inventory-sales_num)?
#sell-through_rate = sales_price/(p.tag_price*p.inventory)

WITH t1 AS
(SELECT p.style_id
       ,p.item_id
       ,IFNULL(p.inventory - s.total_sales, p.inventory) AS in_sales
       ,p.tag_price * p.inventory AS total_in_price
       ,s.total_sales
       ,s.GMV
  FROM product_tb p
LEFT JOIN 
    (SELECT item_id
           ,SUM(sales_num) AS total_sales
           ,SUM(sales_price) AS GMV
      FROM sales_tb 
      GROUP BY item_id) AS s
    ON p.item_id = s.item_id)

SELECT style_id
      ,ROUND(100*SUM(total_sales)/SUM(in_sales), 2) AS 'pin_rate(%)'
      ,ROUND(100*SUM(GMV)/SUM(total_in_price), 2) AS 'sell-through_rate(%)'
   FROM t1
GROUP BY style_id
ORDER BY style_id;

这个破脑子真是转不过来弯