对商品的销售情况进行深度分析_

【背景】：电商平台需要对商品的销售情况进行深度分析，以优化运营策略。现根据以下三个数据表，查询相关销售数据。
【原始表】：
products（商品）表:

product_id (商品 ID): 商品的唯一标识符
product_name (商品名称): 商品的名称
category (类别): 商品所属的类别，固定的（电子、服装、家居）

sales（销售）表:

sale_id (销售 ID): 销售的唯一标识符
product_id (商品 ID): 所属商品的唯一标识符，用于关联商品表中的商品
sale_date (销售日期): 销售日期
quantity (销售数量): 销售的商品数量
price (销售单价): 商品的销售单价

customer_info（客户信息）表:

sale_id (销售 ID): 销售的唯一标识符
customer_id (客户 ID): 客户的唯一标识符
age_group (年龄组): 固定的（青年、中年、老年）
gender (性别): 固定的（男、女）

【要求】：查询每个商品类别下，不同年龄组和性别的客户购买商品的总销售额，并计算每个商品类别在不同年龄组占该类别总购买量的比例。结果按照商品类别升序排列。要求查询出来的表格的字段如下：

product_category: 商品类别。
age_group: 年龄组。
total_sales_amount: 总销售额。
purchase_percentage: 购买量占比。（round保留2位小数）

【示例】

products（商品）表:

sales（销售）表:

customer_info（客户信息）表:

【按要求查询出来的表】

【解释】

上述示例中产品ID1和4号产品都属于电子产品，购买这两个产品的两笔销售记录中分别一个是中年一个青年购买的，中年购买的总金额是105000，青年购买的是60000，中年占比105000/（105000+60000）约等于 0.64，青年占比60000/（105000+60000）约等于 0.36

输入

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS sales;
DROP TABLE IF EXISTS customer_info;

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

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    sale_date DATE,
    quantity INT,
    price DECIMAL(10, 2)
);

CREATE TABLE customer_info (
    sale_id INT PRIMARY KEY,
    customer_id INT ,
    age_group VARCHAR(10),
    gender VARCHAR(10)
);

-- 插入数据
INSERT INTO products (product_id, product_name, category)
VALUES (1, 'iPhone 14', '电子'),
       (2, '时尚连衣裙', '服装'),
       (3, '衬衫S10120', '服装'),
       (4, 'ipad10', '电子');

INSERT INTO sales (sale_id, product_id, sale_date, quantity, price)
VALUES (1, 1, '2024-07-01', 10, 6000),
       (2, 2, '2024-07-02', 5, 200),
       (3, 3, '2024-07-03', 10, 100),
       (4, 4, '2024-07-04', 15, 7000);

INSERT INTO customer_info ( sale_id, customer_id,age_group, gender)
VALUES (1, 1, '青年', '男'),
       (2, 2, '中年', '女'),
       (3, 2, '中年', '女'),
       (4, 2, '中年', '女');

select * from products;
select * from sales;
select * from customer_info;

WITH temp1 AS( SELECT category AS product_category, age_group, SUM(quantity * price) AS total_sales_amount FROM sales JOIN products USING (product_id) JOIN customer_info USING (sale_id) GROUP BY category, age_group ), temp2 AS( SELECT *, SUM(total_sales_amount) OVER (PARTITION BY product_category) AS total_sum FROM temp1 ) SELECT product_category, age_group, total_sales_amount, ROUND(total_sales_amount / total_sum, 2) AS purchase_percentage FROM temp2 ORDER BY product_category, age_group

select category as product_category, age_group, sum(quantity * price) as total_sales_amount, round(sum(quantity * price) / (sum(sum(quantity * price)) over (partition by category)),2) as purchase_percentage from products as p left join sales as s on p.product_id = s.product_id left join customer_info as c on s.sale_id = c.sale_id group by 1, 2 order by 1,4 desc

对商品的销售情况进行深度分析

输入

输出

问题信息

热门推荐

相关试题