难难难****题解 | #某宝店铺动销率与售罄率#2表组合

/*
请你统计每款的动销率（pin_rate，有销售的SKU数量/在售SKU数量）与售罄率（sell-through_rate，GMV/备货值，备货值=吊牌价*库存数），按style_id升序排序，以上例子的输出结果如下：
style_id	pin_rate(%)	sell-through_rate(%)
A	8.33	7.79
B	14.81	11.94
C	10.26	8.75
*/

/*
一、动销率 Stock Keeping Unit (库存量单位)。在很多业务场景下，是计算库存进出计量，物理上不可分割的最小存货单元，表示某款商品的具体配置（规格、颜色等）。

有销售的SKU数量 / 在售SKU数量

有销售的SKU数量：售出的SKU数量总和（商品期间销售数量）

在售的SKU数量 ：剩余（总库存 - 售出）库存SKU的数量总和（商品期末库存数量）

二、售罄率 GMV / 备货值（备货值 = 吊牌价 * 库存数）

GMV ：所有订单的金额总和（很多场景下，下单未付款的订单金额也计算在内）

吊牌价 ：商品详情页显示的价格
*/

# 错误想法
/*
select style_id,
round ( sum(sales_num)*100  / (sum(inventory)-sum(sales_num)) ,2 ) as 'pin_rate(%)',
round (sum(sales_price)*100 /sum(tag_price*inventory)     ,2) as 'sell-through_rate(%)'
from product_tb a left join sales_tb b  
#使用左连接 有些商品可能没有卖出去 没有item_id sales_tb表汇总
on a.item_id =b.item_id 
group by style_id
order by style_id
*/


# 错误原因
# 直接做左连接做计算会导致有重复购买记录的商品的inventory多次计算，计算结果会变小。 要将两个表的计算分开进行。
# 如果直接做内连接/右连接，会导致有一些商品没有卖出一件，所以在表2的范围不存在，但是在表1中确实有库存
# 如果直接做左连接 左边数据确实都存在，但是表2的中item_id可能重复出现很多次，引起表1也有很多次 会导致表1数据变大 inventory变多
# 左连接后 A003就会出现两次 因为在表2中出现了2次 会产生一次重复 类似.... 所以不能左连接


# 要保证左边的inventory不动 即直接对表1进行处理inventory 
# 但是表1中出不来所需要的 sum(sales_num) sum(sales_price) 这两个需要从表2中单独提取 所以先重命名一下 SNUM.SPRICE

select  style_id,
# round ( sum(sales_num)*100  / (sum(inventory)-sum(sales_num)) ,2 ) as 'pin_rate(%)',
# round (sum(sales_price)*100 /sum(tag_price*inventory)     ,2) as 'sell-through_rate(%)'
round ( SNUM*100  / (sum(inventory)-SNUM) ,2 ) as 'pin_rate(%)',
round (SPRICE*100 /sum(tag_price*inventory)    ,2) as 'sell-through_rate(%)'
from product_tb a,
(select item_id,  sum(sales_num) as SNUM, sum(sales_price) as SPRICE 
from sales_tb 
group by item_id 
) as b
where a.item_id = b.item_id 
group by style_id
order by style_id



/*
程序异常退出, 请检查代码"是否有数组越界等异常"或者"是否有语法错误"
SQL_ERROR_INFO: "Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'b.SNUM' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"
1055-SELECT列表的表达式#2不在GROUP BY子句中，并且包含非聚合列“b.SNUM”，该列在功能上不依赖于GROUP BY子句的列；这与sqlmode=only_full_group_by不兼容
*/ 报错 是因为select 中又出现了没涉及到的非聚合函数 需要重新使用聚合函数定义 比如重新加一个sum 


/正确的 法1 
select  style_id,
# round ( sum(sales_num)*100  / (sum(inventory)-sum(sales_num)) ,2 ) as 'pin_rate(%)',
# round (sum(sales_price)*100 /sum(tag_price*inventory)     ,2) as 'sell-through_rate(%)'
round ( sum(SNUM)*100  / (sum(inventory)-sum(SNUM)) ,2 ) as 'pin_rate(%)',
round (sum(SPRICE)*100 /sum(tag_price*inventory)    ,2) as 'sell-through_rate(%)'
from product_tb a,
(select item_id,  sum(sales_num) as SNUM, sum(sales_price) as SPRICE 
from sales_tb 
group by item_id 
) as b
where a.item_id = b.item_id 
group by style_id
order by style_id;


# 整洁的表达
select 
style_id
,round(sum(t2.sales_num)/(sum(inventory)-sum(t2.sales_num))*100,2) pin_rate
,round(sum(sales_price)/sum(tag_price*inventory)*100,2) sell_rate
from product_tb t1
left join 
        (
        select 
        item_id
        ,sum(sales_num) sales_num
        ,sum(sales_price) sales_price
        from sales_tb
        group by item_id
         ) t2
on t1.item_id=t2.item_id
group by style_id
order by style_id;