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

#动销率:已售数量/（库存-已售） 坑点：动销的分母需要减已售 售罄不需要
#售罄率：GMV/吊牌价*（库存）

#优化前/*
with t1 as
(
select style_id,
(sum(distinct inventory)-sum(sales_num)) as num_now,
sum(sales_num) as num_sold,
sum(sales_price) as GMV
from product_tb
left join sales_tb using(item_id)
group by style_id
),
t2 as
(
select style_id,sum(bkv) sbkv
from (
#这里错将售罄率的库存算成已有库存 并且会由于和总表合并 每行都会加上总表的值导致结果过大 这里用了distinct避免
select style_id,item_id,(sum(distinct inventory)-sum(sales_num))*avg(tag_price) as bkv
from product_tb pt
left join sales_tb using(item_id)
group by item_id,style_id
)t
group by style_id
)
select style_id,ROUND((num_sold/num_now)*100,2),ROUND((GMV/sbkv)*100,2)
from t1
inner join t2 using(style_id)
*/
#将冗余优化后
select style_id,
ROUND(
(sum(sold_num)/sum(inventory-sold_num))*100
,2) AS pin_rate,
ROUND(
(SUM(GMV)/sum(tag_price*inventory))*100
,2) AS sell_through_rate
from product_tb
left join
(
select item_id,sum(sales_num) as sold_num ,sum(sales_price) as GMV
from sales_tb
group by item_id
)t using(item_id)
group by style_id