【背景】 电商平台想做“目标月份的品类销售简报”。请基于商品、订单、订单明细三张表，统计目标月（2024-08）各品类的订单数、去重买家数、售出件数、销售额、客单价，并按销售额降序排名。 【原始表】 1)product product_idINT PK，0 categoryVARCHAR(32) 非空 priceDECIMAL(10,2) 非负 2)orders order_idBIGINT PK buyer_idINT 非空 order_dateDATE 非空（下单日期） 3)order_items order_idBIGINT 非空 product_idINT 非空 qtyINT 非负（购买数量） 说明：一个订单可含多品类；统计口径为 2024-08-01 ~ 2024-08-31 的订单。 【要求】 输出每个品类一行： category orders_cnt：该品类在目标月涉及到的订单去重数 buyers_cnt：涉及到该品类的去重买家数 items_qty：售出件数之和 revenue：销售额（SUM(qty*price)） avg_order_value：ROUND(revenueorders_cnt, 2) rank_by_revenue：按 revenue 降序、orders_cnt 降序、category 升序的排名（RANK） 排序：按revenue降序、orders_cnt降序、category升序。 【示例SQL输入】 product +------------+-------------+----------+ product_id category price +------------+-------------+----------+ 1 Electronics 500.00 2 Books 50.00 3 Electronics 1200.00 4 Toys 30.00 5 Books 80.00 +------------+-------------+----------+ orders +----------+----------+------------+ order_id buyer_id order_date +----------+----------+------------+ 101 1 2024-08-03 102 2 2024-08-05 103 1 2024-08-20 104 3 2024-07-30 105 4 2024-08-31 106 2 2024-09-01 +----------+----------+------------+ order_items +----------+------------+-----+ order_id product_id qty +----------+------------+-----+ 101 1 1 101 2 2 102 3 1 103 2 1 103 4 3 104 5 1 105 4 10 105 1 1 106 2 1 +----------+------------+-----+ 【示例输出】 +-------------+------------+------------+-----------+---------+----------------+-----------------+ category orders_cnt buyers_cnt items_qty revenue avg_order_value rank_by_revenue +-------------+------------+------------+-----------+---------+----------------+-----------------+ Electronics 3 3 3 2200.00 733.33 1 Toys 2 2 13 390.00 195.00 2 Books 2 1 6 150.00 75.00 3 +-------------+------------+------------+-----------+---------+----------------+-----------------+
示例1

输入

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS product;

CREATE TABLE product (
  product_id INT PRIMARY KEY,
  category VARCHAR(32) NOT NULL,
  price DECIMAL(10,2) NOT NULL
);
CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  buyer_id INT NOT NULL,
  order_date DATE NOT NULL
);
CREATE TABLE order_items (
  order_id BIGINT NOT NULL,
  product_id INT NOT NULL,
  qty INT NOT NULL
);

INSERT INTO product VALUES
(1,'Electronics',500.00),
(2,'Books',50.00),
(3,'Electronics',1200.00),
(4,'Toys',30.00),
(5,'Books',80.00);

INSERT INTO orders VALUES
(101,1,'2024-08-03'),
(102,2,'2024-08-05'),
(103,1,'2024-08-20'),
(104,3,'2024-07-30'),  -- 非目标月
(105,4,'2024-08-31'),
(106,2,'2024-09-01');  -- 非目标月

INSERT INTO order_items VALUES
(101,1,1),(101,2,2),     -- 500 + 100
(102,3,1),               -- 1200
(103,2,1),(103,4,3),     -- 50 + 90
(104,5,1),               -- 非目标月
(105,4,10),(105,1,1),    -- 300 + 500
(106,2,1);               -- 非目标月

输出

category|orders_cnt|buyers_cnt|items_qty|revenue|avg_order_value|rank_by_revenue
Electronics|3|3|3|2200.00|733.33|1
Toys|2|2|13|390.00|195.00|2
Books|2|1|3|150.00|75.00|3
加载中...