电商平台需要对商品的销售和评价情况进行综合分析_

【背景】：电商平台需要对商品的销售和评价情况进行综合分析，以优化商品推荐和运营策略。

【原始表】：

products_underline （商品）表:

product_id (商品 ID): 商品的唯一标识符，INT
product_name (商品名称): 商品的名称，VARCHAR(100)
category (类别): 商品所属的类别，VARCHAR(50)

sales_underline （销售）表:

sale_id (销售 ID): 销售的唯一标识符，INT
product_id (商品 ID): 商品的唯一标识符，用于关联商品表中的商品，INT
sale_date (销售日期): 销售发生的日期，DATE
quantity (销售数量): 销售的商品数量，INT

reviews_underline （评价）表:

review_id (评价 ID): 评价的唯一标识符，INT
product_id (商品 ID): 商品的唯一标识符，用于关联商品表中的商品，INT
rating (评分): 评价的分数，INT（1 - 5 分）
comment (评价内容): 评价的具体文字内容，VARCHAR(500)

【要求】：根据上述表格，查询出所有产品中，2024年平均产品评分低于4分的所有产品ID，平均评分低于4分的所有产品名称，平均评分低于4分的所有产品的总销量。查询结果按照产品平均分升序排列，如果平均分一致按照产品ID升序排列。要求查询出来的表格的字段如下：

product_id (商品 ID): 商品的唯一标识符，INT（平均评分低于4分的所有产品ID）
product_name: 商品名称。（平均评分低于4分的所有产品名称）
total_quantity: 总销售数量。（平均评分低于4分的所有产品的总销量）
average_rating: 平均评分。（产品的平均评分）（round保留2位小数）

【示例】

products_underline （商品）表:

sales_underline （销售）表:

reviews_underline （评价）表:

【按要求查出来的表】

【解释】

根据上述示例中，产品ID是2的产品是运动鞋有2笔销售数据，分别是saleID 2和5，且销售时间都是在2024年，销售总数量是30+25 = 55；相关的销售评价数据有2条分别是reviewID 2和5，评价平均分是（3+2）/2 = 2.50

输入

DROP TABLE IF EXISTS products_underline;
DROP TABLE IF EXISTS sales_underline;
DROP TABLE IF EXISTS reviews_underline;

-- 创建表
CREATE TABLE products_underline (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50)
);

CREATE TABLE sales_underline (
    sale_id INT PRIMARY KEY,
    product_id INT,
    sale_date DATE,
    quantity INT
);

CREATE TABLE reviews_underline (
    review_id INT PRIMARY KEY,
    product_id INT,
    rating INT,
    comment VARCHAR(500)
);

-- 插入数据
INSERT INTO products_underline (product_id, product_name, category)
VALUES (1, '手机', '电子产品'),
       (2, '运动鞋', '运动用品'),
       (3, '笔记本电脑', '电子产品'),
       (4, '篮球', '运动用品');

INSERT INTO sales_underline (sale_id, product_id, sale_date, quantity)
VALUES (1, 1, '2024-03-01', 50),
       (2, 2, '2024-04-05', 30),
       (3, 1, '2024-04-10', 20),
       (4, 3, '2024-05-15', 40),
       (5, 2, '2024-06-20', 25),
       (6, 4, '2024-06-20', 25),
       (7, 4, '2024-07-20', 25);

INSERT INTO reviews_underline (review_id, product_id, rating, comment)
VALUES (1, 1, 4, '很好用'),
       (2, 2, 3, '还不错'),
       (3, 1, 3, '一般'),
       (4, 3, 5, '性能不错'),
       (5, 2, 2, '一般般'),
       (6, 4, 4, '一般般'),
       (7, 4, 3, '一般');
select * from products_underline;
select * from sales_underline;
select * from reviews_underline;

with t1 as ( select x.product_id,x.product_name,round(avg(rating),2) as average_rating from products_underline x join reviews_underline y on x.product_id=y.product_id join sales_underline z on x.product_id=z.product_id where left(sale_date,4)=2024 group by 1,2 having avg(rating)<4 ) select t1.product_id,product_name,sum(quantity) as total_quantity,average_rating from t1 join sales_underline t2 using(product_id) group by 1,2,4 order by 4,1

电商平台需要对商品的销售和评价情况进行综合分析

输入

输出

问题信息

热门推荐

相关试题