某宝店铺动销率与售罄率

# 难点就是理解 动销率和售罄率 
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

with t1 as (
select style_id,sum(inventory) as inv_cnt,sum(tag_price*inventory) as bhz
from product_tb
group by 1
),t2 as (
select style_id,sum(sales_num) as sales_cnt,sum(sales_price) as GMV
from product_tb
join sales_tb
using(item_id)
group by 1
)
select 
t1.style_id
,round((sales_cnt)/(inv_cnt-sales_cnt)*100,2) as "pin_rate(%)"
,round(GMV/bhz*100,2) as "sell-throught_rate(%)"
from t1 
join t2
using(style_id)
order by 1

-- 第一种
select
    style_id
    , round(sum(sales_num) / (sum(distinct inventory)-sum(sales_num)) * 100, 2) as 'pin_rate(%)'
    , round(sum(sales_price) / sum(distinct p.tag_price * p.inventory) * 100, 2) as '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
style_info as
(
    select
        style_id
        , sum(inventory) as tot_inventory
        , sum(tag_price * inventory) as pre_amount
    from product_tb
    group by style_id
),
style_sales as
(
    select
        style_id
        , sum(sales_num) as tot_sales
        , 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
)
select
    t1.style_id
    , round(tot_sales / (tot_inventory - tot_sales) * 100, 2) as 'pin_rate(%)'
    , round(GMV / pre_amount * 100, 2) as 'sell-through_rate(%)'
from style_info t1
    left join style_sales t2 on t1.style_id = t2.style_id
order by t1.style_id
;

select distinct style_id,
round(100*y/(x-y),2)  'pin_rate(%)',
round(100*n/m,2) 'sell-through_rate(%)'
from 
(select item_id,style_id,
sum(inventory) over(partition by style_id) as x,
sum(tag_price*inventory) over(partition by style_id) as m
from product_tb
) as a
left join 
(select item_id,
sum(sales_num) over(partition by left(item_id,1)) as y,
sum(sales_price) over(partition by left(item_id,1)) as n
from sales_tb
) as b
on a.item_id = b.item_id
order by style_id