某宝店铺动销率与售罄率

select t1.style_id,
       round(100*sum_sn/(sum_inv-sum_sn),2) as `pin_rate(%)`,
       round(100*sum_sp/sum_invp,2) as `sell-through_rate(%)`
       from (
       select style_id,sum(sales_num) sum_sn,sum(sales_price) sum_sp from sales_tb st
       left join product_tb pt using(item_id) group by style_id
       )t1 join (
       select style_id,sum(inventory) sum_inv,sum(inventory*tag_price) sum_in***bsp;from product_tb group by style_id
       )t2 using(style_id);这个题目容易出错，第一次直接用表2join表1，然后直接算聚合，总是不对，原来是表二中的item_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;

select
    style_id,
    round(100 * sum(sales_num) / (sum_inventory - sum(sales_num)), 2) 'pin_rate(%)',
    round(100 * sum(sales_price) / stock_value, 2) 'sell-through_rate(%)'
from sales_tb st left join
    (
    select
        item_id,
        style_id,
        tag_price,
        inventory,
        sum(inventory) over(partition by style_id order by style_id) sum_inventory,
        sum(tag_price * inventory) over(partition by style_id order by style_id) stock_value
    from product_tb
) npt on st.item_id = npt.item_id

group by style_id, sum_inventory, stock_value
order by style_id

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

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

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

/*sell-through_rate, GMV/备货值，备货值 = 吊牌价(tag_price) * 库存数(inventory)

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

吊牌价 ：商品详情页显示的价格*/
/*本题利用窗口函数先计算出备货值和出售前商品数量，与销售表左连接成一个新表进行组合计算*/

with
    t1 as (
        select
            style_id,
            count(distinct item_id) as total_sku,
            sum(tag_price * inventory) as style_inv_price
        from
            product_tb
        group by
            style_id
    ),
    t2 as (
        select
            p.style_id,
            count(distinct s.item_id) as sale_sku,
            sum(sales_price) as style_gmv
        from
            product_tb p
            inner join sales_tb s on p.item_id = s.item_id
        group by
            p.style_id
    )
select
    t1.style_id,
    round(t2.sale_sku * 100.0 / t1.total_sku, 2) as pin_rate,
    round(t2.style_gmv / t1.style_inv_price * 100, 2) as sell_through_rate
from
    t1,
    t2
where
    t1.style_id = t2.style_id
order by
    t1.style_id;

select a.style_id,
round(100*sum(b.sales_num)/(sum(distinct a.inventory)-sum(b.sales_num)),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, sales_tb b
where a.item_id = b.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(sku)/sum(inventory-sku)*100,2) as pin_rate,
round(sum(gmv)/sum(inventory*tag_price)*100,2) as sell_through_rate
from (
    select item_id,sum(sales_num) as sku,sum(sales_price) as gmv
    from sales_tb 
    where date_format(sales_date,'%Y-%m') = '2021-11'
    group by item_id
)t
inner join product_tb p 
on t.item_id = p.item_id
group by style_id
order by style_id;

select 
a.style_id,
ifnull(round(b.nums/(a.nums1-b.nums)*100,2),0),
ifnull(round(b.gmv/a.bh*100,2),0)
from 
(
select style_id ,
sum(inventory) nums1,
sum(tag_price*inventory) bh from product_tb group by style_id 
) a 
left join 
(
select 
substr(item_id,1,1) style_id,
sum(sales_num) nums,
sum(sales_price) gmv
from sales_tb 
where substr(sales_date,1,7) = '2021-11'
group by style_id ) b 
on a.style_id = b.style_id
order by a.style_id

-- GMV指的是成交金额
select style_id,
round(s_num/(s_inv-s_num)*100,2) pin_rate,
round(s_price/s_p_inv*100,2) sell_through_rate
from 
    (select left(item_id,1) style_id,left(sales_date,7) dt,
    sum(sales_num) s_num, sum(sales_price) s_price
    from sales_tb group by left(item_id,1),dt) m
left join 
    (select style_id,sum(inventory) s_inv,sum(tag_price*inventory) s_p_inv
     from product_tb group by style_id) n
using(style_id)
where dt='2021-11'

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,
    SUM(sales_num) sales_num_total,
    SUM(sales_price) GMV
  FROM sales_tb
  LEFT JOIN product_tb p USING(item_id)
  GROUP BY 1
)
# 这样一对多并表，sales_tb表会有重复项，所以最终结果数值就多了。
# 解决方法：分开统计，避免并表出现重复值


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
ORDER BY 1

# 动销率 --- 有销售 / 在售（库存-已销售）
# 售罄率 --- GMV / 备货值
select style_id, round(sum(sales_num)/(sum(distinct inventory)-sum(sales_num))*100, 2) as `pin_rate(%)`, round(sum(sales_price)/sum(distinct tag_price*inventory)*100, 2) as `sell-through_rate(%)` from sales_tb join product_tb using(item_id) group by style_id order by style_id
;

#建立临时表，按item_id聚合得到关于item_id的总销售数量和结算金额
with total_sales as (select item_id,
                    sum(sales_num) as total_num,
                    sum(sales_price) as total_price
                    from sales_tb
                    group by item_id)
#再用style_id聚合得到关于style_id的动销率和售空率
select pt.style_id,
       round(sum(total_num)/(sum(inventory)-sum(total_num))*100,2) 'pin_rate(%)',
       round(sum(total_price)/ sum(inventory*tag_price)*100,2) 'sell-through_rate(%)'
from total_sales as ts
left join product_tb as pt
on ts.item_id = pt.item_id
group by style_id
order by style_id