mysql疯狂练习——05

最新推荐文章于 2024-07-06 09:27:14 发布

有智青年i

最新推荐文章于 2024-07-06 09:27:14 发布

阅读量92

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_64622390/article/details/134254205

版权

MySQL 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

1.请你统计每款的SPU（货号）数量，并按SPU数量降序排序

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);

-- 请你统计每款的SPU（货号）数量，并按SPU数量降序排序
select style_id,count(1) SPU_num
from product_tb
group by style_id
order by SPU_num desc
;

2.请你统计实际总销售额与客单价（人均付费，总收入/总用户数，结果保留两位小数）

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);

# 请你统计实际总销售额与客单价（人均付费，总收入/总用户数，结果保留两位小数）
with t1 as ( select sum(sales_price)as sales_total,count(distinct user_id) user_total  from sales_tb )
select sales_total,round(sales_total/user_total,2) as per_trans
from t1
;

3.请你统计折扣率（GMV/吊牌金额，GMV指的是成交金额）成交金额/吊牌金额

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);

-- 请你统计折扣率（GMV/吊牌金额，GMV指的是成交金额）成交金额/吊牌金额
select round(sum(sales_price) / sum(case when sales_num = 1 then tag_price else tag_price * 2 end) * 100, 2) as `discount_rate(%)`
from sales_tb a
         join product_tb b on a.item_id = b.item_id
;

4.请你统计每款的动销率（pin_rate，有销售的SKU数量/在售SKU数量）与售罄率（sell-through_rate，GMV/备货值，备货值=吊牌价*库存数），按style_id升序排序动销率也可以是销售的商品种数与仓库所有商品种数的比值

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);

-- 请你统计每款的动销率（pin_rate，有销售的SKU数量/在售SKU数量）与售罄率（sell-through_rate，GMV/备货值，备货值=吊牌价*库存数），按style_id升序排序动销率也可以是销售的商品种数与仓库所有商品种数的比值
select style_id,
    round(100*sum(num)/ (sum(inventory)-sum(num)),2) as 'pin_rate(%)',
    round(100*sum(item_GMV)/ sum(inventory*tag_price),2) as 'sell-through_rate(%)'
from product_tb a
join
(select item_id,
    sum(sales_num) as num,
    sum(sales_price) as item_GMV
 from sales_tb
 group by item_id) as b
on a.item_id=b.item_id
group by style_id
order by style_id
;
-- 指标的概念难以理解