某宝店铺动销率与售罄率

# 难点就是理解 动销率和售罄率 
select style_id, round(sum(sku) / sum(inventory-sku)*100,2) pin_rate,
                 round(sum(gmv) / sum(inventory*tag_price)*100,2) sell_through_rate
FROM
    (SELECT item_id,sum(sales_num) sku ,sum(sales_price) gmv
     FROM sales_tb
     WHERE sales_date BETWEEN '2021-11-01' and '2021-11-30'
     GROUP BY item_id) t 
JOIN product_tb pt USING(item_id)
GROUP by style_id
ORDER by style_id

【场景】：分组求和、动销率 = 销售的货物数量/（库存数量减去已经销售的货物数量）、售罄率 = 结算金额/（吊牌价格*库存数）

【分类】：分组查询

分析思路

难点：

1.理解动销率和售罄率

（1）统计每个货号的销售数量和总的销售金额

（2）计算动销率和售罄率

求解代码

方法一

with子句

with
    main as(
        #统计每个货号的销售数量和总的销售金额
        select
            item_id,
            sum(sales_num) sku ,
            sum(sales_price) gmv
        from sales_tb
        group by item_id
    )

#动销率 = 销售的货物数量/（库存数量减去已经销售的货物数量）
#售罄率 = 结算金额/（吊牌价格*库存数）
select
    style_id, 
    round(sum(sku) / sum(inventory-sku)*100,2) pin_rate,
    round(sum(gmv) / sum(inventory*tag_price)*100,2) sell_through_rate
from main
join product_tb pt using(item_id)
group by style_id
order by style_id

方法二

from子查询

select
    style_id, 
    round(sum(sku) / sum(inventory-sku)*100,2) pin_rate,
    round(sum(gmv) / sum(inventory*tag_price)*100,2) sell_through_rate
from(
    #统计每个货号的销售数量和总的销售金额
    select
        item_id,
        sum(sales_num) sku ,
        sum(sales_price) gmv
    from sales_tb
    group by item_id) main
join product_tb pt using(item_id)
group by style_id
order by style_id

select style_id
        ,round(sum(sales_num)/(sum(distinct inventory)-sum(sales_num))*100,2)as "pin_rate(%)"
        ,round(sum(b.sales_price)/sum(distinct a.tag_price*a.inventory)*100,2) as "sell-through_rate(%)"
from product_tb a  join sales_tb b 
on a.item_id=b.item_id
group by style_id
order by style_id

with a as
(
    select
    style_id
    ,sum(inventory) inventory_total
    ,sum(tag_price * inventory) inventory_price
    from product_tb
    group by 1
),
    b as
(
    select
    style_id
    ,sum(sales_num) sales_num_total
    ,sum(sales_price) gmv
    from sales_tb
    left join product_tb p using(item_id)
    group by 1
)
  
select
style_id
,round(sales_num_total/(inventory_total - sales_num_total)*100,2) pin_rate
,round(gmv/inventory_price*100,2) sell_through_rate
from a
join b 
using(style_id)
order by 1

#参考评论区建立新表，方便对没啥关系的库存和售出分别乘积求和

select 
    distinct style_id
    ,round(s_num/(num-s_num)*100,2)
    ,round(GMV/b_GMV*100,2)
from 
    (select style_id,item_id
        ,sum(inventory)over(partition by style_id) as num
        ,sum(tag_price*inventory)over(partition by style_id) as b_GMV 
     from product_tb) a 
left join  
    (select item_id
         ,sum(sales_num)over(partition by substring(item_id,1,1)) as s_num
         ,sum(sales_price)over(partition by substring(item_id,1,1)) as GMV 
     from sales_tb where month(sales_date)=11) b 
on a.item_id=b.item_id
order by a.style_id

select style_id
    ,round(sum(s)/sum(inventory-s)*100,2) 
    ,round(sum(g)/sum(inventory*tag_price)*100,2) 
from(
    select
        item_id 
        ,sum(sales_num) s 
        ,sum(sales_price) g
    from sales_tb
    group by 1) a join product_tb using(item_id)
group by 1
order by 1;

#此动销率非彼动销率
#动销率=销售的商品个数/库存剩余的商品个数
with t0 as
 (
    select style_id,
    sum(tag_price*inventory) as inventory_price,
    sum(inventory) as inventory_total
from product_tb
group by style_id
),
t1 as 
(
    select 
    style_id,
    sum(sales_num) as sales_num_total,
    sum(sales_price) as gmv
from product_tb a left join sales_tb b on a.item_id=b.item_id
group by style_id
)

select 
style_id,
round(sales_num_total/(inventory_total-sales_num_total)*100,2),
round(gmv/inventory_price*100,2)
from t0 
join t1 using(style_id)
order by style_id

select
    style_id,
    round(sum(sales_num) / (sum(distinct inventory) - sum(sales_num)) * 100,2) 'pin_rate(%)',
    round(sum(sales_price) / sum(distinct tag_price * inventory) * 100,2) 'sell-through_rate(%)'
from product_tb p
left join sales_tb s
on p.item_id = s.item_id
group by style_id
order by style_id;

-- 建立临时表，两次分类计算再连接

with temp1 as 
(
select 
style_id,
sum(sales_num) as sell,
sum(sales_price) as GMV
from sales_tb a
left join product_tb b using(item_id)
group by style_id
)

temp2 as 
(
select 
style_id,
sum(inventory) as total_inventory,
sum(tag_price*inventory) as beihuozhi
from product_tb a
group by style_id
)

select 
style_id,
round(100*sell/(total_inventory-sell),2) as 'pin_rate(%)',
round(100*GMV/beihuozhi,2) as 'sell-through_rate(%)'
from temp2
left join temp1 using(style_id)
order by style_id

with temp1 as 
(
select 
style_id,
sum(sales_num) as sell,
sum(sales_price) as GMV
from sales_tb a
left join product_tb b using(item_id)
group by style_id
)
,temp2 as 
(
select 
style_id,
sum(inventory) as total_inventory,
sum(tag_price*inventory) as beihuozhi
from product_tb a
group by style_id
)

select 
style_id,
round(100*sell/(total_inventory-sell),2) as 'pin_rate(%)',
round(100*GMV/beihuozhi,2) as 'sell-through_rate(%)'
from temp2
left join temp1 using(style_id)
order by style_id

#求每个item_id的总销售数量和总销售额
with t1 as
(
select
    item_id,
    sum(sales_num) as sku,
    sum(sales_price) as gmv
from sales_tb
group by item_id
),
#和product_tb进行连接
t2 as
(
select
    *
from t1
join product_tb
using(item_id)
)
#按题意，pin_rate=每个style_id的销售数量/（每个style_id的库存-每个style_id的销售数量），sell-through_rate=每个style_id的销售额/每个style_id的备货值
select
    style_id,
    round(sum(sku)/sum(inventory-sku)*100,2) as pin_rate,
    round(sum(gmv)/sum(inventory*tag_price)*100,2) as sell_through_rate
from t2
group by style_id
order by style_id

select style_id,
round(sum(sales_num)/(avg(style_inventory_cnt)-sum(sales_num))*100,2) 'pin_rate(%)',
round(sum(sales_price)/avg(through)*100,2) 'sell_through_rate(%)'
from sales_tb
inner join (
    select *,
    sum(inventory)over(partition by style_id) style_inventory_cnt,
    sum(tag_price*inventory)over(partition by style_id) through
    from product_tb
)t1
on sales_tb.item_id=t1.item_id
group by style_id
order by 1;

SELECT 
    style_id,
    ROUND(SUM(total_sales_num) / (SUM(inventory - total_sales_num)) * 100, 2) AS pin_rate,
    ROUND(SUM(total_sales_price) / (SUM(inventory * tag_price)) * 100, 2) AS sell_through_rate
FROM (
    SELECT 
        item_id,
        SUM(sales_price) AS total_sales_price,
        SUM(sales_num) AS total_sales_num
    FROM sales_tb 
    GROUP BY item_id
) AS A
LEFT JOIN product_tb USING(item_id)
GROUP BY style_id
ORDER BY style_id