需求一：统计每款的SPU（货号）数量，并按SPU数量降序排序。需求二：统计实际总销售额与客单价（结果保留两位小数）需求三：请你统计折扣率需求四：请你统计每款的动销率与售罄率统计连续2天及以上在该店铺购

需求一：统计每款的SPU（货号）数量，并按SPU数量降序排序。

需求二：统计实际总销售额与客单价（结果保留两位小数）

• 客单价 = 总收入/总用户数

需求三：请你统计折扣率

• 折扣率 = GMV/吊牌销售金额

需求四：请你统计每款的动销率与售罄率

• 动销率 = 有销售的SKU数量/在售SKU数量
• 售罄率 = GMV/备货值

需求五：请你统计连续2天及以上在该店铺购物的用户及其对应的次数

产品情况表product_tb（下面为示例数据）：

• item_id具体货号
• style_id款号
• tag_price标签价
• inventory库存量

tem_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

如下为真实测试数据：

drop table if exists product_tb;
CREATE TABLE product_tb
(
    item_id   string,
    style_id  string,
    tag_price int,
    inventory int
);

drop table if exists sales_tb;
CREATE TABLE sales_tb
(
    sales_date  date,
    user_id     int,
    item_id     string,
    sales_num   int,
    sales_price int
);

INSERT INTO product_tb (item_id, style_id, tag_price, inventory) VALUES
('A001', 'A', 100, 20),
('A002', 'A', 150, 30),
('A003', 'A', 200, 40),
('A004', 'A', 120, 25),
('B001', 'B', 180, 35),
('B002', 'B', 130, 25),
('B003', 'B', 140, 30),
('B004', 'B', 150, 20),
('B005', 'B', 160, 15),
('C001', 'C', 160, 30),
('C002', 'C', 170, 35),
('C003', 'C', 120, 25);

INSERT INTO sales_tb (sales_date, user_id, item_id, sales_num, sales_price) VALUES
('2021-11-01', 1, 'A001', 2, 200),
('2021-11-01', 2, 'B003', 3, 300),
('2021-11-01', 3, 'C003', 4, 400),
('2021-11-02', 1, 'A002', 1, 100),
('2021-11-02', 2, 'B002', 2, 200),
('2021-11-03', 1, 'A002', 3, 300),
('2021-11-03', 3, 'C002', 2, 200),
('2021-11-04', 2, 'B001', 4, 400),
('2021-11-04', 3, 'C003', 1, 100),
('2021-11-05', 1, 'A003', 2, 200),
('2021-11-05', 2, 'B004', 3, 300),
('2021-11-05', 3, 'C002', 4, 400),
('2021-11-06', 1, 'A004', 1, 100),
('2021-11-06', 2, 'B003', 2, 200),
('2021-11-06', 3, 'C001', 3, 300),
('2021-11-06', 10, 'B001', 2, 200),
('2021-11-07', 10, 'B002', 1, 100);

作答

-- 需求一：统计每款的SPU（货号）数量，并按SPU数量降序排序。
select
    item_id,
    sum(inventory) num
from product_tb
group by item_id order by num desc ;

-- 需求二：统计实际总销售额与客单价（结果保留两位小数）
-- 客单价 = 总收入/总用户数

select
     round(sum(sales_price) / count(distinct user_id),2) result
from sales_tb;

-- 需求三：请你统计折扣率
-- 折扣率 = GMV/吊牌销售金额
with t1 as (
    select
         item_id,
         sales_price/sales_num result,
         row_number() over(partition by item_id) rn
    from sales_tb
)
select
    t2.item_id,
    t1.result,
    t2.tag_price,
    cast(t1.result as decimal(38,2)) /cast(t2.tag_price as decimal(38,2)) re
from product_tb t2 join t1 on t1.item_id = t2.item_id
where t1.rn=1;

-- 需求四：请你统计每款的动销率与售罄率
-- 动销率 = 有销售的SKU数量/在售SKU数量
-- 售罄率 = GMV/备货值
with t1 as (
    select
         item_id,
         sales_num,
         sales_price/sales_num result,
         row_number() over(partition by item_id) rn
    from sales_tb
)
select
    cast(s.sales_num as decimal(38,2)) / cast(p.inventory as decimal(38,2)) as rn1,
    s.result / cast(p.inventory as decimal(38,2)) rn2
from t1 s join product_tb p on s.item_id=p.item_id
where s.rn=1;

-- 需求五：请你统计连续2天及以上在该店铺购物的用户及其对应的次数

WITH consecutive_days AS (
    SELECT
        s1.user_id,
        DATE_ADD(s1.sales_date, 1) AS next_date
    FROM sales_tb s1
    JOIN sales_tb s2
    ON s1.user_id = s2.user_id
    AND DATE_ADD(s1.sales_date, 1) = s2.sales_date
)

SELECT
    user_id,
    COUNT(*) AS consecutive_count
FROM consecutive_days
GROUP BY user_id
HAVING COUNT(*) >= 1;