题解 | #10月的新户客单价和获客成本#
某宝店铺动销率与售罄率
http://www.nowcoder.com/practice/715dd44c994f45cb871afa98f1b77538
SELECT a.style_id, #动销率=以及卖出的/现有库存 ROUND(b.sales_num_total/(a.inventory_total-b.sales_num_total)*100,2) AS pin_rate, #售罄率=GMV/备货值,备货值=吊牌价*库存数 ROUND(b.GMV/a.inventory_price*100,2)AS sell_through_rate FROM #将生成的两个子表在进行联结查询,把相应的值进行计算 (#在product_tb计算出商品库存量、备货值 SELECT style_id, SUM(inventory)AS inventory_total, SUM(tag_price*inventory)AS inventory_price FROM product_tb GROUP BY 1) AS a JOIN (#在sales_price表中计算出已卖出商品数、GMV SELECT style_id, SUM(sales_num) AS sales_num_total, SUM(sales_price) AS GMV FROM sales_tb t1 LEFT JOIN product_tb t2 USING(item_id) GROUP BY 1) AS b ON a.style_id=b.style_id ORDER BY 1 #将所需要的数值一步一步求出来,再进行计算就可以了