某宝店铺动销率与售罄率

item_id	style_id	tag_price	inventory
A001	A	100	20
A002	A	120	30
A003	A	200	15
B001	B	130	18
B002	B	150	22
B003	B	125	10
B004	B	155	12
C001	C	260	25
C002	C	280	18

11月份销售数据表sales_tb如下（其中，sales_date表示销售日期，user_id指用户编号，item_id指货号，sales_num表示销售数量，sales_price表示结算金额）：

sales_date	user_id	item_id	sales_num	sales_price
2021-11-01	1	A001	1	90
2021-11-01	2	A002	2	220
2021-11-01	2	B001	1	120
2021-11-02	3	C001	2	500
2021-11-02	4	B001	1	120
2021-11-03	5	C001	1	240
2021-11-03	6	C002	1	270
2021-11-04	7	A003	1	180
2021-11-04	8	B002	1	140
2021-11-04	9	B001	1	125
2021-11-05	10	B003	1	120
2021-11-05	10	B004	1	150
2021-11-05	10	A003	1	180
2021-11-06	11	B003	1	120
2021-11-06	10	B004	1	150

请你统计每款的动销率（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

示例1

输入:

drop table if exists product_tb;
CREATE TABLE product_tb(
item_id char(10) NOT NULL,
style_id char(10) NOT NULL,
tag_price int(10) NOT NULL,
inventory int(10) NOT NULL
);
INSERT INTO product_tb VALUES('A001', 'A', 100,  20);
INSERT INTO product_tb VALUES('A002', 'A', 120, 30);
INSERT INTO product_tb VALUES('A003', 'A', 200,  15);
INSERT INTO product_tb VALUES('B001', 'B', 130, 18);
INSERT INTO product_tb VALUES('B002', 'B', 150,  22);
INSERT INTO product_tb VALUES('B003', 'B', 125, 10);
INSERT INTO product_tb VALUES('B004', 'B', 155,  12);
INSERT INTO product_tb VALUES('C001', 'C', 260, 25);
INSERT INTO product_tb VALUES('C002', 'C', 280,  18);

drop table if exists sales_tb;
CREATE TABLE sales_tb(
sales_date date NOT NULL,
user_id int(10) NOT NULL,
item_id char(10) NOT NULL,
sales_num int(10) NOT NULL,
sales_price int(10) NOT NULL
);

INSERT INTO sales_tb VALUES('2021-11-1', 1, 'A001',  1, 90);
INSERT INTO sales_tb VALUES('2021-11-1', 2, 'A002',  2, 220);
INSERT INTO sales_tb VALUES('2021-11-1', 2, 'B001',  1, 120);
INSERT INTO sales_tb VALUES('2021-11-2', 3, 'C001',  2, 500);
INSERT INTO sales_tb VALUES('2021-11-2', 4, 'B001',  1, 120);
INSERT INTO sales_tb VALUES('2021-11-3', 5, 'C001',  1, 240);
INSERT INTO sales_tb VALUES('2021-11-3', 6, 'C002',  1, 270);
INSERT INTO sales_tb VALUES('2021-11-4', 7, 'A003',  1, 180);
INSERT INTO sales_tb VALUES('2021-11-4', 8, 'B002',  1, 140);
INSERT INTO sales_tb VALUES('2021-11-4', 9, 'B001',  1, 125);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B003',  1, 120);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B004',  1, 150);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'A003',  1, 180);
INSERT INTO sales_tb VALUES('2021-11-6', 11, 'B003',  1, 120);
INSERT INTO sales_tb VALUES('2021-11-6', 10, 'B004',  1, 150);

输出:

A|8.33|7.79
B|14.81|11.94
C|10.26|8.75

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 37ms, 内存消耗: 6380KB, 提交时间: 2021-12-14

/*select a.style_id,
round(b.xiaoshouliang/(a.zonghuoliang-b.xiaoshouliang)*100,2) 'pin_rate(%)',
round((b.GMV/(a.beihuozhi))*100,2) 'sell-through_rate(%)'
from
(select style_id,sum(inventory) zonghuoliang,sum(inventory*tag_price) beihuozhi from product_tb
group by style_id) as a
join
(select p.style_id,sum(s.sales_num) xiaoshouliang,sum(sales_price) GMV from sales_tb s
inner join product_tb p on p.item_id=s.item_id
group by p.style_id) as b on a.style_id = b.style_id
order by a.style_id*/
/*join
(select style_id,sum(tag_price*kucun) beihuozhi
from
(select p1.style_id,p1.item_id,p1.tag_price,p1.inventory-c.shouliang kucun
from product_tb p1 
inner join (select item_id,sum(sales_num) shouliang from sales_tb group by item_id) c 
on c.item_id=p1.item_id) as d
group by style_id) e on e.style_id=b.style_id*/

/*select a.style_id,
round(b.ys_cnt/(a.kc_cnt-b.ys_cnt)*100,2) 'pin_rate(%)',
round(b.GMV/(a.bh_cnt)*100,2) 'sell-through_rate(%)'
from
(select style_id,sum(inventory) kc_cnt,sum(inventory*tag_price) bh_cnt from product_tb group by style_id) as a
left join
(select p.style_id,sum(s.sales_price) GMV,sum(s.sales_num) ys_cnt from product_tb p 
left join sales_tb s on p.item_id=s.item_id group by p.style_id) as b on b.style_id=a.style_id
order by a.style_id asc
*/


select a.style_id,
round(b.ys_cnt/(a.kc_cnt-b.ys_cnt)*100,2) 'pin_rate(%)',
round(b.GMV/a.bh_price*100,2) 'sell-through_rate(%)'
from
(select style_id,sum(inventory) kc_cnt,sum(tag_price*inventory) bh_price from product_tb
group by style_id)as a 
left join 
(select left(item_id,1) style_id,sum(sales_num) ys_cnt,sum(sales_price) GMV from sales_tb
group by style_id) as b on a.style_id=b.style_id
order by a.style_id

Mysql 解法, 执行用时: 37ms, 内存消耗: 6392KB, 提交时间: 2021-12-31

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 join 
(select 
        item_id
        ,sum(sales_num) sales_num
        ,sum(sales_price) sales_price
        from sales_tb
        group by item_id) t2
        using(item_id)
        group by style_id
        order by style_id

Mysql 解法, 执行用时: 37ms, 内存消耗: 6412KB, 提交时间: 2022-01-23

-- 销售的SKU数量/在售SKU数量 , GMV/备货值
select
style_id,
round(sum(sales_num)/sum(inventory-sales_num)*100,2) as "pin_rate(%)",
round(sum(GMV)/sum(inventory*tag_price) *100,2) as "sell_through_rate(%)"
from 

    (select
    style_id,
    pt.item_id,
    inventory,
    tag_price,
    sum(sales_num) as sales_num,
    sum(sales_price) as GMV
    from product_tb as pt
    join sales_tb as st
    on pt.item_id = st.item_id
    group BY style_id,pt.item_id,inventory,tag_price) as a
group by style_id

Mysql 解法, 执行用时: 37ms, 内存消耗: 6412KB, 提交时间: 2021-12-07

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

Mysql 解法, 执行用时: 37ms, 内存消耗: 6468KB, 提交时间: 2021-12-08

select 
    a.style_id,
    round(sale_cnt/(inventory-sale_cnt)*100,2) `pin_rate(%)`,
    round(gmv/inventory_price*100,2) `sell-through_rate(%)`
from
    (select 
        style_id,
        sum(sales_num) sale_cnt,
        sum(sales_price) gmv
    from 
        product_tb a 
    left join 
        sales_tb b 
    on 
        a.item_id = b.item_id
    group by 
        style_id) a 
left join 
    (select 
        style_id,
        sum(inventory) inventory,
        sum(inventory*tag_price) inventory_price
     from
        product_tb
     group by 
        style_id) b 
on 
    a.style_id = b.style_id

上一题

下一题

详情