主要信息
- 产品情况表product_tb(item_id指某款号的具体货号,style_id指款号,tag_price表示标签价格,inventory指库存量);销售数据表sales_tb(sales_date表示销售日期,user_id指用户编号,item_id指货号,sales_num表示销售数量,sales_price表示结算金额):
- 请你统计每款的动销率(pin_rate,有销售的SKU数量/在售SKU数量)与售罄率(sell-through_rate,GMV/备货值,备货值=吊牌价* 库存数),按style_id升序排序
问题拆解
总体思路
- 求pin_rate = 有销售的SKU数量/在售SKU数量(包含在售但不一定有销量的SKU数量),SKU为商品的款式即style_id,有销售的SKU数量 = 备货SKU数量 - 在售SKU数量,即分别求备货SKU数量和在售SKU数量
- 求售罄率 = GMV(商品销售总额)/ 备货值 = GMV(商品销售总额)/(吊牌价* 库存数),最后根据style_id升序排列
具体实现
- pin_rate
- SKU通过style_id区分,根据不同style_id分组统计其销售数量和实际销售额 注:style_id在product_tb中,需要通过item_id与sales_tb连接拿到并统计实际销售额度和实际销售数量
- 备货SKU数量 = sum(product_tb.inventory) 对所有的库存商品数量求和;
- 求售罄率
- 备货商品总价格 = 商品价格 * inventory,GMV = SUM(sales_tb.sales_price)
- GMV(商品销售总额)/ 备货值 = GMV(商品销售总额),style_id升序排列 order by style_id
主要考察的点:group by、join、round、order by的基础使用,以及题目理解
SELECT style_id
, round(onSale_num / (total_inventory - onSale_num) * 100, 2) AS 'pin_rate(%)'
, round(GMV / 备货值 * 100, 2) AS 'sell-through_rate(%)'
FROM (
SELECT style_id, SUM(sales_num) AS onSale_num, SUM(sales_price) AS GMV
FROM sales_tb s
LEFT JOIN product_tb p ON s.item_id = p.item_id
GROUP BY style_id
) t1
LEFT JOIN (
SELECT style_id, SUM(inventory) AS total_inventory
, SUM(tag_price * inventory) AS 备货值
FROM product_tb
GROUP BY style_id
) t2 USING (style_id)
ORDER BY style_id;